asked on

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.

ASKER CERTIFIED SOLUTION

KBIPRES

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KBIPRES

ASKER

I received no answers on this. My workaround is working.

rpalbert

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.

KBIPRES

ASKER

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