Having some difficulty with dbmysql..

BlitzMax Forums/Brucey's Modules/Having some difficulty with dbmysql..

LT	(Posted 2013) [#1]

Hello, I downloaded the database and dbmysql modules back in 2011 and just played long enough to get the basics working. Recently, I decided to look into it again as I am in need of database features for my engine.

The new implementation was pretty easy and I had my own world objects saving and retrieving meta data. So far, so good.

The problem is that at some point, I decided to recompile the modules and now they are not retrieving data properly, though I can still do DROP and INSERT statements. I had recently updated BlitzMax to 1.48, so I thought that was the problem. But as of right now, I am unable to successfully compile a functional mysql module - but it reports no errors (!).

At some point, I did switch to a later version of libmysql.dll, but it's 5.5 so I assume that should still work. The reason I recompiled in the first place is that I was unable to get blobs to work. I keep getting an error..

(3) Error resetting statement (4068)

Any ideas? Has something changed in 1.48 that could cause this issue?

EDIT: I've also tried libmysql.dll from Connector 6.1.2 and that gives me an "Entry Point Not Found" error with KERNAL32.dll.

LT	(Posted 2013) [#2]

Well now, this is very confusing. I did manage to get the module working again (sort of), but I had to comment out a line in dbmysql..

in method TMySQLResultSet.nextRow()

If preparedQuery Then

...

Else

'If Not bmx_mysql_rowField_isNull(row, i) Then
'^^Commenting this out makes non-prepared queries work again!!??^^

Endif

Looking at the C source, I can see nothing wrong with it, but it was returning a non-zero value when it shouldn't. Whatever conditions I had two years ago that generated a working module - I am unable to reproduce them now - without commenting out that line. I can't help but wonder if there is some CC_OPT or something that I'm missing.

LT	(Posted 2013) [#3]

Even with that issue (somewhat) sorted out, I can't get any of the binding stuff to work.

Deciding to start a clean build - I downloaded the zips containing database_1.08 and dbmysql_1.06. I have a MySQL 5.5 database set up and grabbed libmysql.dll from Connector 6.0.2. The modules compile with no errors, but none of the tests work!?

Very odd, indeed. :/

Henri

(Posted 2013) [#4]

Hello,

where did you download the database module ?

-Henri

LT	(Posted 2013) [#5]

Hello,

I downloaded them both at MaxMods at GoogleCode. Now I realize the first issue had something to do with the bool return value. Still don't know why it was a problem.

I decided to try making a stripped-down version of the module and run through the MySql (textbook) example. Still have not got bindings working. :(

LT	(Posted 2013) [#6]

Here's the stripped down version of the module. It's basically just the common part of dbmysql wrapping the mysql and bmx_mysql functions.

Any external imported files are unchanged.

Module test.MySQL

ModuleInfo "Version: 1.06a"
ModuleInfo "Author: Bruce A Henderson"
ModuleInfo "License: BSD"
ModuleInfo "Copyright: Wrapper - 2007-2009 Bruce A Henderson"

?Win32
ModuleInfo "LD_OPTS: -L%PWD%/lib/win32"
?

Import pub.Win32
Import brl.Blitz
Import brl.FileSystem
Import brl.LinkedList
Import brl.Map
Import brl.Math
Import brl.StandardIO

Import "-lmysql"
Import "include/*.h"
Import "mysqlhelper.cpp"

Extern "win32"
	Function mysql_close(handle:Byte Ptr)
	Function mysql_ping:Int(handle:Byte Ptr)
	Function mysql_query:Int(handle:Byte Ptr, query:Byte Ptr)
	Function mysql_errno:Int(handle:Byte Ptr)
	Function mysql_error:Byte Ptr(handle:Byte Ptr)
	Function mysql_init:Byte Ptr(h:Int = 0)
	Function mysql_get_client_version:Int()
	Function mysql_real_connect:Int(handle:Byte Ptr, host:Byte Ptr, user:Byte Ptr, ..
		passwd:Byte Ptr, db:Byte Ptr, port:Int, unix_socket:Byte Ptr, client_flag:Int)
	Function mysql_select_db:Int(handle:Byte Ptr, db:Byte Ptr)
	Function mysql_set_character_set(handle:Byte Ptr, text:Byte Ptr)
	Function mysql_get_server_version:Int(handle:Byte Ptr)
	Function mysql_real_query:Int(handle:Byte Ptr, query:Byte Ptr, size:Int)
	Function mysql_store_result:Byte Ptr(handle:Byte Ptr)
	Function mysql_field_count:Int(handle:Byte Ptr)
	Function mysql_fetch_field_direct:Byte Ptr(handle:Byte Ptr, index:Int)
	Function mysql_stmt_fetch:Int(stmt:Byte Ptr)
	Function mysql_fetch_row:Byte Ptr(stmt:Byte Ptr)
	Function mysql_free_result(result:Byte Ptr)
	Function mysql_stmt_close:Int(stmt:Byte Ptr)

	Function mysql_affected_rows:Int(handle:Byte Ptr)
	
	Function mysql_stmt_init:Byte Ptr(handle:Byte Ptr)
	Function mysql_stmt_prepare:Int(stmt:Byte Ptr, query:Byte Ptr, size:Int)
	Function mysql_stmt_param_count:Int(stmt:Byte Ptr)
	Function mysql_stmt_reset:Int(stmt:Byte Ptr)
	Function mysql_stmt_bind_param:Int(stmt:Byte Ptr, bind:Byte Ptr)
	Function mysql_stmt_execute:Int(stmt:Byte Ptr)
	Function mysql_stmt_error:Byte Ptr(stmt:Byte Ptr)
	Function mysql_stmt_affected_rows:Int(stmt:Byte Ptr)
	Function mysql_stmt_result_metadata:Byte Ptr(stmt:Byte Ptr)
	
	Function mysql_num_fields:Int(meta:Byte Ptr)
	Function mysql_num_rows:Int(meta:Byte Ptr)
	Function mysql_fetch_lengths:Byte Ptr(result:Byte Ptr)
	Function mysql_fetch_field:Byte Ptr(meta:Byte Ptr)
	Function mysql_stmt_bind_result:Int(stmt:Byte Ptr, bindings:Byte Ptr)
	Function mysql_stmt_store_result:Int(stmt:Byte Ptr)
	
	Function mysql_list_tables:Byte Ptr(handle:Byte Ptr, name:Byte Ptr)
End Extern

Extern
	Function bmx_mysql_field_name:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_org_name:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_table:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_org_table:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_db:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_catalog:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_def:Byte Ptr(f:Byte Ptr)
	Function bmx_mysql_field_length:Int(f:Byte Ptr)
	Function bmx_mysql_field_max_length:Int(f:Byte Ptr)
	Function bmx_mysql_field_flags:Int(f:Byte Ptr)
	Function bmx_mysql_field_type:Int(f:Byte Ptr)
	Function bmx_mysql_field_decimals:Int(f:Byte Ptr)
	
	Function bmx_mysql_makeBindings:Byte Ptr(size:Int)
	Function bmx_mysql_deleteBindings(bindings:Byte Ptr)
	'Function bmx_mysql_makeBools:Byte Ptr(size:Int)
	Function bmx_mysql_setBool(bools:Byte Ptr, index:Int, isNull:Int)
	Function bmx_mysql_deleteBools(bools:Byte Ptr)
	
	Function bmx_mysql_bind_int(params:Byte Ptr, index:Int, value:Int Ptr)
	Function bmx_mysql_bind_float(params:Byte Ptr, index:Int, value:Float Ptr)
	Function bmx_mysql_bind_double(params:Byte Ptr, index:Int, value:Double Ptr)
	Function bmx_mysql_bind_long(params:Byte Ptr, index:Int, value:Long Ptr)
	Function bmx_mysql_bind_string(params:Byte Ptr, index:Int, value:Byte Ptr, size:Int)
	Function bmx_mysql_bind_date(params:Byte Ptr, index:Int, value:Byte Ptr, _year:Int, _month:Int, _day:Int)
	Function bmx_mysql_bind_time(params:Byte Ptr, index:Int, value:Byte Ptr, _hour:Int, _min:Int, _sec:Int)
	Function bmx_mysql_bind_datetime(params:Byte Ptr, index:Int, value:Byte Ptr, _year:Int, _month:Int, _day:Int, _hour:Int, _min:Int, _sec:Int)
	Function bmx_mysql_bind_blob(params:Byte Ptr, index:Int, value:Byte Ptr, size:Int)
	
	Function bmx_mysql_makeVals:Byte Ptr(size:Int)
	Function bmx_mysql_deleteVals(vals:Byte Ptr)
	
	Function examine_bindings(params:Byte Ptr, size:Int, stmt:Byte Ptr)
	Function bmx_mysql_getLength:Int(lengths:Byte Ptr, index:Int)
	Function bmx_mysql_rowField_isNull:Int(row:Byte Ptr, index:Int)
	Function bmx_mysql_rowField_chars:Byte Ptr(row:Byte Ptr, index:Int)
	
	Function bmx_mysql_inbind(params:Byte Ptr, index:Int, _field:Byte Ptr, dataValue:Byte Ptr, dataLength:Int Ptr, isNull:Int Ptr)
	Function bmx_mysql_stmt_bind_result:Int(stmt:Byte Ptr, bindings:Byte Ptr)
	Function bmx_mysql_stmt_fetch:Int(stmt:Byte Ptr)
	
	Function bmx_mysql_stmt_bind_param:Int(stmt:Byte Ptr, bindings:Byte Ptr)
	Function bmx_mysql_getBindings:Byte Ptr(stmt:Byte Ptr)
	Function bmx_mysql_getParams:Byte Ptr(stmt:Byte Ptr)

	Function bmx_mysql_stmt_insert_id(stmt:Byte Ptr, id:Long Ptr)
	Function bmx_mysql_insert_id(handle:Byte Ptr, id:Long Ptr)
	
	Function bmx_mysql_makeTime:Byte Ptr()
	Function bmx_mysql_deleteTime(handle:Byte Ptr)
	
End Extern

Extern
	Function _strlen:Int( s:Byte Ptr ) = "strlen"
End Extern

Function convertUTF8toISO8859:String( s:Byte Ptr )
	Local l:Int = _strlen( s )
	Local b:Short[] = New Short[l]
	Local bc:Int = -1
	Local c:Int
	Local d:Int
	Local e:Int
	For Local i:Int = 0 Until l
		bc :+ 1
		c = s[i]
		If c < 128
			b[bc] = c
			Continue
		End If
		i :+ 1
		d = s[i]
		If c < 224 
			b[bc] = ( c - 192 ) * 64 + ( d - 128 )
			Continue
		EndIf
		i :+ 1
		e = s[i]
		If c < 240 
			b[bc] = ( c - 224 ) * 4096 + ( d - 128 ) * 64 + ( e - 128 )
			If b[bc] = 8233 Then
				b[bc] = 10
			End If
			Continue
		EndIf
	Next
	Return String.fromshorts( b, bc + 1 )
End Function

Function convertISO8859toUTF8:String( text:String )
	If Not Text Then Return ""
	Local l:Int = text.length
	If l = 0 Then Return ""
	Local count:Int = 0
	Local s:Byte[] = New Byte[l * 3]
	For Local i:Int = 0 Until l
		Local char:Int = text[i]
		If char < 128 Then
			s[count] = char
			count :+ 1
			Continue
		Else If char < 2048
			s[count] = char/64 | 192
			count :+ 1
			s[count] = char Mod 64 | 128
			count :+ 1
			Continue
		Else
			s[count] =  char/4096 | 224
			count :+ 1
			s[count] = char/64 Mod 64 | 128
			count :+ 1
			s[count] = char Mod 64 | 128
			count :+ 1
			Continue
		EndIf
	Next
	Return String.fromBytes( s, count )
End Function

Function sizedUTF8toISO8859:String( s:Byte Ptr, size:Int )
	Local l:Int = size
	Local b:Short[] = New Short[l]
	Local bc:Int = -1
	Local c:Int
	Local d:Int
	Local e:Int
	For Local i:Int = 0 Until l
		c = s[i]
		If c = 0 Continue
		bc :+ 1
		If c < 128
			b[bc] = c
			Continue
		EndIf
		i :+ 1
		d = s[i]
		If c < 224
			b[bc] = ( c - 192 ) * 64 + ( d - 128 )
			Continue
		EndIf
		i :+ 1
		e = s[i]
		If c < 240
			b[bc] = ( c - 224 ) * 4096 + ( d - 128 ) * 64 + ( e - 128 )
			If b[bc] = 8233 Then b[bc] = 10
			Continue
		EndIf
	Next
	Return String.fromshorts( b, bc + 1 )
End Function

'Not sure if/how these are used..?
Const CLIENT_FOUND_ROWS:Int = 2
Const CLIENT_NO_SCHEMA:Int = 16
Const CLIENT_COMPRESS:Int = 32
Const CLIENT_LOCAL_FILES:Int = 128
Const CLIENT_IGNORE_SPACE:Int = 256
Const CLIENT_INTERACTIVE:Int = 1024
Const CLIENT_SSL:Int = 2048
Const CLIENT_IGNORE_SIGPIPE:Int = 4096
Const CLIENT_MULTI_STATEMENTS:Int = 1 Shl 16
Const CLIENT_MULTI_RESULTS:Int = 1 Shl 17
Const CLIENT_REMEMBER_OPTIONS:Int = 1 Shl 31

And here's the simple test case (local database).

Framework test.MySQL

'---------------------------------------------------------------------------------------------------'
'Open MySQL Database.
Local clientVersion = mysql_get_client_version()
Local dbname$ = "test"
Local host$ = Null
Local port = 0
Local user$ = "root"
Local password$ = "pass"
Local options$ = Null
Local server$ = Null
Local handle:Byte Ptr = mysql_init( 0 )
If Not handle
	Print "ERROR: Could not open database."
	End
EndIf
If Not mysql_real_connect( handle, host, user, password, dbname, port, server, options.ToInt() )
	Print "ERROR: Could not open database."
	End
EndIf
If mysql_select_db( handle, dbname ) Then
	Print "ERROR: Could not open database."
	End
EndIf
If clientVersion >= 50007 Then
	If mysql_set_character_set( handle, "utf8" )
		Print "WARNING: Could not set 'utf8.'"
		End
	EndIf
End If
'Local serverVersion = mysql_get_server_version( handle )
'Local isOpen = True

'---------------------------------------------------------------------------------------------------'
'Drop existing table.
Local s1$ = "DROP TABLE if exists person"
mysql_query( handle, s1 )

'---------------------------------------------------------------------------------------------------'
'Create new table.
Local s2$ = "CREATE TABLE if not exists person ( " + ..
	"id integer NOT NULL AUTO_INCREMENT, " + ..
	"forename varchar(30), " + ..
	"value integer, " + ..
	"primary key(id) )"
mysql_query( handle, s2 )

'---------------------------------------------------------------------------------------------------'
'Create prepare statement.
Local s3$ = "INSERT INTO person values (NULL, ?, ?)"
Local stHandle:Byte Ptr = mysql_stmt_init( handle )
Local q$ = convertISO8859toUTF8( s3 )
Local prepres = mysql_stmt_prepare( stHandle, q, q.length )
Print "Prep: " + prepres
Local pcnt = mysql_stmt_param_count( stHandle ) '+ 1
Print "Params: " + pcnt

'---------------------------------------------------------------------------------------------------'
'Set up bindings.
If pcnt = 2 Then
	Local bindings:Byte Ptr = bmx_mysql_makeBindings( pcnt )
	Local pstr$ = "Oklahoma"	'Param 1
	Local pint = 42			'Param 2
	bmx_mysql_bind_string( bindings, 0, convertISO8859toUTF8( pstr ).ToCString(), pstr.length )
	bmx_mysql_bind_int( bindings, 1, Varptr pint )
	Print "Bind = " + bmx_mysql_stmt_bind_param( stHandle, bindings ) ; Print
	examine_bindings( bindings, pcnt, stHandle )
Else
	Print "ERROR: Unexpected param count!!"
EndIf

'---------------------------------------------------------------------------------------------------'
'Execute the statement.
Local exe = mysql_stmt_execute( stHandle )
If exe Then Print "ERROR: Statement could not be executed - " + exe

mysql_stmt_close( stHandle )

Running the test case results in the existing table being wiped and replaced and one new entry is added, but without the bound values!!

/scratches head

Henri

(Posted 2013) [#7]

Latest version of the module can be found from SVN-repository. The version in website is outdated.

-Henri

LT	(Posted 2013) [#8]

Ah, I had grabbed them from there before, but not for some time. The only difference I could find is the libmysql.a file in lib/win32, but that made a difference! At least the original tests work now, so thanks!! :)

Also have to say, the sheer number of modules there is quite impressive!

EDIT: Different set of includes, also. Now I feel sheepish. :/