PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

LibMySQL Wrapper for MySQL Databases

https://www.purebasic.fr/english/viewtopic.php?t=48634

Page 1 of 2

LibMySQL Wrapper for MySQL Databases

Posted: Wed Dec 21, 2011 4:51 pm
by VoSs2o0o
Here a code to use the "libmysql.dll" in a MySQL style or in a Purebasic style.
You can download mysql-connector-c-noinstall-6.0.2-win32.zip (für Windows x86) package from
http://dev.mysql.com/downloads/connector/c to get the dll.

05.02.13 updated, new version 1.14

History:
1.0, 21.12.11
first version :-)

1.1, 03.01.12
-added all missing commands
-some bugfixes, to make the code more "bulletproof"

1.11, 25.01.12
-Bug related to MYSQL_DatabaseColumnType / MYSQL_DatabaseColumnName fixed, Thanks Andreas21

1.12, 07.02.12
-Bug in MYSQL_CloseDatabase fixted

1.13, 04.12.12
-Bug in MYSQL_OpenDatabase fixed
-'compressflag' now enabled per default
-MYSQL_OpenDatabase has now an optional 'Flags' Parameter

1.14 05.02.13
-Lib PB 5.1 compatibility
-small Code changes f. 64Bit (can not test it, 32bit only)

Code: Select all

; German forum, English Forum 
; Author: VoSs2o0o
; Version: 1.14
; Date: 05. February 2013
; OS: Windows
;
; Library for using MySQL with the libmysql.dll 
; You can download mysql-connector-c-noinstall-6.0.2-win32.zip (für Windows x86)  package from
; http://dev.mysql.com/downloads/connector/c to get the dll.
;
; The Library supports the Purebasic Standard to connect to databases, but also supports the Mysql-Syntax
; See an example at the bottom of the code.

EnableExplicit

#MySQL_CLIENT_COMPRESS = 32 ;client_flag for compress

Global __Lib_mysql_hdl.i, __mysqllibinit, __mysql_pb_any

Structure st_MYSQL_ROW
  fields.s[255]
EndStructure

Structure st_mysql_field
  *name.STRING               ;/* Name of column */
  *org_name.STRING           ;/* Original column name, if an alias */
  *table.STRING              ;/* Table of column if column was a field */
  *org_table.STRING          ;/* Org table name, if table was an alias */
  *DB.STRING                 ;/* Database for table */
  *catalog.STRING            ;/* Catalog for table */
  *def.STRING                ;/* Default value (set by mysql_list_fields) */
  length.l                   ;/* Width of column (create length) */
  max_length.l               ;/* Max width for selected set */
  name_length.l
  org_name_length.l
  table_length.l
  org_table_length.l
  db_length.l
  catalog_length.l
  def_length.l
  flags.l                    ;/* Div flags */
  decimals.l                 ;/* Number of decimals in field */
  charsetnr.l                ;/* Character set */
  type.l                     ;/* Type of field. See enum_field_types */
  *extension
EndStructure ;MYSQL_FIELD;

Structure st_arr_of_fields
  field.st_mysql_field[0]
EndStructure

Structure st_Long_array
  long.l[0]
EndStructure

Structure st_MYSQL_Lib_Data
  dbhnd.l
  sqlresult.l
  sqlresult_row.l
  sqlresult_fields.l
  rownumber.l
  List blogparams.s()
EndStructure

