It is currently Tue Oct 23, 2018 8:42 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: mysql.pbi [cross platfom]
PostPosted: Mon Sep 02, 2013 9:33 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
I was in need of a mysql-database-connection.
There are quite some mysql-snippets on the forum, but as usual i need something as easy as possible.
So i decided to do some kind of subclassing of the normal PB Database-commands.
That's why i didn't go for modules.

With this include you can use the normal PB-Database commands.
Just use a
Code:
UseMySQLDataBase()

at the beginning (it uses a dll, so you should check the return value of this procedure).
And start your action with something like this:
Code:
OpenDatabase(0, "host=localhost dbname=mydbname", "myusername", "mypassword", #PB_Database_MySQL)


I didn't subclass all of the available PB-commands, only those i were in need of (you know: i am an egoist!).
Fell free to add it on your own.

No example this time, because i already said:
It uses the normal PB-commands.

Here we go:
Code:
;=============================================================================
; Include:         mysql.pbi
;
; Author:          HeX0R / infratec / mk-soft
; Date:            Aug 15, 2016
; Version:         1.0.6
; Target Compiler: PureBasic 5.11+
; Target OS:       Windows / Linux / MacOS
;
;
; default PB-Database procedures you can use:
;
; AffectedDatabaseRows
; CheckDatabaseNull
; CloseDatabase
; DatabaseColumnIndex
; DatabaseColumnName
; DatabaseColumns
; DatabaseColumnSize
; DatabaseColumnType
; DatabaseID
; DatabaseQuery
; DatabaseUpdate
; FinishDatabaseQuery
; GetDatabaseDouble
; GetDatabaseFloat
; GetDatabaseLong
; GetDatabaseQuad
; GetDatabaseString
; GetDatabaseBlob
; IsDatabase
; NextDatabaseRow
; OpenDatabase         <- See help of postgresql, this uses the same structure
;
; When you are interested in an error message,
; use MySQL_GetError(#DataBase) instead of DatabaseError()
;
;
; Remarks
;
; Regarding threadsafety:
; I am using only one unsecured linked list which stores the Databases.
; This means you have to take care on your own, when sending querys from
; different threads.
; Adding the compilers threadsafe-option won't help you much here.
;
; Blobs:
; I wasn't in need of blobs, thats why it isn't [yet?!] implemented.
;
; Unicode:
; I would recommend to compile your apps in unicode mode.
; There are quite some characters in UTF-8, which Ascii can not handle.
;
; ----------------------------------------------------------------------------
; "THE BEER-WARE LICENSE":
; <hex0r@coderbu.de> wrote this file. as long as you retain this notice you
; can do whatever you want with this stuff. If we meet some day, and you think
; this stuff is worth it, you can buy me a beer in return
; (see address on http://hex0rs.coderbu.de).
; Or just go out and drink a few on your own/with your friends ;)
;=============================================================================


#PB_Database_MySQL             = $10   ;<- used for OpenDatabase as plugin.

#MySQL_CLIENT_COMPRESS         = 32    ;<- client_flag for compress
#MySQL_CLIENT_MULTI_STATEMENTS = 65536

;-Init some Structures
Structure _MYSQL_HELPER_ ;used to iterate through the fields
   StructureUnion
      row.i[0]
      CompilerIf #PB_Compiler_OS = #PB_OS_Windows
         len.l[0]
      CompilerElse
         len.i[0]
      CompilerEndIf
   EndStructureUnion
EndStructure

Structure _MYSQL_DBs_
   DataBaseNum.i            ;<- the PB-Identifier (#DataBase)
   DataBaseID.i             ;<- the API-Handle
   *mysqlResult             ;<- stores the result of a query
   *mysqlRow._MYSQL_HELPER_ ;<- stores the fields of one row
   *mysqlLen._MYSQL_HELPER_ ;<- stores the length of each field
   FieldCount.i             ;<- stores the fields which have been returned after a query
EndStructure


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


;-Init some Prototypes
; There are more defined as needed.
; This is just for future reference
CompilerSelect #PB_Compiler_OS
   CompilerCase #PB_OS_Windows
      Prototype.i MySQL_Init(dbHwnd)
      Prototype.i MySQL_ERRNO(dbHwnd)
      Prototype.i MySQL_ERROR(dbHwnd)
      Prototype.i MySQL_Real_Connect(dbHwnd, host.p-utf8, user.p-utf8, password.p-utf8, DataBase.p-utf8, Port, *unix_socket, client_flag)
      Prototype.i MySQL_Real_Query(dbHwnd, Query.p-utf8, Length)
      Prototype.i MySQL_Real_Escape_String(dbHwnd, *to, from.p-utf8, Length)
      Prototype.i MySQL_Set_Character_Set(dbHwnd, csname.p-utf8)
      Prototype.i MySQL_Store_Result(dbHwnd)
      Prototype.i MySQL_Field_Count(dbHwnd)
      Prototype.i MySQL_Use_Result(dbHwnd)
      Prototype.i MySQL_Affected_Rows(dbHwnd)
      Prototype.i MySQL_Fetch_Row(*result)
      Prototype.i MySQL_Fetch_Lengths(*result)
      Prototype.i MySQL_Free_Result(*result)
      Prototype.i MySQL_Fetch_Fields(*result)
      Prototype.i MySQL_Num_Fields(*result)
      Prototype.i MySQL_Close(dbHwnd)
      Prototype.i MySQL_Fetch_Field_Direct(*result, fieldnr.i)
      Prototype.i MySQL_Data_Seek(*result, offset.q)
   CompilerCase #PB_OS_Linux
      PrototypeC.i MySQL_Init(dbHwnd)
      PrototypeC.i MySQL_ERRNO(dbHwnd)
      PrototypeC.i MySQL_ERROR(dbHwnd)
      PrototypeC.i MySQL_Real_Connect(dbHwnd, host.p-utf8, user.p-utf8, password.p-utf8, DataBase.p-utf8, Port, *unix_socket, client_flag)
      PrototypeC.i MySQL_Real_Query(dbHwnd, Query.p-utf8, Length)
      PrototypeC.i MySQL_Real_Escape_String(dbHwnd, *to, from.p-utf8, Length)
      PrototypeC.i MySQL_Set_Character_Set(dbHwnd, csname.p-utf8)
      PrototypeC.i MySQL_Store_Result(dbHwnd)
      PrototypeC.i MySQL_Field_Count(dbHwnd)
      PrototypeC.i MySQL_Use_Result(dbHwnd)
      PrototypeC.i MySQL_Affected_Rows(dbHwnd)
      PrototypeC.i MySQL_Fetch_Row(*result)
      PrototypeC.i MySQL_Fetch_Lengths(*result)
      PrototypeC.i MySQL_Free_Result(*result)
      PrototypeC.i MySQL_Fetch_Fields(*result)
      PrototypeC.i MySQL_Num_Fields(*result)
      PrototypeC.i MySQL_Close(dbHwnd)
      PrototypeC.i MySQL_Fetch_Field_Direct(*result, fieldnr.i)
      PrototypeC.i MySQL_Data_Seek(*result, offset.q)
   CompilerCase #PB_OS_MacOS
      PrototypeC.i MySQL_Init(dbHwnd)
      PrototypeC.i MySQL_ERRNO(dbHwnd)
      PrototypeC.i MySQL_ERROR(dbHwnd)
      PrototypeC.i MySQL_Real_Connect(dbHwnd, host.p-utf8, user.p-utf8, password.p-utf8, DataBase.p-utf8, Port, *unix_socket, client_flag)
      PrototypeC.i MySQL_Real_Query(dbHwnd, Query.p-utf8, Length)
      PrototypeC.i MySQL_Real_Escape_String(dbHwnd, *to, from.p-utf8, Length)
      PrototypeC.i MySQL_Set_Character_Set(dbHwnd, csname.p-utf8)
      PrototypeC.i MySQL_Store_Result(dbHwnd)
      PrototypeC.i MySQL_Field_Count(dbHwnd)
      PrototypeC.i MySQL_Use_Result(dbHwnd)
      PrototypeC.i MySQL_Affected_Rows(dbHwnd)
      PrototypeC.i MySQL_Fetch_Row(*result)
      PrototypeC.i MySQL_Fetch_Lengths(*result)
      PrototypeC.i MySQL_Free_Result(*result)
      PrototypeC.i MySQL_Fetch_Fields(*result)
      PrototypeC.i MySQL_Num_Fields(*result)
      PrototypeC.i MySQL_Close(dbHwnd)
      PrototypeC.i MySQL_Fetch_Field_Direct(*result, fieldnr.i)
      PrototypeC.i MySQL_Data_Seek(*result, offset.q)

CompilerEndSelect

;-Init some Globals
Global MySQL_Init              .MySQL_Init
Global MySQL_ERRNO             .MySQL_ERRNO
Global MySQL_ERROR             .MySQL_ERROR
Global MySQL_Real_Connect      .MySQL_Real_Connect
Global MySQL_Real_Query        .MySQL_Real_Query
Global MySQL_Real_Escape_String.MySQL_Real_Escape_String
Global MySQL_Store_Result      .MySQL_Store_Result
Global MySQL_Field_Count       .MySQL_Field_Count
Global MySQL_Use_Result        .MySQL_Use_Result
Global MySQL_Fetch_Row         .MySQL_Fetch_Row
Global MySQL_Fetch_Lengths     .MySQL_Fetch_Lengths
Global MySQL_Free_Result       .MySQL_Free_Result
Global MySQL_Affected_Rows     .MySQL_Affected_Rows
Global MySQL_Close             .MySQL_Close
Global MySQL_Num_Fields        .MySQL_Num_Fields
Global MySQL_Set_Character_Set .MySQL_Set_Character_Set
Global MySQL_Fetch_Fields      .MySQL_Fetch_Fields
Global MySQL_Fetch_Field_Direct.MySQL_Fetch_Field_Direct
Global MySQL_Data_Seek         .MySQL_Data_Seek
Global MySQL_Lib
Global NewList MySQL_DBs._MYSQL_DBs_() ;<- will store the Database IDs and result values.
;                                            a map would be more efficient i guess, but who really opens more then 2 or 3 databases?

Global MySQL_LastErrorFlag.i


; You need to call UseMySQLDataBase() first!
; Instead of UseSQLiteDatabase() and UsePostgreSQLDatabase()
; you should check the return value!
Procedure UseMySQLDataBase(Path_To_MySQL_Lib.s = "")

   CompilerSelect #PB_Compiler_OS
      CompilerCase #PB_OS_Windows
         If Path_To_MySQL_Lib = ""
            Path_To_MySQL_Lib = "libmysql.dll"
         EndIf
         MySQL_Lib = OpenLibrary(#PB_Any, Path_To_MySQL_Lib)
      CompilerCase #PB_OS_Linux
         If Path_To_MySQL_Lib = ""
            Path_To_MySQL_Lib = "libmysqlclient.so.18"
         EndIf
         MySQL_Lib = OpenLibrary(#PB_Any, Path_To_MySQL_Lib)
         If MySQL_Lib = 0
            MySQL_Lib = OpenLibrary(#PB_Any, "libmysqlclient.so.16")
         EndIf
      CompilerCase #PB_OS_MacOS
         If Path_To_MySQL_Lib = ""
            CompilerIf #PB_Compiler_Processor = #PB_Processor_x64
               Path_To_MySQL_Lib = "/usr/local/mysql-connector-c-6.1.6-osx10.8-x86_64/lib/libmysqlclient.18.dylib"
            CompilerElse
               Path_To_MySQL_Lib = "/usr/local/mysql-connector-c-6.1.6-osx10.8-x86/lib/libmysqlclient.18.dylib"
            CompilerEndIf
         EndIf
         MySQL_Lib = OpenLibrary(#PB_Any, Path_To_MySQL_Lib)
         If MySQL_Lib = 0
            MySQL_Lib = OpenLibrary(#PB_Any, "libmysqlclient.dylib")
         EndIf
   CompilerEndSelect

   If MySQL_Lib
      MySQL_Init               = GetFunction(MySQL_Lib, "mysql_init")
      MySQL_ERRNO              = GetFunction(MySQL_Lib, "mysql_errno")
      MySQL_ERROR              = GetFunction(MySQL_Lib, "mysql_error")
      MySQL_Real_Connect       = GetFunction(MySQL_Lib, "mysql_real_connect")
      MySQL_Real_Query         = GetFunction(MySQL_Lib, "mysql_real_query")
      MySQL_Real_Escape_String = GetFunction(MySQL_Lib, "mysql_real_escape_string")
      MySQL_Store_Result       = GetFunction(MySQL_Lib, "mysql_store_result")
      MySQL_Field_Count        = GetFunction(MySQL_Lib, "mysql_field_count")
      MySQL_Use_Result         = GetFunction(MySQL_Lib, "mysql_use_result")
      MySQL_Fetch_Row          = GetFunction(MySQL_Lib, "mysql_fetch_row")
      MySQL_Fetch_Lengths      = GetFunction(MySQL_Lib, "mysql_fetch_lengths")
      MySQL_Free_Result        = GetFunction(MySQL_Lib, "mysql_free_result")
      MySQL_Num_Fields         = GetFunction(MySQL_Lib, "mysql_num_fields")
      MySQL_Affected_Rows      = GetFunction(MySQL_Lib, "mysql_affected_rows")
      MySQL_Close              = GetFunction(MySQL_Lib, "mysql_close")
      MySQL_Set_Character_Set  = GetFunction(MySQL_Lib, "mysql_set_character_set")
      MySQL_Fetch_Field_Direct = GetFunction(MySQL_Lib, "mysql_fetch_field_direct")
      MySQL_Data_Seek          = GetFunction(MySQL_Lib, "mysql_data_seek")
   EndIf

   ProcedureReturn MySQL_Lib
EndProcedure

; Internal function to check, if this database is a mysql database
Procedure MySQL_FindDataBase(DataBase)
   Protected Found = #False

   ForEach MySQL_DBs()
      If MySQL_DBs()\DataBaseNum = DataBase
         Found = #True
         Break
      EndIf
   Next

   ProcedureReturn Found
EndProcedure

; Open database
; uses same structure as the #PB_Database_PostgreSQL-Plugin (but no hostaddr is used).
Procedure MySQL_OpenDatabase(DataBase, Name$, User$, Password$, Plugin)
   Protected i, a$, ParameterName.s, ParameterValue.s, host.s, hostaddr.i, port.i, dbname.s, handle, flags.i

   If Plugin <> #PB_Database_MySQL
      ;o.k. nothing for us, so let PB handle it.
      ProcedureReturn OpenDatabase(DataBase, Name$, User$, Password$, Plugin)
   EndIf
   If MySQL_Init = 0
      ;user forgot to call UseMySQLDataBase() (or the library isn't available)
      ProcedureReturn 0
   EndIf
   If DataBase <> #PB_Any
      ;we check, if there is already a database with this ID open
      If MySQL_FindDataBase(DataBase)
         ;yes, so we will close it.
         ;first check, if there is a query open.
         If MySQL_DBs()\mysqlResult
            MySQL_Free_Result(MySQL_DBs()\mysqlResult)
            MySQL_DBs()\FieldCount  = 0
            MySQL_DBs()\mysqlLen    = 0
            MySQL_DBs()\mysqlRow    = 0
            MySQL_DBs()\mysqlResult = 0
         EndIf
         MySQL_Close(MySQL_DBs()\DataBaseID)
         ;now delete it
         DeleteElement(MySQL_DBs())
      EndIf
   EndIf
   ;Check the parameters
   For i = 0 To CountString(Name$, " ")
      a$ = Trim(StringField(Name$, i + 1, " "))
      If a$
         ParameterName  = LCase(Trim(StringField(a$, 1, "=")))
         ParameterValue = Trim(StringField(a$, 2, "="))
         Select ParameterName
            Case "host"
               Host = ParameterValue
            Case "hostaddr"
               hostaddr = Val(ParameterValue)
            Case "port"
               port = Val(ParameterValue)
            Case "dbname"
               dbname = ParameterValue
            Case "flags"
               flags = Val(ParameterValue)
         EndSelect
      EndIf
   Next i
   If dbname = ""
      dbname = User$
   EndIf
   If host = ""
      host = "localhost"
   EndIf
   handle = MySQL_Init(#Null)
   If handle
      If MySQL_Real_Connect(handle, host, User$, Password$, dbname, port, #Null, flags) = 0
         ;something went wrong...
         handle = #Null
      Else
         ;yessss... now add this client, to be sure we will mark it as a mysql client
         AddElement(MySQL_DBs())
         MySQL_DBs()\DataBaseID = handle
         If DataBase = #PB_Any
            MySQL_DBs()\DataBaseNum = @MySQL_DBs()
            handle                  = @MySQL_DBs()
         Else
            MySQL_DBs()\DataBaseNum = DataBase
         EndIf
         ;now set the client charset to utf8
         MySQL_Set_Character_Set(MySQL_DBs()\DataBaseID, "utf8")
      EndIf
   EndIf

   ProcedureReturn handle
EndProcedure

Procedure MySQL_CloseDatabase(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn CloseDatabase(DataBase)
   EndIf
   ;check if there is a query open.
   If MySQL_DBs()\mysqlResult
      MySQL_Free_Result(MySQL_DBs()\mysqlResult)
   EndIf
   MySQL_Close(MySQL_DBs()\DataBaseID)
   DeleteElement(MySQL_DBs())
EndProcedure

Procedure MySQL_DatabaseQuery(DataBase, Query.s, Flags = 0)
   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn DatabaseQuery(DataBase, Query, Flags)
   EndIf
   MySQL_LastErrorFlag = #True
   With MySQL_DBs()
      If \mysqlResult
         ;hmm user forgot to finish his databasequery. o.k. we will do it for him.
         MySQL_Free_Result(\mysqlResult)
         \FieldCount  = 0
         \mysqlLen    = 0
         \mysqlRow    = 0
         \mysqlResult = 0
      EndIf
      If MySQL_Real_Query(\DataBaseID, Query, StringByteLength(Query, #PB_UTF8)) = 0
         ;yes, strange but true... in this case a result of 0 means success.
         \mysqlResult = MySQL_Use_Result(\DataBaseID)
         ; for FirstDatabaseRow() we need store not use
         ;\mysqlResult = MySQL_Store_Result(\DataBaseID)
      EndIf
   EndWith

   ProcedureReturn MySQL_DBs()\mysqlResult
EndProcedure

Procedure MySQL_NextDatabaseRow(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn NextDatabaseRow(DataBase)
   EndIf
   MySQL_LastErrorFlag = #True
   With MySQL_DBs()
      If \mysqlResult
         \mysqlRow = MySQL_Fetch_Row(\mysqlResult)
         If \mysqlRow
            \mysqlLen   = MySQL_Fetch_Lengths(\mysqlResult)
            \FieldCount = MySQL_Num_Fields(\mysqlResult)
         EndIf
      EndIf
   EndWith

   ProcedureReturn MySQL_DBs()\mysqlRow
EndProcedure

Procedure MySQL_DatabaseUpdate(DataBase, Query.s)
   Protected Result

   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn DatabaseUpdate(DataBase, Query)
   EndIf
   MySQL_LastErrorFlag = #True
   If MySQL_Real_Query(MySQL_DBs()\DataBaseID, Query, StringByteLength(Query, #PB_UTF8)) = 0
      ;yes, strange but true... in this case a result of 0 means success.
      Result = #True
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_FinishDatabaseQuery(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn FinishDatabaseQuery(DataBase)
   EndIf
   MySQL_LastErrorFlag = #True
   With MySQL_DBs()
      If \mysqlResult
         MySQL_Free_Result(\mysqlResult)
         \FieldCount  = 0
         \mysqlLen    = 0
         \mysqlRow    = 0
         \mysqlResult = 0
      EndIf
   EndWith
EndProcedure


Procedure.i MySQL_GetDatabaseBlob(DataBase, Column, *Buffer, BufferLength)
   Protected Result.i

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseBlob(DataBase, Column, *Buffer, BufferLength)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         If MySQL_DBs()\mysqlLen\len[Column] <= BufferLength
            CopyMemory(MySQL_DBs()\mysqlRow\row[Column], *Buffer, MySQL_DBs()\mysqlLen\len[Column])
            Result = MySQL_DBs()\mysqlLen\len[Column]
         EndIf
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure


Procedure.s MySQL_GetDatabaseString(DataBase, Column)
   Protected Result.s

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseString(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         Result = PeekS(MySQL_DBs()\mysqlRow\row[Column], MySQL_DBs()\mysqlLen\len[Column], #PB_UTF8)
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure.d MySQL_GetDatabaseDouble(DataBase, Column)
   Protected Result.d

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseDouble(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         Result = ValD(PeekS(MySQL_DBs()\mysqlRow\row[Column], MySQL_DBs()\mysqlLen\len[Column], #PB_UTF8))
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure.f MySQL_GetDatabaseFloat(DataBase, Column)
   Protected Result.f

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseFloat(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         Result = ValF(PeekS(MySQL_DBs()\mysqlRow\row[Column], MySQL_DBs()\mysqlLen\len[Column], #PB_UTF8))
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_GetDatabaseLong(DataBase, Column)
   Protected Result

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseLong(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         Result = Val(PeekS(MySQL_DBs()\mysqlRow\row[Column], MySQL_DBs()\mysqlLen\len[Column], #PB_UTF8))
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure.q MySQL_GetDatabaseQuad(DataBase, Column)
   Protected Result.q

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn GetDatabaseQuad(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If Column < MySQL_DBs()\FieldCount And MySQL_DBs()\mysqlLen\len[Column] > 0
         Result = Val(PeekS(MySQL_DBs()\mysqlRow\row[Column], MySQL_DBs()\mysqlLen\len[Column], #PB_UTF8))
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_AffectedDatabaseRows(DataBase)

   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn AffectedDatabaseRows(DataBase)
   EndIf
   MySQL_LastErrorFlag = #True

   ProcedureReturn MySQL_Affected_Rows(MySQL_DBs()\DataBaseID)
EndProcedure

Procedure MySQL_CheckDatabaseNull(DataBase, Column)
   Protected Result

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn CheckDatabaseNull(DataBase, Column)
   EndIf
   If MySQL_DBs()\mysqlResult
      If MySQL_DBs()\mysqlLen = 0 Or MySQL_DBs()\mysqlLen\len[Column] = 0
         Result = #True
      EndIf
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_DatabaseID(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseID(DataBase)
   EndIf

   ProcedureReturn MySQL_DBs()\DataBaseID
EndProcedure

Procedure MySQL_IsDatabase(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn IsDatabase(DataBase)
   EndIf

   ProcedureReturn #True
EndProcedure

; use this procedure to add escape characters, when putting strings in querys
Procedure.s MySQL_EscapeString(DataBase, String.s)
   Protected Text$, *Buffer, Length

   If MySQL_FindDataBase(DataBase)
      *Buffer = AllocateMemory(StringByteLength(String, #PB_UTF8) * 2 + 1)
      If *Buffer
         Length = MySQL_Real_Escape_String(MySQL_DBs()\DataBaseID, *Buffer, String, StringByteLength(String, #PB_UTF8))
         Text$  = PeekS(*Buffer, Length, #PB_UTF8)
         FreeMemory(*Buffer)
      EndIf
   EndIf

   ProcedureReturn Text$
EndProcedure

Procedure.s MySQL_GetError()
   Protected Errormsg.s, i, *Error

   If MySQL_LastErrorFlag = #False; Or MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseError()
   EndIf

   If MySQL_ERRNO(MySQL_DBs()\DataBaseID) > 0
      *Error   = MySQL_ERROR(MySQL_DBs()\DataBaseID)
      Errormsg = PeekS(*Error, -1, #PB_UTF8)
   EndIf

   ProcedureReturn Errormsg
EndProcedure

Procedure MySQL_DatabaseColumns(DataBase)
   Protected Result

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseColumns(DataBase)
   EndIf
   Result = MySQL_Field_Count(MySQL_DBs()\DataBaseID)

   ProcedureReturn Result
EndProcedure

Procedure.s MySQL_DatabaseColumnName(DataBase, Column)
   Protected *Result._MYSQL_FIELD_

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseColumnName(DataBase, Column)
   EndIf
   *Result = MySQL_Fetch_Field_Direct(MySQL_DBs()\mysqlResult, Column)
   If *Result
      ProcedureReturn PeekS(*Result\name, -1, #PB_UTF8)
   EndIf

   ProcedureReturn ""
EndProcedure

Procedure MySQL_DatabaseColumnType(DataBase, Column)
   Protected *Result._MYSQL_FIELD_, Result

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseColumnType(DataBase, Column)
   EndIf
   *Result = MySQL_Fetch_Field_Direct(MySQL_DBs()\mysqlResult, Column)
   If *Result
      Select *Result\type
         Case 1, 2, 3
            Result = #PB_Database_Long
         Case 4
            Result = #PB_Database_Float
         Case 12
            Result = #PB_Database_Quad
         Case 252
            Result = #PB_Database_Blob
         Case 253
            Result = #PB_Database_String
      EndSelect
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_DatabaseColumnSize(DataBase, Column)
   Protected *Result._MYSQL_FIELD_

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseColumnSize(DataBase, Column)
   EndIf
   If Column < MySQL_DBs()\FieldCount
      Select MySQL_DatabaseColumnType(DataBase, Column)
         Case #PB_Database_String, #PB_Database_Blob
            ProcedureReturn MySQL_DBs()\mysqlLen\len[Column]
         Default
            *Result = MySQL_Fetch_Field_Direct(MySQL_DBs()\mysqlResult, Column)
            If *Result
               ProcedureReturn *Result\db_length
            EndIf
      EndSelect
   EndIf

   ProcedureReturn 0
EndProcedure

Procedure MySQL_DatabaseColumnIndex(DataBase, Columnname.s)
   Protected *Result._MYSQL_FIELD_, Result, Count

   If MySQL_FindDataBase(DataBase) = 0
      ProcedureReturn DatabaseColumnIndex(DataBase, Columnname)
   EndIf
   Count = MySQL_DatabaseColumns(DataBase) - 1
   For Result = 0 To Count
      *Result = MySQL_Fetch_Field_Direct(MySQL_DBs()\mysqlResult, Result)
      If *Result
         If LCase(PeekS(*Result\name, -1, #PB_UTF8)) = LCase(Columnname)
            Break
         EndIf
      Else
         Result = -1
         Break
      EndIf
   Next Result
   If Result > Count
      Result = -1
   EndIf

   ProcedureReturn Result
EndProcedure

Procedure MySQL_FirstDatabaseRow(DataBase)
   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn FirstDatabaseRow(DataBase)
   EndIf
   MySQL_LastErrorFlag = #True
   With MySQL_DBs()
      If \mysqlResult
         MySQL_Data_Seek(\mysqlResult, 0)
         \mysqlRow = MySQL_Fetch_Row(\mysqlResult)
         If \mysqlRow
            \mysqlLen   = MySQL_Fetch_Lengths(\mysqlResult)
            \FieldCount = MySQL_Num_Fields(\mysqlResult)
         EndIf
      EndIf
   EndWith

   ProcedureReturn MySQL_DBs()\mysqlRow
EndProcedure

; some macros to overright the PB procedures
Macro AffectedDatabaseRows(a)
   MySQL_AffectedDataBaseRows(a)
EndMacro

Macro CheckDatabaseNull(a, b)
   MySQL_CheckDatabaseNull(a, b)
EndMacro

Macro CloseDatabase(a)
   MySQL_CloseDatabase(a)
EndMacro

Macro DatabaseError()
   MySQL_GetError()
EndMacro

Macro DatabaseID(a)
   MySQL_DatabaseID(a)
EndMacro

Macro DatabaseQuery(a, b, c = 0)
   MySQL_DatabaseQuery(a, b, c)
EndMacro

Macro DatabaseUpdate(a, b)
   MySQL_DatabaseUpdate(a, b)
EndMacro

Macro FinishDatabaseQuery(a)
   MySQL_FinishDatabaseQuery(a)
EndMacro

Macro GetDatabaseDouble(a, b)
   MySQL_GetDatabaseDouble(a, b)
EndMacro

Macro GetDatabaseFloat(a, b)
   MySQL_GetDatabaseFloat(a, b)
EndMacro

Macro GetDatabaseLong(a, b)
   MySQL_GetDatabaseLong(a, b)
EndMacro

Macro GetDatabaseQuad(a, b)
   MySQL_GetDatabaseQuad(a, b)
EndMacro

Macro GetDatabaseString(a, b)
   MySQL_GetDatabaseString(a, b)
EndMacro

Macro IsDatabase(a)
   MySQL_IsDatabase(a)
EndMacro

Macro NextDatabaseRow(a)
   MySQL_NextDatabaseRow(a)
EndMacro

Macro OpenDatabase(a, b, c, d, e = 0)
   MySQL_OpenDatabase(a, b, c, d, e)
EndMacro

; more Macros, which i wasn't in need of
; if you need them, feel free to implement them on your own..

Macro DatabaseColumnIndex(a, b)
   MySQL_DatabaseColumnIndex(a, b)
EndMacro
;
Macro DatabaseColumnName(a, b)
   MySQL_DatabaseColumnName(a, b)
EndMacro

Macro DatabaseColumnSize(a, b)
   MySQL_DatabaseColumnSize(a, b)
EndMacro

Macro DatabaseColumnType(a, b)
   MySQL_DatabaseColumnType(a, b)
EndMacro
;
Macro DatabaseColumns(a)
   MySQL_DatabaseColumns(a)
EndMacro

; implemented, but slows a bit down and PreviousDatabaseRow() is not easy possible.
;Macro FirstDatabaseRow(a)
;   MySQL_FirstDatabaseRow(a)
;EndMacro
;
Macro GetDatabaseBlob(a, b, c, d)
   MySQL_GetDatabaseBlob(a, b, c, d)
EndMacro
;
;Macro PreviousDatabaseRow(a)
;   MySQL_PreviousDatabaseRow(a)
;EndMacro
;
; Macro SetDatabaseBlob(a, b, c, d)
;    MySQL_SetDatabaseBlob(a, b, c, d)
; EndMacro

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Last edited by HeX0R on Mon Aug 15, 2016 8:12 pm, edited 10 times in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 9:48 am 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 3979
Location: Germany
Hi,

GREAT :!:


For the problem of DatabaseError():

Use a global variable MySQL_LastErrorFlag

And set it to #True if the last access was via a MySQL command.
Set it to #False if you call an other DB.

Than you can simply check if the last command was for a MySQL database.

Bernd


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 10:45 am 
Offline
Addict
Addict
User avatar

Joined: Tue Oct 09, 2007 2:15 am
Posts: 994
Image

And now I found a bug on Win7x64 ... PB5.20b15x64

Use this this little example with the standard xampp database cdcol :

Code:
User.s = "username"
Pass.s = "password"

XIncludeFile "mysql.pbi"

UseMySQLDataBase()

db = OpenDatabase(#PB_Any, "host=localhost dbname=cdcol", User, Pass, #PB_Database_MySQL)

If db
  a = DatabaseQuery(DB, "SELECT * FROM  `cds`")
  If a
    While NextDatabaseRow(db)
      Debug GetDatabaseString(db,0) 
      Debug GetDatabaseString(db,1)
      Debug GetDatabaseLong(db,2)
      Debug GetDatabaseLong(db,3)
    Wend
  EndIf
  CloseDatabase(db)
EndIf


If you compile it with PBx86 all fine, but PBx64 gives an IMA on GetDatabaseLong and GetDatabaseQuad with the unicode flag (in ascii all ok)
(and yes, I get the x64 version of libmysl.dll ;) ) ... Libmysql.dll is from "mysql-connector-c-6.1.0" archive...

and here the sql dump of this db:
Code:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE DATABASE IF NOT EXISTS `cdcol` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
USE `cdcol`;

CREATE TABLE IF NOT EXISTS `cds` (
  `titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `jahr` int(11) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
('Beauty', 'Ryuichi Sakamoto', 1990, 1),
('Goodbye Country (Hello Nightclub)', 'Groove Armada', 2001, 4),
('Glee', 'Bran Van 3000', 1997, 5);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

_________________
PureBasic 5.46 LTS / 5.62 (Windows x86/x64) | Windows10 Pro x64 | Z370 Extreme4 | i7 8770k | 32GB RAM | iChill GeForce GTX 980 X4 Ultra | HAF XF Evo​​
English is not my native language... (I often use DeepL to translate my texts.)


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 1:17 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
@intratec:
Clever idea, i will implement this in the next version.

@Bisonte:
Strange!
I heavily use this include with PBx64 Linux and Unicode on without any problems.
Which PB Version are you using?
And also, are the two strings correctly debugged before the error rises?

Still wondering why this should only happen with activated unicode.

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 1:57 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Oct 09, 2007 2:15 am
Posts: 994
Bisonte wrote:
And now I found a bug on Win7x64 ... PB5.20b15x64


I tested it with this. Yes the strings are fine, only the Longs and (i tested this) Quads... and only x64 with unicode.
and with PB5.20b15 x86 there no errors... ascii or unicode, all ok.

I don't check Floats and Doubles... maybe also there ?

Edit :

For testing I add this x86/x64 dll switch to the UseMySQLDatabase()
Code:
   CompilerSelect #PB_Compiler_OS
     CompilerCase #PB_OS_Windows
       CompilerIf #PB_Compiler_Processor = #PB_Processor_x64
         MySQL_Lib = OpenLibrary(#PB_Any, "libmysql_x64.dll")
       CompilerElse
         MySQL_Lib = OpenLibrary(#PB_Any, "libmysql.dll")
       CompilerEndIf
       
      CompilerCase #PB_OS_Linux
         MySQL_Lib = OpenLibrary(#PB_Any, "libmysqlclient.so.16")
      CompilerCase #PB_OS_MacOS
         ;???
   CompilerEndSelect

_________________
PureBasic 5.46 LTS / 5.62 (Windows x86/x64) | Windows10 Pro x64 | Z370 Extreme4 | i7 8770k | 32GB RAM | iChill GeForce GTX 980 X4 Ultra | HAF XF Evo​​
English is not my native language... (I often use DeepL to translate my texts.)


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 2:10 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
Bisonte wrote:
And now I found a bug on Win7x64 ... PB5.20b15x64

Ahrgh, o.k. i need glasses, sorry.

Could you please check with PB5.11, just to make sure it has nothing to do with the beta version?
(I use PB5.20B14 btw.)

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Sep 03, 2013 10:33 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
O.k., no answer means i had to install xampp, and check it on my own... :x

I were able to reproduce this behaviour using exactly the same files as Bisonte.
Strange thing is, that with this win dll i got LONGs as Length and not INTEGERs like on Linux.

I did a quick fix for it, so your test should now work Bisonte.
But i'm not sure if this is the correct way to handle this.

But anyway (i am not in need of the windows-version in this case), i've put in online.

And also some nice improvements from infratec, thanks again!

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Wed Sep 04, 2013 7:02 am 
Offline
Addict
Addict
User avatar

Joined: Tue Oct 09, 2007 2:15 am
Posts: 994
HeX0R wrote:
O.k., no answer means i had to install xampp, and check it on my own... :x

sorry, the birthday party of my wife....

Yes! Now it works! Thx for the fix.

_________________
PureBasic 5.46 LTS / 5.62 (Windows x86/x64) | Windows10 Pro x64 | Z370 Extreme4 | i7 8770k | 32GB RAM | iChill GeForce GTX 980 X4 Ultra | HAF XF Evo​​
English is not my native language... (I often use DeepL to translate my texts.)


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Mon Oct 28, 2013 12:16 pm 
Offline
User
User

Joined: Fri Aug 06, 2010 11:46 pm
Posts: 24
I have two changes for you:

1) Bugfix: MySQL_DatabaseUpdate should be give a #True as Result.

Code:
Procedure MySQL_DatabaseUpdate(DataBase, Query.s)
   Protected Result

   If MySQL_FindDataBase(DataBase) = 0
      MySQL_LastErrorFlag = #False
      ProcedureReturn DatabaseUpdate(DataBase, Query)
   EndIf
   MySQL_LastErrorFlag = #True
   If MySQL_Real_Query(MySQL_DBs()\DataBaseID, Query, StringByteLength(Query, #PB_UTF8)) = 0
      ;yes, strange but true... in this case a result of 0 means success.
      Result = #True
   EndIf

   ProcedureReturn Result
EndProcedure


2) a suggestion:

Please add "flags" to OpenDatabase, so we can use:

Code:
Enumeration Flags
  #MySQL_CLIENT_COMPRESS = 32 ;client_flag for compress
  #MySQL_CLIENT_MULTI_STATEMENTS = 65536
EndEnumeration


changed OpenDatabase-Code
Code:
; Open database
; uses same structure as the #PB_Database_PostgreSQL-Plugin (but no hostaddr is used).
Procedure MySQL_OpenDatabase(DataBase, Name$, User$, Password$, Plugin)
Protected i, a$, ParameterName.s, ParameterValue.s, host.s, hostaddr.i, port.i, dbname.s, handle, flags.i

   If Plugin <> #PB_Database_MySQL
      ;o.k. nothing for us, so let PB handle it.
      ProcedureReturn OpenDatabase(DataBase, Name$, User$, Password$, Plugin)
   EndIf
   If MySQL_Init = 0
      ;user forgot to call UseMySQLDataBase() (or the library isn't available)
      ProcedureReturn 0
   EndIf
   If DataBase <> #PB_Any
      ;we check, if there is already a database with this ID open
      If MySQL_FindDataBase(DataBase)
         ;yes, so we will close it.
         ;first check, if there is a query open.
         If MySQL_DBs()\mysqlResult
            MySQL_Free_Result(MySQL_DBs()\mysqlResult)
            MySQL_DBs()\FieldCount  = 0
            MySQL_DBs()\mysqlLen    = 0
            MySQL_DBs()\mysqlRow    = 0
            MySQL_DBs()\mysqlResult = 0
         EndIf
         MySQL_Close(MySQL_DBs()\DataBaseID)
         ;now delete it
         DeleteElement(MySQL_DBs())
      EndIf
   EndIf
   ;Check the parameters
   For i = 0 To CountString(Name$, " ")
      a$ = Trim(StringField(Name$, i + 1, " "))
      If a$
         ParameterName  = LCase(Trim(StringField(a$, 1, "=")))
         ParameterValue = Trim(StringField(a$, 2, "="))
         Select ParameterName
            Case "host"
               Host = ParameterValue
            Case "hostaddr"
               hostaddr = Val(ParameterValue)
            Case "port"
               port = Val(ParameterValue)
            Case "dbname"
              dbname = ParameterValue
            Case "flags"
              flags.i = Val(ParameterValue)
         EndSelect
      EndIf
   Next i
   If dbname = ""
      dbname = User$
   EndIf
   If host = ""
      host = "localhost"
   EndIf
   handle = MySQL_Init(#Null)
   If handle
      If MySQL_Real_Connect(handle, host, User$, Password$, dbname, Port, #Null, flags.i) = 0
         ;something went wrong...
         handle = #Null
      Else
         ;yessss... now add this client, to be sure we will mark it as a mysql client
         AddElement(MySQL_DBs())
         MySQL_DBs()\DataBaseID = handle
         If DataBase = #PB_Any
            MySQL_DBs()\DataBaseNum = @MySQL_DBs()
            handle                  = @MySQL_DBs()
         Else
            MySQL_DBs()\DataBaseNum = DataBase
         EndIf
         ;now set the client charset to utf8
         MySQL_Set_Character_Set(MySQL_DBs()\DataBaseID, "utf8")
      EndIf
   EndIf

   ProcedureReturn handle
EndProcedure


Thank you for your Fresh Code.

I use your code now instead of my old Lib:
viewtopic.php?f=5&t=48634&hilit=mysql


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Mon Oct 28, 2013 8:21 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
Fixed, and thanks for the improvement.
Code of first post updated.

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Mon Oct 28, 2013 10:39 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 21, 2011 9:11 am
Posts: 603
Location: France
Thank for sharing and for this update. Can you add a function to know the version number of MySQL ?

_________________

➽ Windows 10 - PB 5.45 LTS & PB 5.60

http://falsam.com & EasySprite.js
http://purebasic.chat - Code PureBasic
Image Pure Basic Francophone Community

Sorry for my bad english and the Dunning–Kruger effect.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Mon Oct 28, 2013 11:35 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
falsam wrote:
Thank for sharing and for this update. Can you add a function to know the version number of MySQL ?

This should be quite easy to implement, just look at mysql_get_server_version()

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Tue Aug 12, 2014 8:58 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Mon Sep 20, 2004 7:12 am
Posts: 477
Location: Hell
Fixed DatabaseColumnName().

Thanks to infratec!

_________________
Link dead?
Change h3x0r.ath.cx into hex0rs.coderbu.de and all will be fine.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Sun Jun 21, 2015 2:31 am 
Offline
Enthusiast
Enthusiast

Joined: Fri Feb 20, 2009 9:24 am
Posts: 530
Location: Almaty (Kazakhstan)
can you give a little explanation for a noob in mysql part?

1. i need to make copy code from first message and save it as mysql.pbi?

2. make another example and copy to it Bisonte code from 3 post. (both files unicode and without threadsafe)

3. then i need a mysql - i found EasyPHP DevServer 14.1 VC9. installed it.

4. now i need to know port. 3306:
Image

5. i dont know hot to set password for root in this mysql. but i think i can make another user and database. this EasyPHP have some options for it - PhpMyAdmin:
Image
seregaz, anyhost, 1234567890, create database seregaz too, and give all priveleges

6. then i need to change Bisonte examle code to:
Code:
User.s = "seregaz"
Pass.s = "1234567890"

XIncludeFile "G:\DISTR\SEREGASOFT\MYSQL\mysql.pbi"

UseMySQLDataBase()

db = OpenDatabase(#PB_Any, "host=localhost dbname=seregaz port=3306", User, Pass, #PB_Database_MySQL)

If db
  Debug "work"
  a = DatabaseQuery(DB, "SELECT * FROM  `cds`")
  If a
    While NextDatabaseRow(db)
      Debug GetDatabaseString(db,0) 
      Debug GetDatabaseString(db,1)
      Debug GetDatabaseLong(db,2)
      Debug GetDatabaseLong(db,3)
    Wend
  EndIf
  CloseDatabase(db)
Else
  Debug "not work"
  Debug DatabaseError()
  Debug MySQL_GetError()
EndIf


and not work. what i am doing wrong?


p.s.:
; When you are interested in an error message,
; use MySQL_GetError(#DataBase) instead of DatabaseError()

MySQL_GetError(#DataBase) can be MySQL_GetError() - without parametres.


and i think i need to copy libmysql.dll from easyphp folder to folder with project. i coped it, but anyway didnt work.

so i found how to set password for root:
find mysql.exe in easyphp folder
launch this file with parameter -u root
then input this command: SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(‘1234567890’);
(probably i will need repeat it for 'root'@'127.0.0.1' and 'root'@'::1')

after this PhpMyAdmin is broke :) i find config.inc.php file in easyphp folder and change password options:
$cfg['Servers'][$i]['password'] = '1234567890';

then PhpMyAdmin is work again. i delete my seregaz database, create new one - "root" with utf8 bin collation selector.

then tada! i have connection. now i will study how to make table and work with it.


Top
 Profile  
Reply with quote  
 Post subject: Re: mysql.pbi [linux & win]
PostPosted: Sun Jun 21, 2015 12:41 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 3979
Location: Germany
Hi,

in general: when you don't have to use mySQL, then don't use it.
The license requires that you have to pay for it if you use it in a comercial program.

I would prefer Postgres.

The port is optional, since 3306 is the default port when you install mysql from scratch.

In general the sql syntax is the same as for other databases.
What's different is the use of ` and '.

Best is: use always lower case for your names.

Use create table :mrgreen:
You can look also at sqlite examples, since sql92 is sql92.

Bernd


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 28 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye