1. RE: problem
Topica clipped out some of the previous message..
I'm trying to write a functon to convert a sequence (of numbers and
strings) to a long string sequence.
i.e {1,2,{2002,12,30}} to "(1,2,"2002-12-30")"
the problem is to distinguish between the string "123" and the sequence
{1,2,3}.
they both have a length of 3 and all their elements are atoms.
can you improve on this? as it stands it has problems.
-- converts a sequence of atoms and strings to string
-- for use in SQL statements
-- ( i.e. "insert into aTable values" & toString(record))
-- would be how it is used.
if atom(s[i]) then
out &= str(s[i]) else ---<- this "str" function I wrote
if length(s[i]) = 3 then
if atom(s[i][1]) and atom(s[i][2]) and atom(s[i][3]) then
out &= "\"" & s[i] & "\", "
end if
end if
end for
out[length(out)] = ')'
return out
end function
thanks for any suggestions
george
2. RE: problem
> 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
3. RE: problem
You might try using single quotes instead of double. Also, some databases
like to see # #s around dates: #1998-09-16#
Matt Lewis
> -----Original Message-----
> From: George Walters [mailto:gwalters at sc.rr.com]
> Subject: Re: problem
>
>
>
> Matt,
>
> The error reported from "odbcError(-tmp)" is: I can't cut and
> paste it, I've
> type'd it
>
> ODBC Error #42000: 42000[MySQL][IDBC 3,51
> /druver][mysqld-3,23.54-nt]You
> have an error in your SQL syntax
> near " at line2
>
> "record" var is attached.
>
> Here's the code for getColumnTypes.... all I've done is to
> change the name
> of getColumnData to getColumnTypes and return data_type
> instead of data.
> Seems to work.
>
> --/func getColumnTypes( atom hstmt )
> --Pass the handle for the statement in question, and a
> sequence of column
> types
> --will be returned to be used on converting outbound data
> going back to the
> --data base. If hstmt is 0, uses the current handle.
> global function getColumnTypes( atom hstmt )
> object data, data_type
> atom h_ptr, ok, mset, handle, ptr1, ptr2, ptr3, ptr4, ptr5, ptr6,
> ptr7, len, hs, rec_count
> integer cols, row
>
> if not hstmt then
> hstmt = current_handle
> else
> current_handle = hstmt
> end if
>
> hs = getHandleODBC( hstmt)
>
> h_ptr = allocate( 4 )
> poke4( h_ptr, 0 )
> ok = c_func( SQLNumResultCols, { hs, h_ptr } )
>
> if not find( ok, { SQL_SUCCESS, SQL_SUCCESS_WITH_INFO } ) then
> --odbcError( hstmt )
> free( h_ptr )
> return -hstmt
> end if
>
> cols = peek4u( h_ptr )
> data = { repeat( {}, cols ) }
> data_type = repeat( 0, cols )
>
> ptr1 = allocate( 256 )
> ptr2 = allocate( 4 )
> ptr3 = allocate( 4 )
> ptr4 = allocate( 4 )
> ptr5 = allocate( 4 )
> ptr6 = allocate( 4 )
> poke4( ptr3, 0 )
>
> row = 1
> for i = 1 to cols do
> ok = c_func( SQLDescribeCol, { hs, i, ptr1, 256,
> ptr2, ptr3, ptr4, ptr5, ptr6 } )
>
> data[row][i] = peek_string( ptr1 )
> data_type[i] = peek4s( ptr3 )
> if data_type[i] > #8000 then
> data_type[i] -= #10000
> end if
> end for
>
> last_datatype = data_type
> free( ptr1 )
> free( ptr2 )
> free( ptr3 )
> free( ptr4 )
> free( ptr5 )
> free( ptr6 )
>
> return data_type
> end function
>
> ----- Original Message -----
> From: "Matt Lewis" <matthewwalkerlewis at yahoo.com>
> To: "EUforum" <EUforum at topica.com>
> Sent: Monday, October 13, 2003 8:35 AM
> Subject: RE: problem
>
>
<snip>
>
>
4. RE: problem
sql_type is one of: SQL_CHAR, SQL_VARCHAR, SQL_INTEGER, SQL_NUMERIC, etc
c_type is one of: SQL_C_CHAR, SQL_C_LONG, SQL_C_DOUBLE, etc
I'll make this clearer in the docs.
Matt Lewis
> -----Original Message-----
> From: George Walters [mailto:gwalters at sc.rr.com]
> Subject: Re: problem
>
>
>
> Matt, I don't know what the sql_type is? Is that a string
> like "float(8,2)"?? or is it some numeric representation.
>
> george
>
> ----- Original Message -----
> From: "Matt Lewis" <matthewwalkerlewis at yahoo.com>
> To: "EUforum" <EUforum at topica.com>
> Sent: Monday, October 13, 2003 8:35 AM
> Subject: RE: problem
>
>
> > > 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
> >
> >
> > TOPICA - Start your own email discussion group. FREE!
> >
> >
>
>
> TOPICA - Start your own email discussion group. FREE!
>
>
>