Enumeration ;enum_field_types
  #MYSQL_TYPE_DECIMAL         ;DECIMAL- oder NUMERIC-Feld
  #MYSQL_TYPE_TINY            ;TINYINT-Feld
  #MYSQL_TYPE_SHORT           ;SMALLINT-Feld
  #MYSQL_TYPE_LONG            ;INTEGER-Feld
  #MYSQL_TYPE_FLOAT           ;FLOAT-Feld
  #MYSQL_TYPE_DOUBLE          ;DOUBLE- oder REAL-Feld
  #MYSQL_TYPE_NULL            ;Feld mit NULL-Typ
  #MYSQL_TYPE_TIMESTAMP       ;TIMESTAMP-Feld
  #MYSQL_TYPE_LONGLONG        ;BIGINT-Feld
  #MYSQL_TYPE_INT24           ;MEDIUMINT-Feld
  #MYSQL_TYPE_DATE            ;DATE-Feld
  #MYSQL_TYPE_TIME            ;TIME-Feld
  #MYSQL_TYPE_DATETIME        ;DATETIME-Feld
  #MYSQL_TYPE_YEAR            ;YEAR-Feld
  #MYSQL_TYPE_NEWDATE         ;DATETIME-Feld
  #MYSQL_TYPE_VARCHAR         ;VARCHAR
  #MYSQL_TYPE_BIT             ;BIT-Feld
  #MYSQL_TYPE_NEWDECIMAL=246  ;DECIMAL- oder NUMERIC-Typ für Präzisionsberechnungen
  #MYSQL_TYPE_ENUM=247        ;ENUM-Feld
  #MYSQL_TYPE_SET=248         ;SET-Feld
  #MYSQL_TYPE_TINY_BLOB=249   ;TINYTEXT
  #MYSQL_TYPE_MEDIUM_BLOB=250 ;MEDIUMTEXT
  #MYSQL_TYPE_LONG_BLOB=251   ;LONGTEXT
  #MYSQL_TYPE_BLOB=252        ;BLOB- oder TEXT-Feld (die Maximallänge wird mit max_length ermittelt)
  #MYSQL_TYPE_VAR_STRING=253  ;VARCHAR- oder VARBINARY-Feld
  #MYSQL_TYPE_STRING=254      ;CHAR- oder BINARY-Feld
  #MYSQL_TYPE_GEOMETRY=255    ;Feld eines raumbezogenen Typs
  #MAX_NO_FIELD_TYPES ;/* Should always be last */
EndEnumeration

#CLIENT_MULTI_STATEMENTS = 65536

Global NewMap __MySQLLib_Data.st_MYSQL_Lib_Data()

;{ Prototypes
Prototype.i proto_mysql_init(dbHnd)
Prototype.i proto_mysql_connect(dbHnd, host.p-ascii, user.p-ascii, passwd.p-ascii, DB.p-ascii, port.l, unix_socket.p-ascii, client_flag.l)
Prototype.i proto_mysql_error(dbHnd)
Prototype.i proto_mysql_errno(dbHnd)
Prototype.i proto_mysql_close(dbHnd)
Prototype.i proto_mysql_query(dbHnd, sql.p-ascii,Len_sql.l)
Prototype.i proto_mysql_use_result(dbHnd)
Prototype.i proto_mysql_store_result(dbHnd)
Prototype.i proto_mysql_free_result(QueryResult)
Prototype.i proto_mysql_field_count(dbHnd)
Prototype.i proto_mysql_fetch_row(SQLResult)
Prototype.i proto_mysql_fetch_fields(SQLResult)
Prototype.i proto_mysql_fetch_lengths(SQLResult)
Prototype.i proto_mysql_num_rows(SQLResult)
Prototype.i proto_mysql_num_fields(SQLResult)
Prototype.i proto_mysql_data_seek(SQLResult, Row_offset.q)
Prototype.i proto_mysql_insert_id(dbHnd)
Prototype.i proto_mysql_affected_rows(dbHnd)
Prototype.i proto_mysql_real_escape_string(dbHnd, strTo.p-ascii, *strfrom.p-ascii, length)
;}

Procedure isLastElement(List myList.l())
  If ListIndex(myList()) <= ListSize(myList()) - 1
    ProcedureReturn  #True
  EndIf
EndProcedure

