PureBasic Forum

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

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

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

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