asked on

Program Dump when passing decimal variables via asp.net oledb command parameter

I am calling a compiled CL program with 9 parameters. 2 of them are decimal 8,0. The others are chars which work.

the dump shows:

@@DOCO 000481 PACKED(8,0) '0000000000'X
@@DOCO.P 000010 POINTER(SPP)
SPACE OFFSET 1153 '00000481'X
CONTEXT QTEMP 005B89410C6A5C438FB0
OBJECT ZRCRPCSPC

oCmd.Connection = oAS400
oCmd.CommandText = "{{CALL /QSYS.LIB/PALOBJ.LIB/IMC153.PGM (?,?,?,?,?,?,?,?,?)}}"
oCmd.CommandType = CommandType.Text

oCmd.Parameters.Add("&ACTN", System.Data.OleDb.OleDbType.Char, 1).Value = "A"
oCmd.Parameters.Add("&DOCO", System.Data.OleDb.OleDbType.Decimal, 8).Value = 105297.0
oCmd.Parameters.Add("&PID", System.Data.OleDb.OleDbType.Char, 10).Value = "TESTOLEDB"
oCmd.Parameters.Add("&DIP", System.Data.OleDb.OleDbType.Char, 15).Value = "192.168.1.100"
oCmd.Parameters.Add("&USER", System.Data.OleDb.OleDbType.Char, 10).Value = "WARREN"
oCmd.Parameters.Add("&AN8", System.Data.OleDb.OleDbType.Decimal, 8).Value = 500411.0
oCmd.Parameters.Add("&TORP", System.Data.OleDb.OleDbType.Char, 4).Value = "TEST"
oCmd.Parameters.Add("&OVRD", System.Data.OleDb.OleDbType.Char, 1).Value = "N"
oCmd.Parameters.Add("&RESULT", System.Data.OleDb.OleDbType.Char, 50, System.Data.ParameterDirection.InputOutput).Value = ""
Dim x As Integer
Try
x = oCmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

x = -1 and the CL has dump above. How do I get it to accept decimal variables??

peteie

I'm not sure what you are calling this from but can you pass the decimal fields as an integer data type instead? So you pass 105297 instead of 105297.0. I think the as400 sees 105297.0 as having a decimal place.

peteie

wcoykendall

ASKER

Same results as when it's a .decimal, it does not work.......