Procedure mysql_lib_init(dllfile.s)
  If __Lib_mysql_hdl.i = 0
    __Lib_mysql_hdl.i = OpenLibrary(#PB_Any, dllfile)
    If __mysqllibinit = #False
      Global mysql_init.proto_mysql_init=GetFunction(__Lib_mysql_hdl.i,"mysql_init")
      Global mysql_real_connect.proto_mysql_connect=GetFunction(__Lib_mysql_hdl.i, "mysql_real_connect")
      Global __mysql_error.proto_mysql_error=GetFunction(__Lib_mysql_hdl.i,"mysql_error")
      Global mysql_errno.proto_mysql_errno=GetFunction(__Lib_mysql_hdl.i,"mysql_errno")
      Global mysql_real_query.proto_mysql_query=GetFunction(__Lib_mysql_hdl.i, "mysql_real_query")
      Global mysql_use_result.proto_mysql_use_result=GetFunction(__Lib_mysql_hdl.i,"mysql_use_result")
      Global mysql_store_result.proto_mysql_store_result=GetFunction(__Lib_mysql_hdl.i,"mysql_store_result")
      Global mysql_free_result.proto_mysql_free_result=GetFunction(__Lib_mysql_hdl.i,"mysql_free_result")
      Global mysql_field_count.proto_mysql_field_count=GetFunction(__Lib_mysql_hdl.i,"mysql_field_count")
      Global mysql_fetch_row.proto_mysql_fetch_row=GetFunction(__Lib_mysql_hdl.i,"mysql_fetch_row")
      Global mysql_fetch_fields.proto_mysql_fetch_fields=GetFunction(__Lib_mysql_hdl.i,"mysql_fetch_fields")
      Global mysql_fetch_lengths.proto_mysql_fetch_lengths=GetFunction(__Lib_mysql_hdl.i,"mysql_fetch_lengths")
      Global mysql_num_rows.proto_mysql_num_rows=GetFunction(__Lib_mysql_hdl.i,"mysql_num_rows")
      Global mysql_num_fields.proto_mysql_num_fields=GetFunction(__Lib_mysql_hdl.i,"mysql_num_fields")
      Global mysql_insert_id.proto_mysql_insert_id=GetFunction(__Lib_mysql_hdl.i,"mysql_insert_id")
      Global mysql_close.proto_mysql_close=GetFunction(__Lib_mysql_hdl.i,"mysql_close")
      Global mysql_affected_rows.proto_mysql_affected_rows=GetFunction(__Lib_mysql_hdl.i,"mysql_affected_rows")
      Global mysql_data_seek.proto_mysql_data_seek=GetFunction(__Lib_mysql_hdl.i, "mysql_data_seek")
      Global mysql_real_escape_string.proto_mysql_real_escape_string=GetFunction(__Lib_mysql_hdl.i, "mysql_real_escape_string")
      
      __mysqllibinit = #True
    EndIf
  EndIf
EndProcedure

Procedure.s mysql_error(dbHnd, requester.l = 0) 
Define *Error, Res
  
  Protected Errormsg.s, i.l, Error.l
  If mysql_errno(dbHnd) > 0
     *Error= __mysql_error(dbHnd)      
     Errormsg = PeekS(*Error) 
    If requester 
       Res= MessageRequester("MySQL error",Errormsg,#PB_MessageRequester_Ok)
    EndIf 
  EndIf 
  ProcedureReturn Errormsg 
EndProcedure 

Procedure mysql_lib_close()
  CloseLibrary(__Lib_mysql_hdl.i)
  __Lib_mysql_hdl.i = 0
EndProcedure

;PB compatible fnctions.....

Procedure.s MYSQL_DatabaseError(Databaseno)
Define dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd
  ProcedureReturn mysql_error(dbHnd) 
EndProcedure

Procedure MYSQL_UseMySQLDatabase()
  CompilerIf #PB_Compiler_Processor = #PB_Processor_x64
    mysql_lib_init("libmysql_64.dll")
  CompilerElse
    mysql_lib_init("libmysql.dll")
  CompilerEndIf
  __mysql_pb_any = 1
EndProcedure

Procedure MYSQL_CloseDatabase(Databaseno, Libclose=0)
Define dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd
  
  If dbhnd
    mysql_close(dbHnd)
    __MySQLLib_Data(Str(Databaseno))\dbhnd = 0
  EndIf

  If Libclose = #True
    mysql_lib_close()    
  EndIf
EndProcedure

Procedure MYSQL_OpenDatabase(Databaseno, Host.s, Benutzer.s, Passwort.s, Database.s, Port.l = 3306, Flags.l = #CLIENT_MULTI_STATEMENTS | #MySQL_CLIENT_COMPRESS) ; DefaultPort (no need to change)
Define res.l
  
  If Databaseno = #PB_Any
    While FindMapElement(__MySQLLib_Data(), Str(__mysql_pb_any))
      __mysql_pb_any + 1
    Wend
    Databaseno = __mysql_pb_any
    res.l = Databaseno
  Else
    If FindMapElement(__MySQLLib_Data(), Str(Databaseno))
      If __MySQLLib_Data()\dbhnd
        ProcedureReturn 0
      EndIf
    EndIf
    res.l = 1
  EndIf
  __MySQLLib_Data(Str(Databaseno))\dbhnd = mysql_init(0)
  If mysql_real_connect(__MySQLLib_Data(Str(Databaseno))\dbhnd, Host.s, Benutzer.s, Passwort.s, Database.s, Port.l,#NULL$, Flags.l) ;#Null$>Verbindungsart TCP, #Null>kein Client_Flag
    ProcedureReturn res.l
  Else
    MYSQL_CloseDatabase(Databaseno)
    ProcedureReturn 0
  EndIf
EndProcedure

Procedure MYSQL_DatabaseQuery(Databaseno, Abfrage.s)
Define res.l, sqlresult, dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd
Define *dat.st_MYSQL_Lib_Data = __MySQLLib_Data(Str(Databaseno)), i
  
  If ListSize(*dat\blogparams()) > 0
    For i = 0 To ListSize(*dat\blogparams()) - 1
      SelectElement(*dat\blogparams(), i)
      ReplaceString(Abfrage.s, "$" + Str(i), *dat\blogparams())
    Next
    ClearList(*dat\blogparams()) 
  EndIf
  
  res.l = mysql_real_query(dbHnd, Abfrage.s, Len(Abfrage.s))
  If res.l = 0
    
    __MySQLLib_Data(Str(Databaseno))\sqlresult = mysql_store_result.l(dbHnd)
    sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult
    If sqlresult
      __MySQLLib_Data(Str(Databaseno))\sqlresult_fields = mysql_fetch_fields(sqlresult)
      __MySQLLib_Data(Str(Databaseno))\rownumber = -1
      ProcedureReturn 1
    Else
      ProcedureReturn 0
    EndIf
  Else
    ProcedureReturn 0
  EndIf
EndProcedure

Procedure MYSQL_DatabaseUpdate(Databaseno, Abfrage.s)
Define res.l, dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd
Define *dat.st_MYSQL_Lib_Data = __MySQLLib_Data(Str(Databaseno)), i

  If ListSize(*dat\blogparams()) > 0
    For i = 0 To ListSize(*dat\blogparams()) - 1
      SelectElement(*dat\blogparams(), i)
      ReplaceString(Abfrage.s, "$" + Str(i), *dat\blogparams())
    Next
    ClearList(*dat\blogparams()) 
  EndIf  
  
  res.l = mysql_real_query(dbHnd, Abfrage.s, Len(Abfrage.s))
  If res.l = 0
    ProcedureReturn mysql_affected_rows(dbHnd)
  Else
    ProcedureReturn 0
  EndIf
EndProcedure

Procedure MYSQL_NextDatabaseRow(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult

  __MySQLLib_Data(Str(Databaseno))\rownumber + 1
  __MySQLLib_Data(Str(Databaseno))\sqlresult_row = mysql_fetch_row(sqlresult) 

  ProcedureReturn __MySQLLib_Data(Str(Databaseno))\sqlresult_row
EndProcedure

Procedure MYSQL_PreviousDatabaseRow(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult

  __MySQLLib_Data(Str(Databaseno))\rownumber - 1
  mysql_data_seek(sqlresult, __MySQLLib_Data(Str(Databaseno))\rownumber)
  __MySQLLib_Data(Str(Databaseno))\sqlresult_row = mysql_fetch_row(sqlresult)
  
  ProcedureReturn __MySQLLib_Data(Str(Databaseno))\sqlresult_row 
EndProcedure 

Procedure MYSQL_FirstDatabaseRow(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult
Define res.l

  mysql_data_seek(sqlresult, 0)
  __MySQLLib_Data(Str(Databaseno))\rownumber = 0
  __MySQLLib_Data(Str(Databaseno))\sqlresult_row = mysql_fetch_row(sqlresult) 

  ProcedureReturn __MySQLLib_Data(Str(Databaseno))\sqlresult_row
EndProcedure

Procedure MYSQL_DatabaseColumnType(Databaseno, Spalte.l)
Define *fields.st_arr_of_fields = __MySQLLib_Data(Str(Databaseno))\sqlresult_fields, numfields.l
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult

  numfields = mysql_num_fields(sqlresult)
  If Spalte.l < numfields
    Select *fields\field[Spalte.l]\type
    Case #MYSQL_TYPE_TINY, #MYSQL_TYPE_SHORT, #MYSQL_TYPE_LONG, #MYSQL_TYPE_NULL, #MYSQL_TYPE_TIMESTAMP, #MYSQL_TYPE_YEAR, #MYSQL_TYPE_BIT, #MYSQL_TYPE_ENUM
      ProcedureReturn #PB_Database_Long 
    Case #MYSQL_TYPE_DATE, #MYSQL_TYPE_TIME, #MYSQL_TYPE_DATETIME, #MYSQL_TYPE_NEWDATE, #MYSQL_TYPE_VARCHAR, #MYSQL_TYPE_SET, #MYSQL_TYPE_GEOMETRY
      ProcedureReturn #PB_Database_String
    Case #MYSQL_TYPE_FLOAT
      ProcedureReturn #PB_Database_Float
    Case #MYSQL_TYPE_DECIMAL, #MYSQL_TYPE_DOUBLE, #MYSQL_TYPE_NEWDECIMAL
      ProcedureReturn #PB_Database_Double 
    Case #MYSQL_TYPE_LONGLONG, #MYSQL_TYPE_INT24
      ProcedureReturn #PB_Database_Quad 
    Case #MYSQL_TYPE_TINY_BLOB, #MYSQL_TYPE_MEDIUM_BLOB, #MYSQL_TYPE_LONG_BLOB, #MYSQL_TYPE_BLOB, #MYSQL_TYPE_VAR_STRING, #MYSQL_TYPE_STRING
      ProcedureReturn #PB_Database_Blob
    Default
      ProcedureReturn #PB_Database_Long
    EndSelect
  EndIf
EndProcedure

Procedure.s MYSQL_DatabaseColumnName(Databaseno, Spalte.l)
Define *fields.st_arr_of_fields = __MySQLLib_Data(Str(Databaseno))\sqlresult_fields, numfields.l
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult

  numfields = mysql_num_fields(sqlresult)
  If Spalte.l < numfields
    ProcedureReturn *fields\field[Spalte.l]\name\s
  EndIf
EndProcedure

Procedure MYSQL_DatabaseColumns(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult
   ;   Debug mysql_field_count(dbHnd)
  ProcedureReturn mysql_num_fields(sqlresult)
EndProcedure

Procedure MYSQL_DatabaseRows(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult
  ProcedureReturn mysql_num_rows(sqlresult)
EndProcedure

Procedure.l MYSQL_GetDatabaseBlob(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn @*row\fields[Spalte.l]
  EndIf
EndProcedure

Procedure.d MYSQL_GetDatabaseDouble(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn ValD(*row\fields[Spalte.l])
  EndIf
EndProcedure

Procedure.f MYSQL_GetDatabaseFloat(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn ValF(*row\fields[Spalte.l])
  EndIf
EndProcedure

Procedure.l MYSQL_GetDatabaseLong(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn Val(*row\fields[Spalte.l])
  EndIf
EndProcedure

Procedure.q MYSQL_GetDatabaseQuad(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn Val(*row\fields[Spalte.l])
  EndIf
EndProcedure

Procedure.s MYSQL_GetDatabaseString(Databaseno, Spalte.l)
Define *row.st_MYSQL_ROW, sqlresultrow = __MySQLLib_Data(Str(Databaseno))\sqlresult_row
  *row = sqlresultrow
  If *row
    ProcedureReturn *row\fields[Spalte.l]
  EndIf
EndProcedure

Procedure.l MYSQL_DatabaseColumnSize(Databaseno, Spalte.l)
Define *sizes.st_Long_array, sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult
Define numfields

  numfields = mysql_num_fields(sqlresult)
  If Spalte.l < numfields
    *sizes = mysql_fetch_lengths(sqlresult)
    If *sizes
      ProcedureReturn *sizes\long[Spalte]
    EndIf
  EndIf
  
EndProcedure 

Procedure.l MYSQL_FinishDatabaseQuery(Databaseno)
Define sqlresult = __MySQLLib_Data(Str(Databaseno))\sqlresult  
  mysql_free_result(sqlresult)
  __MySQLLib_Data(Str(Databaseno))\sqlresult = 0
EndProcedure

Procedure MYSQL_IsDatabase(Databaseno)
Define dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd
  If dbHnd And __Lib_mysql_hdl.i
    ProcedureReturn 1
  EndIf
EndProcedure

Procedure MYSQL_DatabaseID(Databaseno)
  ProcedureReturn __MySQLLib_Data(Str(Databaseno))\dbhnd
EndProcedure

;Use $1, $2, $3.... in your query 
Procedure MYSQL_SetDatabaseBlob(Databaseno, Statementindex.l, *Puffer, Pufferlaenge.l)
Define dbhnd = __MySQLLib_Data(Str(Databaseno))\dbhnd

  While ListSize(__MySQLLib_Data(Str(Databaseno))\blogparams()) - 1 < Statementindex.l
    AddElement(__MySQLLib_Data(Str(Databaseno))\blogparams())
  Wend
  SelectElement(__MySQLLib_Data(Str(Databaseno))\blogparams(), Statementindex.l)
  __MySQLLib_Data(Str(Databaseno))\blogparams() = Space(Pufferlaenge.l*2 + 1)

  If mysql_real_escape_string(dbhnd, __MySQLLib_Data(Str(Databaseno))\blogparams(), *Puffer, Pufferlaenge.l)
    __MySQLLib_Data(Str(Databaseno))\blogparams() = "'" + __MySQLLib_Data(Str(Databaseno))\blogparams() + "'"
    Debug __MySQLLib_Data(Str(Databaseno))\blogparams()
    ProcedureReturn 1
  EndIf
EndProcedure

; Define  user.s, passwd.s, port.l, host.s, DB.s, SQL.s, Res.l, SQLResult, *row.st_MYSQL_ROW, *mem
; 
; user.s    = "user"
; passwd.s  = "password"
; host.s    = "localhost"
; DB.s      = "phpmyadmin" ;Schema / DB
; 
; 
; SQL.s="SELECT * FROM pma_history"
; 
; MySQL_UseMySQLDatabase()
; res = MYSQL_OpenDatabase(0, Host.s, user.s, passwd.s, DB.s)
; If res
;   res = MYSQL_DatabaseQuery(0,SQL.s)
;   If res
;     Debug "Query ok"
;         
;     Debug MYSQL_DatabaseColumnType(0, 1)
;     Debug MYSQL_DatabaseColumnName(0, 1)
;     
;     While MYSQL_NextDatabaseRow(0)
;       Debug MYSQL_GetDatabaseString(0, 0)
;       Debug MYSQL_DatabaseColumnSize(0, 0)
;     Wend
;         
;     MYSQL_FinishDatabaseQuery(0)
;   Else
;     Debug "Query fehlgeschlagen"
;     Debug MYSQL_DatabaseError(0)  
;   EndIf 
; Else
;   Debug MYSQL_DatabaseError(0)
; EndIf
;
have fun.

Re: LibMySQL Wrapper for MySQL Databases

Posted: Wed Dec 21, 2011 6:31 pm
by bobobo
What about MYSQL_DatabaseColumName and MYSQL_DatabaseColumType ?

Re: LibMySQL Wrapper for MySQL Databases

Posted: Tue Jan 03, 2012 10:20 pm
by VoSs2o0o
I have added this command in the updated code above.
Also all other missing function are now available :-)

Re: LibMySQL Wrapper for MySQL Databases

Posted: Mon Jan 16, 2012 12:33 pm
by bobobo
Have You tried to get more than one column?

I got errors in reading from tables with more than 2 cols .

(a check with "normal" pb-Syntax (odbc) results ok (all 128 Cols are readable))

Re: LibMySQL Wrapper for MySQL Databases

Posted: Mon Jan 16, 2012 5:36 pm
by VoSs2o0o
Yes, i use this Library in a lot of Programs without problems.
Do you have an small code example and a mysql-Structure for me.
(Your ODBC-Code will do it)

I will check it and fix it, if there an error.

Re: LibMySQL Wrapper for MySQL Databases

Posted: Wed Jan 25, 2012 2:47 am
by VoSs2o0o
Ahhh, you mean MYSQL_DatabaseColumnType / MYSQL_DatabaseColumnName.
I had an old libmysql on my machine, so i have not seen ths problem.

code fixed and updated.

Re: LibMySQL Wrapper for MySQL Databases

Posted: Fri Jan 27, 2012 12:36 pm
by bobobo
yepp .it's ok now ..

though a little slow compared with a connection via odbc
(it may be depend on other actual traffic on this database
and on the code used (filling strings with large values is a
showstopper:) )

there is indeed no relevant difference in speed


(connect to a big and far away db via Network)

if see this constant #MySQL_CLIENT_COMPRESS defined

but how to use it ? Maybe this will give a speed-push.

Re: LibMySQL Wrapper for MySQL Databases

Posted: Tue Feb 07, 2012 11:53 am
by VoSs2o0o
Use #MySQL_CLIENT_COMPRESS in "MySQL_OpenDatabase" as last param in "mysql_real_connect".
I have not tested it yet, but will do it in the next time.

I have also updated the code (small Bugfix)

Re: LibMySQL Wrapper for MySQL Databases

Posted: Tue Feb 07, 2012 1:19 pm
by bobobo
i tested with that flag

it's about 2 times faster then :!: :mrgreen:

Re: LibMySQL Wrapper for MySQL Databases

Posted: Mon Mar 05, 2012 11:08 pm
by Olby
Just tried this wrapper, works perfectly. I had to add PeekS with #PB_Ascii as my application is compiled in unicode, I guess the dll is ascii only?

Re: LibMySQL Wrapper for MySQL Databases

Posted: Sun May 06, 2012 12:39 pm
by ClueLess
I got A query fail When I do an Insert. This is the example code I'm using:

Code: Select all


XIncludeFile "MySQL Wraper.pbi"

user.s    = "root"
passwd.s  = "MyStrongPass"
host.s    = "localhost"
DB.s      = "spare_parts"

sitecode.s = "LDA"
SiteName.s = "Luanda"

SQL.s = "INSERT INTO sites (site_code, site_name) VALUES (" + Chr(39) + SiteCode + Chr(39) + ", " + Chr(39) + SiteName + Chr(39) + ")"

MySQL_UseMySQLDatabase()
res = MYSQL_OpenDatabase(0, Host.s, user.s, passwd.s, DB.s)
If res
  res = MYSQL_DatabaseQuery(0,SQL.s)
  If res
    Debug "Query ok"
        
    Debug MYSQL_DatabaseColumnName(0, 1)
    Debug MYSQL_DatabaseColumnName(0, 0)
    
    While MYSQL_NextDatabaseRow(0)
      Debug MYSQL_GetDatabaseString(0, 1)
      Debug MYSQL_GetDatabaseString(0, 0)
    Wend
        
    MYSQL_FinishDatabaseQuery(0)
  Else
    Debug "Query failed"
    Debug MYSQL_DatabaseError(0)  
  EndIf 
Else
  Debug MYSQL_DatabaseError(0)
EndIf
Any Ideas?

Re: LibMySQL Wrapper for MySQL Databases

Posted: Sun May 06, 2012 1:25 pm
by Fangbeast
Try simplifying the query by using literal single quotes like this first:

SQL.s = "INSERT INTO sites (site_code, site_name) VALUES ('" + SiteCode + "', '" + SiteName + "')"

Then also debug the query itself on fail to see if SiteCode or SiteName had single quotes in them that need ESCaping (Single quotes in strings need to be double quoted (ESCaped)) to be saved.

So if any of your variables had single quotes in them E.g

"Robert's haven"

It needs to be "Robert''s haven" to not fail.

Also, in the query you have

site_code
site_name

but in the variables above you have

sitecode.s = "LDA"
SiteName.s = "Luanda"

(Sorry, I wasn't quite awake when I posted that)

Re: LibMySQL Wrapper for MySQL Databases

Posted: Sun May 06, 2012 2:57 pm
by Kiffi
ClueLess wrote:I got A query fail When I do an Insert.
Use DatabaseUpdate() to execute an Insert.

Greetings ... Kiffi

Re: LibMySQL Wrapper for MySQL Databases

Posted: Tue May 08, 2012 3:15 pm
by ClueLess
Smething I don't see is Happenig with this Wrapper

This code works fine:

Code: Select all

XIncludeFile "MySQL Wraper.pbi"
MySQL_UseMySQLDatabase()

Global user.s    = "root"
Global passwd.s  = "MyStrongPass"
Global host.s    = "localhost"
Global DB.s      = "spare_parts"

Global SiteCode.s = "Lda"
Global SiteName.s = "Luanda"
Global MachineType.s = "M7"
Global MachineSN.s ="12345678"

Procedure SaveTables()
		res = MYSQL_OpenDatabase(0, host.s, user.s, passwd.s, DB.s)
		
		If res
			Debug "DB openned"
		Else 
			Debug "DB failed: " + MYSQL_DatabaseError(0)
		EndIf 
		If res
;			Items = CountGadgetItems(#TBL_List_SitesWarehouses)
			
			SQL.s = "DELETE from sites"
			res1 = MYSQL_DatabaseUpdate(0, SQL.s)
			
;			For i = 0 To Items - 1
; 				SetGadgetState(#TBL_List_SitesWarehouses, i)
; 				Site.s = GetGadgetText(#TBL_List_SitesWarehouses)
; 				LenghtWrd = Len(Site)
; 				Lenght1st = FindString(Site, " - ", 1)
; 				SiteCode.s = Left(Site, Lenght1st - 2)
; 				SiteName.s = Right(Site, LenghtWrd - (Lenght1st + 2))
				SQL.s = "INSERT INTO sites (site_code, site_name) VALUES (" + Chr(39) + SiteCode + Chr(39) + ", " + Chr(39) + SiteName + Chr(39) + ")"
				res2 = MYSQL_DatabaseUpdate(0, SQL.s)
;			Next
			
;			Items = CountGadgetItems(#TBL_List_Machines)
			
			SQL.s = "DELETE from machines"
			res3 = MYSQL_DatabaseUpdate(0, SQL.s)
			
;			For i = 0 To Items - 1
; 				SetGadgetState(#TBL_List_Machines, i)
; 				Machine.s = GetGadgetText(#TBL_List_Machines)
; 				LenghtWrd = Len(Machine)
; 				Lenght1st = FindString(Machine, " - ", 1)
; 				MachineType.s = Left(Machine, Lenght1st - 2)
; 				MachineSN.s = Right(Machine, LenghtWrd - (Lenght1st + 2))
				SQL.s = "INSERT INTO machines (machine_type, machine_sn, machine_site) VALUES (" + Chr(39) + MachineType + Chr(39) + ", " + Chr(39) + MachineSN + Chr(39) + ", " + Chr(39) + SiteCode + Chr(39) + ")"
				res4 = MYSQL_DatabaseUpdate(0, SQL.s)
;			Next
			If res1 And res2 And res3 And res4
				MessageRequester("INFO", "Database updated sussefully")
			Else
;				MessageRequester("ERROR", "There was an Error updating the database: " + MYSQL_DatabaseError(0))
			EndIf
		Else		
;			MessageRequester("ERROR", "There was an Error oppening the database: " + MYSQL_DatabaseError(0))
		EndIf
			MYSQL_FinishDatabaseQuery(0)
MYSQL_CloseDatabase(0)
EndProcedure

SaveTables()
With this one Can't open the database:

Code: Select all

XIncludeFile "MySQL Wraper.pbi"
MySQL_UseMySQLDatabase()

Global user.s    = "root"
Global passwd.s  = "MyStrongPass"
Global host.s    = "localhost"
Global DB.s      = "spare_parts"

Global SiteCode.s = "Lda"
Global SiteName.s = "Luanda"
Global MachineType.s = "M7"
Global MachineSN.s ="12345678"

Procedure SaveTables()
		res = MYSQL_OpenDatabase(0, host.s, user.s, passwd.s, DB.s)
		
		If res
			Debug "DB openned"
		Else 
			Debug "DB failed: " + MYSQL_DatabaseError(0)
		EndIf 
		If res
;			Items = CountGadgetItems(#TBL_List_SitesWarehouses)
			
			SQL.s = "DELETE from sites"
			res1 = MYSQL_DatabaseUpdate(0, SQL.s)
			
;			For i = 0 To Items - 1
; 				SetGadgetState(#TBL_List_SitesWarehouses, i)
; 				Site.s = GetGadgetText(#TBL_List_SitesWarehouses)
; 				LenghtWrd = Len(Site)
; 				Lenght1st = FindString(Site, " - ", 1)
; 				SiteCode.s = Left(Site, Lenght1st - 2)
; 				SiteName.s = Right(Site, LenghtWrd - (Lenght1st + 2))
				SQL.s = "INSERT INTO sites (site_code, site_name) VALUES (" + Chr(39) + SiteCode + Chr(39) + ", " + Chr(39) + SiteName + Chr(39) + ")"
				res2 = MYSQL_DatabaseUpdate(0, SQL.s)
;			Next
			
;			Items = CountGadgetItems(#TBL_List_Machines)
			
			SQL.s = "DELETE from machines"
			res3 = MYSQL_DatabaseUpdate(0, SQL.s)
			
;			For i = 0 To Items - 1
; 				SetGadgetState(#TBL_List_Machines, i)
; 				Machine.s = GetGadgetText(#TBL_List_Machines)
; 				LenghtWrd = Len(Machine)
; 				Lenght1st = FindString(Machine, " - ", 1)
; 				MachineType.s = Left(Machine, Lenght1st - 2)
; 				MachineSN.s = Right(Machine, LenghtWrd - (Lenght1st + 2))
				SQL.s = "INSERT INTO machines (machine_type, machine_sn, machine_site) VALUES (" + Chr(39) + MachineType + Chr(39) + ", " + Chr(39) + MachineSN + Chr(39) + ", " + Chr(39) + SiteCode + Chr(39) + ")"
				res4 = MYSQL_DatabaseUpdate(0, SQL.s)
;			Next
			If res1 And res2 And res3 And res4
				MessageRequester("INFO", "Database updated sussefully")
			Else
;				MessageRequester("ERROR", "There was an Error updating the database: " + MYSQL_DatabaseError(0))
			EndIf
		Else		
;			MessageRequester("ERROR", "There was an Error oppening the database: " + MYSQL_DatabaseError(0))
		EndIf
			MYSQL_FinishDatabaseQuery(0)
MYSQL_CloseDatabase(0)
EndProcedure

SaveTables()
ou will have to dig up to "TBL_CMD_Save..

Does anyone can explain me what is diferent?

Database user, password and DB is in the main file, but is the same as the working example...

Re: LibMySQL Wrapper for MySQL Databases

Posted: Tue Feb 05, 2013 6:19 pm
by VoSs2o0o
Lib updated....
All times are UTC+01:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited