• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

mas90 odbc from Progress 4GL - Numeric fields return a nul value

I wrote a Progress 4GL script to pull data out of Mas90 via the Mas90 ODBC. It works fine on my XP Pro desktop but when I move it to our production machine (Windows 2003 32bit server), all numeric fields in Mas90 get returned to Progress 4gl as a null value. When I run a VBS script on the 2003 server, all is fine. If I have to, I will run the VBS script to retrieve the data and then use Progress to manipulate the data but I would obviously prefer to use Progress for it all. The Progress script is below:

OUTPUT TO c:\temp\xx.txt.

&scoped-define adUseNone    1
DEFINE TEMP-TABLE tt-salesman
    FIELD salesman-num     AS CHARACTER FORM "x(15)" LABEL "Employee Number"
    FIELD div-no   AS CHARACTER FORMAT "x(2)" LABEL "Division Number"
    FIELD name    AS CHARACTER FORM "x(15)" LABEL "Employee Name"
    INDEX salesman-num AS PRIMARY salesman-num.

DEFINE TEMP-TABLE tt-coline
    FIELD co-num AS CHAR FORMAT "x(7)" LABEL "SO Num"
    FIELD co-line AS CHAR FORMAT "x(6)" LABEL "SO Line Num"
    FIELD ITEM AS CHAR FORMAT "x(15)" LABEL "Part Num"
    FIELD DESC1 AS CHAR FORMAT "X(30)" LABEL "Description"
    FIELD discount AS CHAR FORMAT "x" LABEL "Discount"
    FIELD promise-date AS DATE LABEL "Promise Date"
    FIELD qty-ordered AS DECIMAL LABEL "Qty Ordered"
    FIELD qty-shipped AS DECIMAL LABEL "Qty Shipped"
    FIELD Unit-Price AS DECIMAL LABEL "Unit Price"
    FIELD Extended-amount AS DECIMAL LABEL "Extened Amount"
    FIELD line-discount-amt AS DECIMAL LABEL "Line Disc Amt"
    FIELD acctkey AS CHAR LABEL "Acct Key"
    INDEX co-num AS PRIMARY co-num.

DEFINE VARIABLE filename# AS CHARACTER FORM "x(22)" NO-UNDO.

ASSIGN
    filename# = "c:\hlspc-test.slk".

