Search code examples
sqldb2odbcibm-midrange

Insert fails on db2 database (on iSeries)

We are having trouble inserting records into a file/table on an iSeries from our VB.NET 2010 application.

The old system running on Windows Xp has no problems but we are trying to run the code (insode VS 2010) on a Windows 7 64-bit box, and OS400 V5.4

Here is the error message returned by the driver:

ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token 2014 was not valid. Valid tokens: ) ,.

Here are the relevant parts of the code:

sConStr = "Driver={Client Access ODBC Driver (32-bit)};" & _
    "System=" & sAS400Server & ";" & _
    "Uid=" & UCase(sAS400UserName) & ";" & _
    "Pwd=" & UCase(sAS400UserPwd) & ";" & _
    "DBQ=" & UCase$(sAS400Library) & _
    IIf(Trim$(sLibraryOther) <> "", "," & sLibraryOther, "") & _
        ";COMPRESSION=1;ALLOWUNSCHAR=1;TRANSLATE=1;"

conOdbc = New Odbc.OdbcConnection(sConStr)
conOdbc.Open()

We create out INSERT statement which looks like this:

"INSERT INTO kerry.YSEPF(YSESID, YSESAN, YSESCC, YSECCY, YSENEGP, YSEAMA, YSESPOD, YSEVFR, YSESNAR, YSELMBY, YSELMPC, YSECRBY, YSECRPC) VALUES (0002109416, 12345678, PS , GBP, C, 000000000006851, 1140918, 1140831, August 2014 Fuel for Co.van                                 , N , 'profile  ','DPVO           ','profile  ','DPVO           ')"

Then we try to insert as below:

iSubmitItems += 1
Try
    iRetVal = cmdOdbc.ExecuteNonQuery()
        iSubmitItemsSuccess += 1
 Catch ex As Exception
     iSubmitItemsFail += 1

and every time we get the error message.

Is there something we are missing such as the driver (is it a 32-bit driver but installed in a different place on Win64?)

EDIT Just wanted to mention that also, the target files is packed or compressed, this is why we can't FTP the data in fixed length text format, whish is what we do for some other data imports

Thanks for any help

Solution

  • Your SQL is invalid:

    [..snip...]6851, 1140918, 1140831, August 2014 Fuel for Co.van   [...snip...]
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^

    Strings must be encapsulated in quotes, e.g.

    [..snip...]6851, 1140918, 1140831, 'August 2014 Fuel for Co.van'   [...snip...]
                                   ^---------------------------^---

    And given this trivial/beginner error, I'm going to say that you're also vulnerable to SQL injection attacks.