here is teh CL
0017.00 IMC153: PGM PARM(&ACTN &DOCO &PID &DIP &USER &AN8 -
0018.00 &TORP &OVRD &RESULT)
0019.00 /*
0020.00 DCL VAR(&ACTN ) TYPE(*CHAR) LEN(1)
0021.00 DCL VAR(&DOCO ) TYPE(*DEC ) LEN(8 0)
0022.00 DCL VAR(&PID ) TYPE(*CHAR) LEN(10)
0023.00 DCL VAR(&DIP ) TYPE(*CHAR) LEN(15)
0024.00 DCL VAR(&USER ) TYPE(*CHAR) LEN(10)
0025.00 DCL VAR(&AN8 ) TYPE(*DEC ) LEN(8 0)
0026.00 DCL VAR(&TORP ) TYPE(*CHAR) LEN(4)
0027.00 DCL VAR(&OVRD ) TYPE(*CHAR) LEN(1)
0028.00 DCL VAR(&RESULT ) TYPE(*CHAR) LEN(50)
0029.00 /*

peteie

I'm not very familliar with calling CLs from anywhere other than another CL or RPG program. Is it possible to pass the decimal values as character and then in the CL program move them to a decimal value?

wcoykendall

ASKER

Yes, I could rewrite the RPG that the CL calls to translate the DOCO and AN8 from CHAR to DECIMAL 8 0. But that's not really what I want to do. The docs say this should work, I was hoping to no thave to retro-fit all the various programs I already have to do this.

Seems like a pretty basic thing that IBM does not have working. Very disapointing. But it is what it is.

ASKER CERTIFIED SOLUTION

daveslater

membership

This solution is only available to members.

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

Start Free Trial

ShadowProgrammer

I have no experience with asp.net oledb commands but my educated opinion is as follows..

(1) Is the problem to do with the decimal number is not being defined with any decimal places and yet you are passing .0 in the field, pass an integer value.

try....
oCmd.Parameters.Add("&DOCO", System.Data.OleDb.OleDbType.Decimal, 8).Value = 105297
oCmd.Parameters.Add("&AN8", System.Data.OleDb.OleDbType.Decimal, 8).Value = 500411

(2) Alternative - when I used to call CL programs from command line with decimal numbers you had to pass the parameter as packed format [ x'00000F' or x'00000D' ] as CL defines decimals as packed numbers.
8,0 will take 5 bytes (10 characters) to allow for the sign character at the end F=+ve , D=-ve, so you need nine digits with leading zeros and the sign character.

try....
oCmd.Parameters.Add("&DOCO", System.Data.OleDb.OleDbType.Decimal, 8).Value = x'000105297F'
oCmd.Parameters.Add("&AN8", System.Data.OleDb.OleDbType.Decimal, 8).Value = x'000500411F'

It may work.

Tony

ShadowProgrammer

Extra... If my option (2) works...

For future reference if you want to pass a packed decimal number defined as 8,2 ie. 123456.78 you would pass it in exactly the same way x'012345678F' (it works out where the decimal point goes from the field definition).

Bit of History for you.... This is why packed numbers were traditionally defined with odd lengths - to allow for the required sign character... without any additional leading digit and therefore minimising the space used for storage.

7,2 = 12345.67 '1234567F'
7,3 = 1234.567 '1234567F'
7,4 = 1234.567 '1234567F'

Tony.

wcoykendall

ASKER

Visual Studio 2003.NET does not accept '1234567F' as a value, VB.NET compiler rejects the statement.

c:\tnnvbn101\MyFirstApp\FormADONET.vb(91): Expression expected.

When I do the Param(X).Blah the CL does not dump but it does not work.

daveslater

Hi
there is the code I use - and set it op as a test program.

CL program - compile in to QGPL as PGM2
-----------------------------------------
pgm (&char &dec)

dcl &char *char 10
dcl &dec *dec (8 0)

chgvar &char 'my as400'
chgvar &dec 12345

endpgm
---------------------------------------------

Vb Code

Dim MyConnection As New ADODB.Connection
Dim MyProgram As New ADODB.Command
Dim Rcds As Variant
Dim Parms As Variant

' Connect
MyConnection.Open "Provider=IBMDA400;Data Source=as400c;User ID= ;Password= ;"

' Prepare for call
Set MyProgram.ActiveConnection = MyConnection
MyProgram.CommandText = "{{call qgpl/pgm2(?,?)}}"
MyProgram.Prepared = True
MyProgram.Parameters.Append MyProgram.CreateParameter("Parm1", adChar, adParamInputOutput, 10)
MyProgram.Parameters.Append MyProgram.CreateParameter("Parm2", adDecimal, adParamInputOutput, 0)
MyProgram.Parameters(1).Precision = 8
MyProgram.Parameters(1).NumericScale = 0

' Set parameters from text boxes named Parameter1 and Parameter2.
MyProgram.Execute Rcds, Parms, -1

' Retrieve returned parameters to the form.
Debug.Print MyProgram.Parameters.Item("Parm1").Value
Debug.Print MyProgram.Parameters.Item("Parm2").Value

' Terminate
MyConnection.Close
Set MyConnection = Nothing
Set MyProgram = Nothing

----------------------------------------------

In VB goto to tools / references and select
Microsoft AcitveX data Objects 2.5 Library

Have Fun

Dave

ShadowProgrammer

Did you try '1234567F' or x'1234567F', I missed off the x on my second comment !! and you need the x

Tony.

wcoykendall

ASKER

So we need to use the older COM stuff? ADO DB instead of OLE DB? IBM doesn't have it working?

wcoykendall

ASKER

I added oCmd.Prepare and that still passed in a null value for the call :(

wcoykendall

ASKER

I tried '1234567F'
and x'1234567F' in the IDE gets changed to x '1234567F' which the VB.NET compiler does not like.

If I change the OLEDBType.Decimal to OLEDBType.Char the dump on the As/400 has F1F0F5F9' instead of 105907

ShadowProgrammer

Another thought - can you change the CL ? Does it get called from anywhere else ?

If you can change the CL - change the numeric parameters to characters and in the CL move the character string to a numeric field.
Then you can change your OLEDB code to pass the numerics as character strings. Miss off the decimals though eg. '12345678' not '12345678.0'

PGM (.... &PARMCHR .... )
DCL &PARMCHR *CHAR 8
DCL &PARMNUM *DEC (8 0)

....
CHGVAR &PARMNUM &PARMCHR
.....

ENDPGM

Maybe not a very neat solution but should work.

Tony

daveslater

Hi
"So we need to use the older COM stuff? ADO DB instead of OLE DB? "

I do not have the OLEDB objects on my PC.
Have you tried decalring the variables as OLEDB objects - I am guessig that it is something like

Dim MyConnection
Set MyConnection = CreateObject("OLEDB.Connection")
Dim MyProgram
Set MyProgram = CreateObject("OLEDB.Command")

Let me know how you a defining them and I will load VB6 up a try to get it working!

Dave

wcoykendall

ASKER

VB.NET 2003:

I have tried both:
Dim oAS400 As New System.Data.OleDb.OleDbConnection
Dim oCmd As New System.Data.OleDb.OleDbCommand

Dim o400 As New IBM.Data.DB2.iSeries.iDB2Connection
Dim oCmd As New iSeries.iDB2Connection
'Connection string here/open works without any error
o400.Open()

'MessageBox.Show(oAS400.State.ToString())
oCmd.Connection = o400
oCmd.CommandText = "{{CALL QSYS.LIB/PALOBJ.LIB/IMC200.PGM (?,?)}}"
'oCmd.CommandType = CommandType.Text
'oCmd.Prepare()
'oCmd.DeriveParameters()

oCmd.Parameters.Add("&ACTN", iSeries.iDB2DbType.iDB2Char, 1, ParameterDirection.InputOutput)
oCmd.Parameters.Add("&DOCO", iSeries.iDB2DbType.iDB2Decimal, 8, ParameterDirection.InputOutput)
'oCmd.Parameters("&DOCO").Precision = 8
'oCmd.Parameters("&DOCO").Scale = 0
'oCmd.Parameters("&DOCO").Value = 0

'oCmd.Parameters.Add("&PID", System.Data.OleDb.OleDbType.Char, 10).Value = "TESTOLEDB"
'oCmd.Parameters.Add("&DIP", System.Data.OleDb.OleDbType.Char, 15).Value = "192.168.1.100"
'oCmd.Parameters.Add("&USER", System.Data.OleDb.OleDbType.Char, 10).Value = "WARREN"
'oCmd.Parameters.Add("&AN8", System.Data.OleDb.OleDbType.Decimal, 8)
'oCmd.Parameters("&AN8").Precision = 8
'oCmd.Parameters("&AN8").Scale = 0
'oCmd.Parameters("&AN8").Value = 500411

'oCmd.Parameters.Add("&TORP", System.Data.OleDb.OleDbType.Char, 4).Value = "TEST"
'oCmd.Parameters.Add("&OVRD", System.Data.OleDb.OleDbType.Char, 1).Value = "N"
'oCmd.Parameters.Add("&RESULT", System.Data.OleDb.OleDbType.Char, 50, System.Data.ParameterDirection.InputOutput).Value = ""
Dim x As Integer
Try

x = oCmd.ExecuteScalar

Catch ex As Exception
MessageBox.Show(" Execute Result: " + ex.Message)
End Try

' If oCmd.Parameters("&RESULT").Value = "ERROR" Then
'MessageBox.Show(x.ToString + " RESULT:" + oCmd.Parameters("&RESULT").Value)
MessageBox.Show(x.ToString + " RESULT:" + oCmd.Parameters(1).Value.ToString)
' End If
End Try

Member_2_276102

wcoykendall:

Go back to your first example. Include a specification for SCALE=0 since I don't know what the default setting is if one isn't supplied. (And since you're supplying values that include a decimal point and a digit after the decimal point, it might do almost anything.)

Then tell us what error messages you're getting.

You've supplied us with a small section of a dump, but you've never told us what the error messages are.

Tom

wcoykendall

ASKER

I am not sure where to add SCALE=0 too.

As far as the error... it looks like CHAR works fine with the OLE DB but DECIMAL is getting dropped and passed as NULL.

I would like to avoid changing over to CHAR.......

Additional Message Information

Message ID . . . . . . : RPG0907 Severity . . . . . . . : 99
Message type . . . . . : Inquiry
Date sent . . . . . . : 03/04/05 Time sent . . . . . . : 19:12:28

Message . . . . : IMR153 119600 decimal-data error in field (C G S D F).
Cause . . . . . : The RPG program IMR153 in library PALOBJ found a
decimal-data error at statement 119600. One field did not contain valid
numeric data. The digit and/or sign is not valid.
Recovery . . . : Enter C to cancel, G to continue processing at *GETIN, S
to obtain a system dump, or D to obtain an RPG formatted dump.
Possible choices for replying to message . . . . . . . . . . . . . . . :
D -- Obtain RPG formatted dump.
S -- Obtain system dump.
G -- Continue processing at *GETIN.
C -- Cancel.
More...
Reply . . . : D
Press Enter to continue.

wcoykendall

ASKER

My Current Code:
Imports System.Data.OleDb
Imports System.Data.ParameterDirection
...
...
...
Dim oAS400 As New OleDbConnection
Dim oCmd As New OleDbCommand
'Set connect string
oAS400.Open()

oCmd.Connection = oAS400
oCmd.CommandText = "{{CALL /QSYS.LIB/PALOBJ.LIB/IMC153.PGM (?,?,?,?,?,?,?,?,?)}}"
oCmd.CommandType = CommandType.Text

oCmd.Parameters.Add("&ACTN", OleDbType.Char, 1, Input).Value = "A"
oCmd.Parameters.Add("&DOCO", OleDbType.Decimal, 8, Input).Value = "105297"
oCmd.Parameters.Add("&PID", OleDbType.Char, 10, Input).Value = "TESTOLEDB"
oCmd.Parameters.Add("&DIP", OleDbType.Char, 15, Input).Value = "192.168.1.100"
oCmd.Parameters.Add("&USER", OleDbType.Char, 10, Input).Value = "WARREN"
oCmd.Parameters.Add("&AN8", OleDbType.Decimal, 8, Input).Value = "500411"
oCmd.Parameters.Add("&TORP", OleDbType.Char, 4, Input).Value = "TEST"
oCmd.Parameters.Add("&OVRD", OleDbType.Char, 1, Input).Value = "N"
oCmd.Parameters.Add("&RESULT", OleDbType.Char, 50, InputOutput).Value = ""
Dim x As Integer
Try
x = oCmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message, "EXECUTE ERROR:")
End Try
MessageBox.Show(x.ToString + " RESULT:" + oCmd.Parameters("&RESULT").Value.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR!!!!!!")
End Try

x = -1 and there is a dump on the as400

wcoykendall

ASKER

Ok.... If I do the above CL example with CHGVAR it works... however if I call my CL it does not.

My CL Is:

0017.00 IMC153: PGM PARM(&ACTN &DOCO &PID &DIP &USER &AN8 -
0018.00 &TORP &OVRD &RESULT)
0019.00 /* */
0020.00 DCL VAR(&ACTN ) TYPE(*CHAR) LEN(1)
0021.00 DCL VAR(&DOCO ) TYPE(*DEC ) LEN(8 0)
0022.00 DCL VAR(&PID ) TYPE(*CHAR) LEN(10)
0023.00 DCL VAR(&DIP ) TYPE(*CHAR) LEN(15)
0024.00 DCL VAR(&USER ) TYPE(*CHAR) LEN(10)
0025.00 DCL VAR(&AN8 ) TYPE(*DEC ) LEN(8 0)
0026.00 DCL VAR(&TORP ) TYPE(*CHAR) LEN(4)
0027.00 DCL VAR(&OVRD ) TYPE(*CHAR) LEN(1)
0028.00 DCL VAR(&RESULT ) TYPE(*CHAR) LEN(50)
0029.00 /* */
0030.00 /* --------- Global CPF Error Intercept. --------------------------- */
0031.00 /* */
0032.00 MONMSG MSGID(CPF0000 CPC0000 CPD0000)
0033.00 /* */
0034.00 /* --------- Set Environment --------------------------------------- */
0035.00 /* */
0036.00 ADDLIBLE LIB(PALOBJ) POSITION(*LAST)
0037.00 ADDLIBLE LIB(PALCOM) POSITION(*LAST)
0038.00 ADDLIBLE LIB(JDFOBJ) POSITION(*LAST)
0039.00 /* */
0040.00 RMVLIBLE LIB(PALDATA)
0041.00 RMVLIBLE LIB(TESTDATA)
0042.00 RMVLIBLE LIB(TESTOBJ)
0043.00 /* */
0044.00 IF (&TORP *EQ 'PROD') THEN(DO)
0045.00 ADDLIBLE LIB(PALDATA) POSITION(*FIRST)
0046.00 ENDDO
0047.00 /* */
0048.00 IF (&TORP *NE 'PROD') THEN(DO)
0049.00 ADDLIBLE LIB(TESTDATA) POSITION(*FIRST)
0050.00 ADDLIBLE LIB(TESTOBJ) POSITION(*FIRST)
0051.00 ENDDO
0052.00 /* */
0053.00 /* --------- Call Program ------------------------------------------ */
0054.00 /* */
0055.00 CALL PGM(*LIBL/IMR153) PARM(&ACTN &DOCO &PID &DIP -
0057.00 &RESULT)
0058.00 MONMSG MSGID(CPF0000 CPC0000 CPD0000 MCH0000) +
0059.00 EXEC(GOTO CMDLBL(ABEND))
0060.00 /* */
0061.00 /* --------- Branch To Normal EOJ ---------------------------------- */
0062.00 /* */
0063.00 GOTO EOJ
0064.00 /* */
0065.00 /* --------- Abnormal End Of Job Processing ------------------------ */
0066.00 /* */
0067.00 ABEND: CHGVAR VAR(&RESULT) VALUE('ERROR - PLEASE TRY AGAIN')
0068.00 CALL PGM(*LIBL/IMC999) PARM(&ACTN &PID &DIP &AN8 -
0069.00 &TORP &OVRD &USER)
0070.00 MONMSG MSGID(CPF0000 CPC0000 CPD0000)
0071.00 /* */
0072.00 /* --------- Normal End Of Job ------------------------------------- */
0073.00 /* */
0074.00 EOJ: ENDPGM