OS-DELETE VALUE (filename#).

DEFINE VARIABLE SQLstr  AS CHARACTER  NO-UNDO.
DEFINE VARIABLE chCN    AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chRS    AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ConnStr AS CHARACTER  NO-UNDO.

ASSIGN
   
ConnStr = "driver=" + CHR(123)
    + "MAS 90 4.0 ODBC Driver" + CHR(125)
    + ";UID=MyUserName|PP2;PWD=MyPassword;DSN=SOTAMAS90;DIRECTORY=D:\program files\Best\MAS 90\Version 440\MAS90;SERVER=192.168.0.13".
 
CREATE "adodb.connection" chCN.
ASSIGN
    chCN:ConnectionString  = ConnStr
    chCN:ConnectionTimeout = 60.
               
        chCN:Open(ConnStr,,,).
/*chRS:Close.*/
CREATE "ADODB.RecordSet" chRS.
SQLstr = 'SELECT AR_Salesperson.SalespersonNo,
                 AR_Salesperson.SalespersonDivisionNo,
                 AR_Salesperson.SalespersonName
                 FROM AR_Salesperson'.
                             
chRS = chCN:Execute(SQLstr,,).
/*build salemsman table*/
REPEAT:
    IF chRS:EOF THEN LEAVE.

    CREATE tt-salesman.
    ASSIGN                  
        salesman-num     = chRS:Fields("SalespersonNo"):VALUE
        div-no           = chrs:FIELDS("salespersonDivisionNo"):VALUE
        name             = chRS:Fields("SalespersonName"):VALUE.
                                           
    chRS:MoveNext.
END.


/*build SO_SalesOrderDetail table*/

SQLstr = 'SELECT SO_SalesOrderDetail.SalesOrderNo,
                 SO_SalesOrderDetail.LineKey,
                 SO_SalesOrderDetail.ItemCode,
                 SO_SalesOrderDetail.ItemCodeDesc,
                 SO_SalesOrderDetail.Discount,
                 SO_SalesOrderDetail.PromiseDate,
                 SO_SalesOrderDetail.QuantityOrdered,
                 SO_SalesOrderDetail.QuantityShipped,
                 SO_SalesOrderDetail.UnitPrice,
                 SO_SalesOrderDetail.ExtensionAmt,
                 SO_SalesOrderDetail.LineDiscountPercent,
                 SO_SalesOrderDetail.SalesAcctKey
                 FROM SO_SalesOrderDetail'
                 .
     
chRS = chCN:Execute(SQLstr,,).
REPEAT:
    IF chRS:EOF THEN LEAVE.  

    PUT UNFORMATTED
        chRS:Fields("SalesOrderNo"):VALUE
        " "
        chrs:FIELDS("LineKey"):VALUE
        " "
        chRS:Fields("QuantityOrdered"):VALUE SKIP 1.

    CREATE tt-coline.
    ASSIGN                  
        tt-coline.co-num            = chRS:Fields("SalesOrderNo"):VALUE
        tt-coline.co-line           = chrs:FIELDS("LineKey"):VALUE
        tt-coline.ITEM              = chRS:Fields("ItemCode"):VALUE
        tt-coline.DESC1             = chRS:Fields("ItemCodeDesc"):VALUE        
        tt-coline.discount          = chRS:Fields("Discount"):VALUE
        tt-coline.promise-Date      = chRS:Fields("PromiseDate"):VALUE
        tt-coline.qty-ordered       = chRS:Fields("QuantityOrdered"):VALUE
        tt-coline.qty-shipped       = chRS:Fields("QuantityShipped"):VALUE
        tt-coline.unit-Price        = chRS:Fields("UnitPrice"):VALUE
        tt-coline.Extended-amount   = chRS:Fields("ExtensionAmt"):VALUE
        tt-coline.line-discount-amt = chRS:Fields("LineDiscountPercent"):VALUE
        tt-coline.acctkey           = chRS:Fields("SalesAcctKey"):VALUE
        .
    IF tt-coline.desc1 = ? THEN tt-coline.desc1 = "".
    chRS:MoveNext.
END.

FOR EACH tt-salesman:
    DISPLAY tt-salesman WITH STREAM-IO WIDTH 244 DOWN.
END.

FOR EACH tt-coline:
    DISPLAY tt-coline WITH STREAM-IO WIDTH 244 DOWN.
END.

0
KBIPRES
Asked:
KBIPRES
  • 3
1 Solution
 
KBIPRESAuthor Commented:
I have worked around this problem by writing a vbs script to output delimited data that I then read in to Progress 4gl in order to run the reports that I need
0
 
KBIPRESAuthor Commented:
I received no answers on this. My workaround is working.
0
 
rpalbertCommented:
I had the same problem for decimals only - i worked around it with casting

SELECT
CAST( CAST(MyDecimalField AS DECIMAL(18,10)) AS VARCHAR) MyDecimalField
FROM ...

1) i used CAST(MyDecimalField AS DECIMAL(18,10)) because my sql field was float type and i wasn't allowed to modify it. I had to cast to have the same precision as the progress DECIMAL-10 type.

2) CAST( ... AS VARCHAR)
Since i couldn't send decimal to progress using adodb i just cast to char.


3) in progress i use Decimal(MyRecordset:Fields("MyDecimalField "):value) to get the decimal value....

Ugly - but i preffered that than sending to text file as intermediary.
0
 
KBIPRESAuthor Commented:
That is great. I will try it. Writing out to a text file and then reading back in the data is a real pain but it got the job done. This will save me that step
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now