OpenEuphoria

RE: problem

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Oct 13, 2003
• 498 views

> From: George Walters [mailto:gwalters at sc.rr.com] 
> 
> After listening to forum comments (thank you) and thinking a 
> bit I don't beleive this problem has a solution. What I have 
> done is to use a modified version of Matt's ODBC function 
> getColumnData which returns the data from an SQL statement. I 
> now have a getColumnTypes which returns data_type for each 
> column of the table. Using this I know how to convert the 
> data into an SQL statement to insert or replace the row..... 
> at least that's my approach. I'm getting an error back as follows.

Can you post the code for getColumnData?  
 
> syntax error near " at line 2

Is this the exact error report?
 
> anyone know what line 2 is?? The statement is
> 
> tmp = execDirectODBC(connId, "insert into arCust values " & record )
> 
> record is the string I've converted using data_type from the 
> data base. I see nothing wrong with record, but it does have 
> lots of "s.  I'm beginning to wonder if my string is too long 
> and the system is breaking it into multiple lines and is 
> breaking it at an inconvinent place?? record is approx 259 
> chars long. Any body know?

Can you post the value of record?

I suspect that the solution is to probably use a prepared statement using
parameters:

-- use however many ?'s as there are fields
ins = prepareSQL( connId, "insert into arCust values ( ?, ?, ?,..." )

-- to insert a record:
for i = 1 to length(record) do
    -- where c_type is a sequence of the c data types you're using
    -- and sql_type is a sequence of the sql data types
    setParameter( ins, i, record[i], c_type[i], sql_type[i] )
end for
result = executeSQL( ins )

For instance, for a date, you could use a string c type and a sql date type.
That tells the ODBC driver that it needs to parse the date from its string
format into whatever internal format it needs, and the sql type allows it to
do type checking on the data.  I'll try to make the docs clearer on this,
but part of the problem is that I'm learning as I go, too.

Matt Lewis