|
-----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Matt Gross Sent: Tuesday, August 22, 2006 8:00 PM To: Post AS-400 Language Questions Subject: Numeric fields in SQL String Statement
<snip>
It's writing the records correctly, with Value2 and Value5 as numerics. The problem I'm running into is two fold: One - this string is being passed into a procedure, running in a software package. I don't have any access to the code that's executing this( RJS Software's RPG2SQL). The package reports a syntax error: (The below has many more fields that the above 5) Received SQL Execute: insert into TRANSACT.DBF(DISTRICT,ORDERNO,WOLINENO,MATERIAL,THICKNESS,QTY, ORDERSHAPE,SHPPARAM1,SHPPARAM2,SHPPARAM3,SHPPARAM4,SHPPARAM5,S HPPARAM6,SHPPARAM7,SHPPARAM8,SPECPROP,SPECPROP1,SPECPROP2,SPEC PROP3,SHTLENGTH,SHTWIDTH,SHTCOST,SHEETTYPE,INTEGER1,INTEGER2,R EAL1,REAL2,REAL3,REAL4,RESULT,ODPOSTOL,ODNEGTOL,IDPOSTOL,IDNEG TOL) VALUES( 99,'123 ','01 ','ABC ', 3.0, 2,'Rec ', .0, .0, .0, .0, .0, .0, .0, .0,'0', .0, .0, .0, .0, .0, .0, .0, .0, .0, .0, .0, .0, .0,'0', .0, .0, .0, .0, .0) from 172.16.222.200(168) Sending: 100 SQL 999 ERR-2147217900 [Microsoft][ODBC dBase Driver] Syntax error in INSERT INTO statement. Two - if you look at the above, the numeric fields are initialized as "0.0", but they're inserted as " .0". I can probably fix that by changing the edit code, from 'P' to something else. What I'm wondering is this: I think that by using the %EDITC, the fields are being interpreted as Character Data, rather than numeric. It works in DB2, which is somewhat forgiving, but, when going to the Microsoft world of a .DBF, maybe it's choking and reporting a syntax error???? When I look at the statement, it looks valid. I'm going to do further testing tomorrow at work with the 'server' guy.
No, that's not what's happening. Remember, you're sending a 100% character statement to the RPG2SQL server. In your example above, the '123 ' is going to be seen as a character value, and the 3.0 will be seen as a numeric value. What's the difference? One has quotes one doesn't. Things to look at: - You may be quoting a value that is supposed to be numeric or vice versa. - The OBDC driver may not understand the insert syntax you are using. As far as the possible syntax error: Maybe you need a space between TRANSACT.DBF (DISTRICT Maybe you need double quotes instead of single ones. Hopefully, you've got a PC tool that can make use of the ODBC driver directly. (Basically like iSeries Nav "Run SQL Scripts") Once you get a working statement, the same statement should work through RPG2SQL. The thing to remember is that the syntax of the statement you're sending has to be what the ODBC driver is expecting. Not the syntax you'd use directly on the iSeries for the same statement. RPG2SQL doesn't translate from iSeries syntax to dBase/SQL Server/Access ect. HTH, Charles Wilt -- iSeries Systems Administrator / Developer Mitsubishi Electric Automotive America ph: 513-573-4343 fax: 513-398-1121
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.