OpenEuphoria

1. Matt's ODBC wrapper and Mysql

• Posted by George Walters <gwalters at sc.rr.com> Nov 01, 2004
• 678 views

Matt, I think there is a problem with error handeling in execDirect. Or
I'm handeling it wrong (most likely). I had a syntax error in the SQL
statement and odbcError reported an "invalid handle". Here's how I'm using
it. The value returned for "a" was -29. On one of the SQL fields I had {48}
instead of "0" which caused this error.

a = execDirectODBC(dbId, "replace " & openTbNames[t] &
							 " values (" & packRecord( t, recordKey, recordDb) & ")")
	if atom(a) then
		odbcError(-a)
		a = message_box("Can't find record "&recordKey&" to update.","",0)
		abort(1)
	else

george

3. Re: Matt's ODBC wrapper and Mysql

• Posted by George Walters <gwalters at sc.rr.com> Nov 01, 2004
• 624 views

Matt, You're right, the message box should have displayed a syntax error at some
point
on the line. It has done that in the past. The exact text was the same
as in your odbcError routine, error #0: "invalid ODBC handle". The return
code was -29. I printed the sql statment and saw there was an error, fixed
it and the "invalid ODBC handle" message went away.

george

5. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Nov 03, 2004
• 651 views

George Walters wrote:
> 
> 
> Matt, It seems that all syntax errors in an SQL statement are now being
> reported as invalid handle. I beleive this began when I upgraded MySql
> to their current version 4.015. At least this has been the case of the 
> last 3 or 4 bugs of mine. On the older MySql it always seemed to report the
> position on the line and showed the text following that position. Have you
> found anything that might remedy this?

I don't know.  I have v4.0.21, and here's the error message I got
from a bad query:

ODBC Error 0: [4] [MySQL][ODBC 3.51 Driver][mysqld-4.0.21-nt]Unknown column 'x'
in 'field list'

> I've also noticed from an executing program when I close the DB that my dos
> screen which is running MySql line interpreter usually loses it's connection
> to
> the data base.. any thoughts on this one? A command of "use DBname"
> reconnects.

Were you able to run queries without the 'use' command previously?  
Because I believe you either need to do the 'use db' command or add the
db name to table and field names.

Matt Lewis

7. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Nov 03, 2004
• 633 views

George Walters wrote:
> 
> Maybe it's a bug that 4.021 fixed...I'll down load it.
> 
> On the dos comand issue.
> I've always used the "use DB" to connect. But what I'm saying is after that
> and having run some commands that when an EU program uses a closeDb function
> caused
> it to
> lose it's connection to the db. Again entering the command to "use db" it will
> again connect and work as expected. Not a big issue but just curious.

Yeah, sounds like something internal to MySQL.

Matt Lewis

9. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Nov 04, 2004
• 654 views

George Walters wrote:
> 
> 
> Matt, what do you make of this? I don't know what happened. It consistently
> crashes.
> 
> C:\EUPHORIA\include\odbc.ew:972 in function getHandleODBC() 
> subscript value 0 is out of bounds, reading from a sequence of length 41 
>     id = 0
> 
> ... called from C:\EUPHORIA\include\odbc.ew:1156 in procedure freeHandleODBC()
>
>     id = 0
> 
> ... called from C:\EUPHORIA\include\odbc.ew:1706 in function execDirectODBC() 
>
>     hconn = 2

It looks to me like there was a problem in allocating the statement
handle from within runDirectODBC, however, the error isn't trapped 
correctly.  Try replacing runDirectODBC and execDirectODBC with the
following, and you should be able to get a valid error for why the 
statement handle wasn't created:

--/topic API
--/func runDirectODBC( atom hconn, sequence sql )
--Same as /execDirectODBC(), but runDirectODBC() does not return any data.  You
--must use /getColumnHeaders() and /getData().  This function returns an ODBC
--statement handle, which must be freed by the application ( /freeHandleODBC).
global function runDirectODBC( atom hconn, sequence sql )
    sequence data, data_type
    atom hstmt, h_ptr, ok, mset, handle, ptr1, ptr2, ptr3, ptr4, ptr5, ptr6,
        ptr7, hs
    integer cols, row
    atom len
    
    data = {}
--    mset = new_memset()
    h_ptr = allocate( 4 )
    
    hstmt = allocHandleODBC( SQL_HANDLE_STMT, hconn )
    free( h_ptr )
    if not hstmt then
        return 0
    end if
    
    hs = getHandleODBC( hstmt )
    h_ptr = allocate_string( sql )
    
    ok = c_func( SQLExecDirect, { hs, h_ptr, length( sql ) })
    free( h_ptr )    
    if not SQL_SUCCEEDED( ok ) then
        
        return -hstmt
    end if
    
    return hstmt
    
end function

--/topic API
--/func execDirectODBC( atom hconn, sequence sql )
--
--Returns the results of query sql_text.  This function ignores the settings
made
--by /setMaxRecords.  If no errors occur, /execDirectODBC frees the statement
handle
--it creates.  If there is an error, a negative handle is returned, so
--that the application can query for errors.  The returned handle may be either
the
--statement or the connection, if there was an error in allocating the handle.
--The statment handle (if one was created) must then be freed by
--the application ( /freeHandleODBC) to avoid resource leakage.
global function execDirectODBC( atom hconn, sequence sql )
    object data
    atom hstmt, records
    
    hstmt = runDirectODBC( hconn, sql )
    
    if hstmt > 0 then
        current_handle = hstmt
        records = max_records
        max_records = -1
        data = {getColumnHeaders( hstmt )} & getData( hstmt ) 
        max_records = 1
	    -- Josef Jindra:
	    freeHandleODBC( hstmt )
    elsif hstmt then
        data = hstmt
    else
    	data = - hconn
    end if

    return data
end function

Matt Lewis

11. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Nov 04, 2004
• 643 views

George Walters wrote:
> 
> It made the fix and it now returns "ODBC error #0: Invalid ODBC Handle".
> Don't know why.
> 
> BTW: MySQL 4.1.7 in now the suggested download however I could not get it 
> to install. It quit during the setup when trying to start the service, with
> no help on why or what to do. I installed 4.0.22 and it's working but with
> the above error. This program last worked with 4.0.15. Soooo, I'm at a loss.

I don't know what to tell you.  It seems like a MySQL thing.  The only
other thing I'd recommend is use prepare/execute getData rather than
execDirect, so that you can more easily see where the error is happening.
If you're getting invalid handle from here, it sounds like there's a 
problem with the connection.  For kicks, you could try disconnecting
and then reconnecting.

Matt Lewis

13. Re: Matt's ODBC wrapper and Mysql

• Posted by George Walters <gwalters at sc.rr.com> Nov 05, 2004
• 623 views

Matt, would you know how to track down "error #124 from the table Handler "
ODBC interface? About 8 or so reads through a program that loads gl figures
into a financial format, I get that message returned from ODBC. I've searched
through the MySQL docs for "124" and don't find a match.

george

15. Re: Matt's ODBC wrapper and Mysql

• Posted by George Walters <gwalters at sc.rr.com> Nov 09, 2004
• 631 views

Matt, don't worry about that one. The data base appeared corrupted. 
After the message, junked up data was being returned,then a few minutes 
later it cleared up. I did nothing to clear it up. It must have been a 
MySQL bug that appeared and went away. I'm thinking it was perhaps a 
buffer issue. I'm testing update programs and periodically copy a backup 
set of data back to the set I'm using. I think that was what confused 
it. Would this make sense?

The error messaged text was what was returned from execDirect. That was 
pretty close to the text. I couldn't clip it from the messageBox.

george


Matt Lewis wrote:
> 
> 
> posted by: Matt Lewis <matthewwalkerlewis at yahoo.com>
> 
> 
> George Walters wrote:
> 
>>Matt, would you know how to track down "error #124 from the table Handler"
>>ODBC interface? About 8 or so reads through a program that loads gl figures
>>into a financial format, I get that message returned from ODBC. I've searched
>>through the MySQL docs for "124" and don't find a match.
> 
> 
> What function did you call that returned the error?  Is that the actual
> error message that is returned?
> 
> Matt Lewis
> 
> 
> 
>

17. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Oct 20, 2004
• 640 views

George Walters wrote:
> 
> 
>  Matt, I'm at a road block using your ODBC wrapper and Mysql. I'm getting
>  "out of  memory" reported from XP and the program then crashes with an
>  ODBC error.I've tried this with several rather simple programs. I'm 
>  simply reading "next larger key" through an inventory file. Here's the
>  function that reads next records:

Which version do you have?  v1.31.1 fixed a resource leak in 
execDirectODBC().  This could be the cause of your problem.

Matt Lewis

19. Re: Matt's ODBC wrapper and Mysql

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Oct 21, 2004
• 634 views
• Last edited Oct 22, 2004

George Walters wrote:
> 
>  I'm simply reading "next larger key" through an inventory file. 
> Here's the  function that reads next records:
> 
> }}}
<eucode>
> 
>    recDb = execDirectODBC(dbId,"select * from " & openTbNames[t] &
>                                " where dbKey > \""    & lastKey[t] & "\" limit
>                                1")
> 
> </eucode>
{{{


George, if you're doing this to run through the table, you might consider
using createCursor() and moveToRecord():

the_cursor = createCursor( dbId, "select * from " & openTbNames[t] &
                                 " order by dbKey" )

key = moveToRecord( the_cursor, SQL_FETCH_FIRST, 1 )
while length( key ) do
    -- ...do stuff with data

    key = moveToRecord( the_cursor, SQL_FETCH_NEXT, 1 )
end while

Matt Lewis

PS Jonas sent me some changes that allow the library to work under Linux
using UnixODBC.  I hope to have v1.33 tested and released by next week.