Re: EuCom

new topic     » goto parent     » topic index » view thread      » older message » newer message 

Yes that was just example, in reality I need to go thru every record in
table from first till last and check some fields, and depending on value of
those fields set value of other field in same record. That's all I want to
do. And I can't do it in Euphoria, ODBC API is so complicated it's driving
me nuts :(. Lost whole week trying to figure it out.
MFC wrapps ODBC API with 4000 lines file, that gives you idea how
complicated is ODBC API.
I thought about wrapping MFC dll, but MFC uses C++ classes so how would that
work?

Your example probably wouldn't speed things much up as you said, but using
SQLSetPos with SQL_UPDATE would.
I have this C++ code (below, it's easier in this case to test it in C++) and
it doesn't work, last SQLSetPos() fails. It gets correct data from table,
but it's unable to update it with new data. I followed example and
instructions from MSDN exactly and it still doesn't work :(.

Now I have these options:
- Write this whole part in C++ and make dll out of it (I can't use ADO in
C++(had some problems), so I would need to use MFC, complications again).
- You will fix EuCom and I will use ADO.
    (BTW Eucom crashed with Windows error on WinXP and other computer which
has Win98 installed when I run comtest.exw.)
- Wrap MFC dll?
- Continue to investiagte how to use ODBC API correctly :( :( :(.

void test2 ()
{
    SQLRETURN ret;
    SQLHENV hEnv;
    SQLHDBC hDbc;
    SQLHSTMT hStmt;
    SQLUSMALLINT RowStatusArray[10], Action, RowNum;
    SQLCHAR  NameArray[11][51];
    SQLINTEGER NameLenOrIndArray[11];
    //
    // Connect to database:
    //=>
        SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
     SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
     SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
        SQLSMALLINT pcbConnStrOut;
        ret = SQLDriverConnect(hDbc,
      NULL,
      (SQLCHAR*)"DSN=EON Files Sorter Test;uid=sa;pwd=;database=EON Files
Sorter Test",
      SQL_NTS,
      (SQLCHAR*)NULL,
      0,
      &pcbConnStrOut,
      (SQLUSMALLINT)SQL_DRIVER_NOPROMPT);
        assert (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO);
        SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    //
    // Prepare some things:
    //=>
        // Set the SQL_ATTR_ROW_BIND_TYPE statement attribute to use
column-wise binding.
        // Declare the rowset size with the SQL_ATTR_ROW_ARRAY_SIZE
statement attribute.
        // Set the SQL_ATTR_ROW_STATUS_PTR statement attribute to point to
the row status
        // array.
        SQLSetStmtAttr(hStmt, SQL_ATTR_CURSOR_TYPE, (void
*)SQL_CURSOR_KEYSET_DRIVEN, 0);
        SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN,
0);
        SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (void *)10, 0);
        SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_STATUS_PTR, RowStatusArray, 0);
        // Bind arrays to the CustID, Name, Address, and Phone columns.
        SQLBindCol(hStmt, 2, SQL_C_CHAR, NameArray, sizeof(NameArray[0]),
NameLenOrIndArray);
    //
    // Get data from database?:
    //=>
        // Execute a statement to retrieve rows from the Customers table.
        ret = SQLExecDirect(hStmt, (unsigned char *)"SELECT * FROM
SelectiveReception", SQL_NTS);
        assert (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO);
        ret = SQLFetchScroll(hStmt, SQL_FETCH_NEXT, 0);
        assert (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO);
    //
    // Update database:
    //=>
        strcpy ((char *)NameArray [0], "new value");
        ret = SQLSetPos(hStmt, 0, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
        assert (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO);
        SQLCloseCursor(hStmt);
}


----- Original Message -----
From: "Matthew Lewis" <matthewwalkerlewis at YAHOO.COM>
To: "EUforum" <EUforum at topica.com>
Sent: Monday, July 15, 2002 8:04 PM
Subject: RE: EuCom


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

new topic     » goto parent     » topic index » view thread      » older message » newer message

Search



Quick Links

User menu

Not signed in.

Misc Menu