OpenEuphoria

RE: EuCom

• Posted by Matthew Lewis <matthewwalkerlewis at YAHOO.COM> Jul 15, 2002
• 417 views

> -----Original Message-----
> From: Tone 10963508 at europeonline.com [mailto:10963508 at europeonline.com]
 
> procedure test_odbc_speed ()
>     atom t1
>     object sr_data
>     STRING guid
> 
>     sr_data = execDirectODBC (H_Fazzt_Conn, "SELECT * FROM
> SelectiveReception")
>     if not sequence (sr_data) then
>         odbcError (-sr_data)
>         return
>     end if
> 
>     puts (1, "start test\n")
>     t1 = time ()
>     for i = 1 to length (sr_data) do
>         guid = sr_data [i] [SR_GUID]
>         Void = execDirectODBC (H_Fazzt_Conn,
>             "UPDATE SelectiveReception SET Ignore = 2 WHERE 
> (GUID = '" &
> guid & "')")
>     end for
>     ? time () - t1
> end procedure

I assume this is just an example, because you could really speed this query
up simply by running the query:

"UPDATE SelectiveReception SET Ignore = 2"

There are some more general things you can do to speed up processing.
Although I never wrote a nice wrapper for it, you'll want to use
SQLPrepare/SQLExecute for your UPDATE.  It will save time parsing.  You'll
need to use a parameter (SQLBindParameter) for GUID, and set that each time.
I suspect that the total volume of hard drive access isn't really any
different for either case.

SQLBindParameter allows you to set up a buffer for a parameter and poke the
value in.  You don't need to do any sort of parsing each time to run the
query.  All of the functions and constants should already be declared in
odbc.ew.  So something like this:

-- start code
atom hstmt
hstmt = allocHandleODBC( SQL_HANDLE_STMT, H_Fazzt_Conn )
atom sql_ptr
sequence sql
sql = "UPDATE SelectiveReception SET Ignore = 2 WHERE GUID = ?"
sql_ptr = allocate_string(sql)

-- the ODBC driver will parse the query here
VOID = c_func( SQLPrepare, {hstmt,sql_ptr,length(sql)+1})
free(sql)

-- sr_data is the result of your query
guid_ptr = allocate_string(sr_data[1][SR_GUID)
strlen_ptr = allocate(4)
poke4(strlen_ptr( length( sql ) + 1 )

-- bind guid_ptr to the '?' in the SQL statement
VOID = c_func(SQLBindParameter,{hstmt,1,SQL_PARAM_INPUT,
       SQL_C_CHAR,SQL_VARCHAR,length(sql)+1,0,guid_ptr, srtlen_ptr})

for i = 1 to length(sr_data) do
    -- updates the parameter each time
    poke(guid_ptr,sr_data[i][SR_GUID)
    VOID = c_func( SQLExecute,{hstmt})
end for

-- end sample

You may need to play around with some of the parameters passed to
SQLBindParameter:

From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/od
bcsqlbindparameter.asp

SQLRETURN SQLBindParameter(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT     ParameterNumber,
     SQLSMALLINT     InputOutputType,
     SQLSMALLINT     ValueType,
     SQLSMALLINT     ParameterType,
     SQLUINTEGER     ColumnSize,
     SQLSMALLINT     DecimalDigits,
     SQLPOINTER     ParameterValuePtr,
     SQLINTEGER     BufferLength,
     SQLINTEGER *     StrLen_or_IndPtr);

Matt Lewis