PI JDBC Receiver - strange error on simple INSERT

[former_member105769]

Hi

I have a DB2 (AS400) database table with the following definition:

ColumnIndex	getColumnName	getColumnClassName	getColumnDisplaySize
1	M5LITM	java.lang.String	25
2	M5MMCU	java.lang.String	12
3	M5FDT	java.lang.String	8
4	M5QOP	java.math.BigDecimal	17
5	M5UOM	java.lang.String	2
6	M5SRC	java.lang.String	4
7	M5DCTO	java.lang.String	2
8	M5URRF	java.lang.String	12

I am generating an XML as:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns1:F57492SSL xmlns:ns1="urn:XXXXXXXXXXXXXXX.com:GBL:SNP:Demand">

- <STATEMENT1>

- <TABLENAME ACTION="DELETE">

  <TABLE>UWEUPMQ.F57492SSL3</TABLE>

  </TABLENAME>

  </STATEMENT1>

- <STATEMENT1>

- <TABLENAME ACTION="INSERT">

  <TABLE>UWEUPMQ.F57492SSL3</TABLE>

- <ACCESS>

  <M5LITM>8055951</M5LITM>

  <M5MMCU>99999</M5MMCU>

  <M5FDT>20131101</M5FDT>

  <M5QOP>1</M5QOP>

  <M5UOM>ZZ</M5UOM>

  <M5SRC>ITMI</M5SRC>

  <M5DCTO>FP</M5DCTO>

  <M5URRF>22454</M5URRF>

  </ACCESS>

  </TABLENAME>

  </STATEMENT1>

</ns1:F57492SSL>

The error being returned is:

14.01.2014 16:09:18.038	Information	JDBC Adapter Receiver Channel JDBCReceiverF57492SSL3: Processing started; party   / service JDE_Dev
14.01.2014 16:09:18.077	Information	DELETE FROM UWEUPMQ.F57492SSL3
14.01.2014 16:09:18.086	Information	INSERT INTO  UWEUPMQ.F57492SSL3 (M5LITM, M5MMCU, M5FDT, M5QOP, M5UOM, M5SRC, M5DCTO, M5URRF) VALUES (8055951,        99999, 20131101, 1, ZZ, ITMI, FP,        22454)
14.01.2014 16:09:18.092	Error	Unable to execute statement for table or stored procedure. 'UWEUPMQ.F57492SSL3' (Structure 'STATEMENT1') due to java.sql.SQLException: [SQL0206] Column or global variable ZZ not found.

Can anyone help please?

Regards

Steve

[former_member184720 · Accepted solution]

Hi Stephen - is "UWEUPMQ.F57492SSL3" the actual table name or you have mentioned the schema(UWEUPMQ) in it?

If UWEUPMQ is a schema then can you try giving it in the channel insetad of giving it in the table name mapping? in connection string after the DBname just /schema.

[former_member105769 · Accepted solution]

Hi.  Because this is a AS400 DB2 table, the SQL needs to reference the AS400 Library and the Tablename.

UWEUPMQ is the AS400 Library

F57492SSL3 is the Tablename.

As per my reply above, the following works fine in a SQL client, outside of PI.

INSERT INTO  UWEUPMQ.F57492SSL3 (M5LITM, M5MMCU, M5FDT, M5QOP, M5UOM, M5SRC, M5DCTO, M5URRF) VALUES (8055951,        99999, 20131101, 1, 'ZZ', 'ITMI', 'FP',        22454)

[former_member184720 · Accepted solution]

Hi Steve - May be you can add the attriubte "hasQuot" for the fields where you need a quote..

[former_member105769 · Accepted solution]

Thanks.  I've never had to do this before, but it works.  Cheers. 

[Former Member]

Hi Bentley,

Try passing a different value to field M5UOM other than ZZ and see.

Regards,

Pranav

[former_member105769]

Hi.  M5UOM originally had a different value.  I set it to a constant of 'ZZ'  to see if that would solve the problem, but it didnt. 
What is interesting is, if I run the following SQL statement directly on the database in a SQL client:

INSERT INTO  UWEUPMQ.F57492SSL3 (M5LITM, M5MMCU, M5FDT, M5QOP, M5UOM, M5SRC, M5DCTO, M5URRF) VALUES (8055951,        99999, 20131101, 1, ZZ, ITMI, FP,        22454)

I get the exact same error

If I change it in the SQL client to

INSERT INTO  UWEUPMQ.F57492SSL3 (M5LITM, M5MMCU, M5FDT, M5QOP, M5UOM, M5SRC, M5DCTO, M5URRF) VALUES (8055951,        99999, 20131101, 1, 'ZZ', 'ITMI', 'FP',        22454)

it works.

It seems that the JDBC adapter is not recognising the fact that some of the fields are Strings, and is treating all fields at numerics.