RE: Numeric fields in SQL String Statement

-----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