wcoykendall

ASKER

Shot! I didn't mean to accept that answer :(

Member_2_276102

wcoykendall:

This sheds a little more light. The error is reported in IMR153 rather than in the CL program IMC153. That might make no difference or it might make all the difference; we'll see.

If you can add a DMPCLPGM command just before the CALL to program IMR153, we can see exact values that are being sent into IMC153 (assuming IMC153 has observability.) We'd like to see the values, including hex, for all parameter variables since the value of one might affect the value of another.

One other question... None of the CL program commands show anything to indicate whether it is OPM CL or ILE CL. Likewise for IMR153. Parameter linkage for CL can have a troublesome aspect in certain situations, particularly with 1-byte character parameter fields; and your first parameter is a 1-byte character. Can you verify that all of the programming is OPM? Perhaps OLE DB usage in VB.NET somehow triggers this problem.

Tom

ShadowProgrammer

As I stated previously, change your CL program by adding the lines below indicated by <<< and changing lines 17/18 to have &DOCO_CHR and &AN8_CHR.
This will receive &DOCO and &AN8 as character strings and convert to decimal for you.

0017.00 IMC153: PGM PARM(&ACTN &DOCO_CHR &PID &DIP &USER &AN8_CHR -
0018.00 &TORP &OVRD &RESULT)
0019.00 /* */
0020.00 DCL VAR(&ACTN ) TYPE(*CHAR) LEN(1)
0021.00 DCL VAR(&DOCO ) TYPE(*DEC ) LEN(8 0)
0021.01 DCL VAR(&DOCO_CHR ) TYPE(*CHAR ) LEN(8) /* CHAR PARAMETER */ <<<
0022.00 DCL VAR(&PID ) TYPE(*CHAR) LEN(10)
0023.00 DCL VAR(&DIP ) TYPE(*CHAR) LEN(15)
0024.00 DCL VAR(&USER ) TYPE(*CHAR) LEN(10)
0025.00 DCL VAR(&AN8 ) TYPE(*DEC ) LEN(8 0)
0025.01 DCL VAR(&AN8_CHR ) TYPE(*CHAR ) LEN(8) /* CHAR PARAMETER */ <<<
0026.00 DCL VAR(&TORP ) TYPE(*CHAR) LEN(4)
0027.00 DCL VAR(&OVRD ) TYPE(*CHAR) LEN(1)
0028.00 DCL VAR(&RESULT ) TYPE(*CHAR) LEN(50)
0029.00 /* */
0030.00 /* --------- Global CPF Error Intercept. --------------------------- */
0031.00 /* */
0032.00 MONMSG MSGID(CPF0000 CPC0000 CPD0000)
0032.01 /* */ <<<
0032.02 /* --------- Convert Character Parameters to Decimal ----------- */ <<<
0032.03 /* */ <<<
0032.07 CHGVAR &DOCO &DOCO_CHR <<<
0032.08 CHGVAR &AN8 &AN8_CHR <<<
0033.00 /* */
0034.00 /* --------- Set Environment --------------------------------------- */
0035.00 /* */

