Hi,
I'm attempting to write a script in xbasic that selects data from a database on an IBM iSeries system using the ODBC driver provided by IBM. I can use the Alpha Anywhere SQL Builder to create and successfully execute queries, but when I try to execute the same SQL in my script I get an error. The error stems from the use of special characters in some of the column names, such as CCUST#, which necessitates putting square brackets around the name like this [CCUST#]. Please note that I cannot modify the column names in this database.
Why does the SQL Builder run the SQL just fine, and, more importantly, how do I write xbasic code to duplicate that success?
Here's the relevant script code:
Here's the CallResults when I try to execute the sql command:
Thanks for any insights!
I'm attempting to write a script in xbasic that selects data from a database on an IBM iSeries system using the ODBC driver provided by IBM. I can use the Alpha Anywhere SQL Builder to create and successfully execute queries, but when I try to execute the same SQL in my script I get an error. The error stems from the use of special characters in some of the column names, such as CCUST#, which necessitates putting square brackets around the name like this [CCUST#]. Please note that I cannot modify the column names in this database.
Why does the SQL Builder run the SQL just fine, and, more importantly, how do I write xbasic code to duplicate that success?
Here's the relevant script code:
Code:
f = cn.open("::name::soft-pak") sqlCmd = <<%txt% SELECT IWSE4S8_CUST.[CCUST#] AS CCUST_, IWSE4S8_CUST.CNAME, IWSE4S8_CUST.CBLNAM, IWSE4S8_CUST.CSADR1, IWSE4S8_CUST.CSCITY, IWSE4S8_CUST.CSSTAT, IWSE4S8_CUST.CSZIP, IWSE4S8_RDTL.RDDAY, IWSE4S8_RDTL.RDQTY, IWSE4S8_RDTL.RDROUT, IWSE4S8_RDTL.[RDSEQ#] AS RDSEQ_, IWSE4S8_RTYP.RTTYPE, IWSE4S8_ROUT.RDESC, IWSE4S8_BINS.[BBIN#] AS BBIN_, IWSE4S8_BINS.BSIZE, IWSE4S8_BINS.BCHG, IWSE4S8_BINS.BTOTPU, IWSE4S8_BINS.[BBILL$] AS BBILL_, IWSE4S8_BCDE.BCSIZE, IWSE4S8_BCDE.BCCHG, IWSE4S8_BCDE.BCDESC FROM (IWSE4S8.ROUT IWSE4S8_ROUT INNER JOIN (IWSE4S8.RDTL IWSE4S8_RDTL INNER JOIN IWSE4S8.CUST IWSE4S8_CUST ON IWSE4S8_RDTL.RDCUST = IWSE4S8_CUST.[CCUST#] AND IWSE4S8_RDTL.RDCOMP = IWSE4S8_CUST.CCMPNY INNER JOIN IWSE4S8.BINS IWSE4S8_BINS ON IWSE4S8_RDTL.[RDBIN#] = IWSE4S8_BINS.[BBIN#] AND IWSE4S8_RDTL.RDCOMP = IWSE4S8_BINS.BCMPNY AND IWSE4S8_RDTL.RDCUST = IWSE4S8_BINS.[BCUST#] INNER JOIN IWSE4S8.BCDE IWSE4S8_BCDE ON IWSE4S8_RDTL.RDCOMP = IWSE4S8_BCDE.BCCMPY AND IWSE4S8_RDTL.RDSIZE = IWSE4S8_BCDE.BCSIZE AND IWSE4S8_RDTL.RDCHGC = IWSE4S8_BCDE.BCCHG ) ON IWSE4S8_ROUT.RROUTE = IWSE4S8_RDTL.RDROUT AND IWSE4S8_ROUT.RCMPNY = IWSE4S8_RDTL.RDCOMP INNER JOIN IWSE4S8.RTYP IWSE4S8_RTYP ON IWSE4S8_ROUT.RTYPE = IWSE4S8_RTYP.RTTYPE AND IWSE4S8_ROUT.RCMPNY = IWSE4S8_RTYP.RTCMP ) WHERE IWSE4S8_CUST.CCMPNY = :company AND (IWSE4S8_RTYP.RTTYPE LIKE :route_type) ORDER BY IWSE4S8_RDTL.RDROUT %txt% if cn.Execute(sqlCmd,args) then rs = cn.ResultSet loadSoftpakStops = rs.data(1) cn.Close() else loadSoftpakStops = cn.CallResult cn.Close() end if
Code:
= API = "" Canceled = .F. Code = 195 Error = .T. LastInsertedIdentity = <No data returned> NativeCode = -104 NativeText = [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: * DAY RID RRN CAST CHAR DATE DAYS HASH HOUR LEFT RANK TIME TRIM. SQL State is: 42000 RowsAffected = 0 RowsReturned = -1 Success = .F. Syntax = "" Text = Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. -104 - '[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: * DAY RID RRN CAST CHAR DATE DAYS HASH HOUR LEFT RANK TIME TRIM. SQL State is: 42000' Warnings = .F.
Comment