Then also change your OleDb code from

oCmd.Parameters.Add("&DOCO", System.Data.OleDb.OleDbType.Decimal, 8).Value = 105297.0
oCmd.Parameters.Add("&AN8", System.Data.OleDb.OleDbType.Decimal, 8).Value = 500411.0

to

oCmd.Parameters.Add("&DOCO_CHR", System.Data.OleDb.OleDbType.Char, 8).Value = '00105297'
oCmd.Parameters.Add("&AN8_CHR", System.Data.OleDb.OleDbType.Char, 8).Value = '00500411'

to reflect that you are now calling the CL with character parameters and not decimal.

Give that a try.....

Tony.

wcoykendall

ASKER

'oCmd.Parameters.Add("&RESULT", System.Data.OleDb.OleDbType.Char, 50, System.Data.ParameterDirection.InputOutput).Value = ""

Was the problem. It works but the .Add method does not support InputOutput..... so once I changed that everything is working fine now.

Is there an easy way (1 linter) to do a full Add? or do I always have to do .Add then do .<Property> = something after the fact all the time?

Member_2_276102

wcoykendall:

I'm not surprised that an issue with one parameter caused a seemingly unrelated error with another parameter. As for your last question, if there is no clear answer here, perhaps the question would be better in a VB.NET related forum?

Tom