posted March 01, 2003 11:35 PM            

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ This is an example of how to use a MySQL database in your program
'~~~ without having access to a running MySQL server.
'~~~ 
'~~~ All you need is to distribute the libmysqld.dll library with your
'~~~ program. You can get it from www.mysql.com 
'~~~
'~~~ This was tested with version 4.0.11
'~~~
'~~~ By Torben Marcussen (March 2003)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Compile Exe
#Include "MySQL.inc"
Function Pbmain() As Long   
 
    Local db		As T_MYSQL Ptr
    Local rs		As T_MYSQL_RES Ptr
    Local record	As Asciiz Ptr
    Local SQL		As Asciiz * 255
    Local x		As Long

    '~~~ Start the Embedded Server.
    '~~~ This has to be called at the start of the program.
    '~~~ The parameter is the path to the data directory.
    mySQL_server_connect("./")

    '~~~ Connect to a database. Leave blank if just connecting to
    '~~~ the server. (If creating a database)
    db = mySQL_connect("")
    If db = %NULL Then Exit Function

    '~~~ Create a database
    SQL = "create database if not exists myDB"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    '~~~ Select a database to use. Not needed if a database was
    '~~~ selected when connecting
    SQL = "use myDB"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    '~~~ Create a table in the current database
    SQL = "create table if not exists myTable (username VARCHAR(50))"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    '~~~ Insert some records into the table
    For x = 1 To 10
        SQL = "insert into myTable (username) Values ('user" & Format$(x) & "')"
        rs = mySQL_execute(db, SQL)
        If mySQL_errno(db) <> 0 Then Exit Function
    Next

    '~~~ change some data in the table
    SQL = "update myTable set username = 'test1' where username = 'user1'"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    '~~~ Loop through all the records in the database and display
    '~~~ the Data
    SQL = "select * from myTable"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    record = mysql_fetch_row(rs)
    Do While record <> %NULL
        Msgbox mySQL_data(record,1)			'~~~ Column 1
        record = mysql_fetch_row(rs)
    Loop

    '~~~ Delete the database
    SQL = "drop database myDB"
    rs = mySQL_execute(db, SQL)
    If mySQL_errno(db) <> 0 Then Exit Function

    '~~~ Close the database
    Call mySQL_Close(db)

    '~~~ Close the embedded server.
    '~~~ Must be called at the end of your program
    Call mysql_server_end()

    Msgbox "done"

End Function

And the include file:
MySQL.inc

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' (MOSTLY) Ported to PowerBASIC by Florent Heyworth - 17-Sep-2001 (incomplete)
'
' Added some functions to use the Embedded Server.
' March 2003
' Torben Marcussen
'
' Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
' This library is free software; you can redistribute it and/or
' modify it under the terms of the GNU Library General Public
' License as published by the Free Software Foundation; either
' version 2 of the License, or (at your option) any later version.
' This library is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
' Library General Public License for more details.
' You should have received a copy of the GNU Library General Public
' License along with this library; if not, write to the Free
' Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
' MA 02111-1307, USA */
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'mysql_option enum
%MYSQL_OPT_CONNECT_TIMEOUT  = 0
%MYSQL_OPT_COMPRESS         = 1
%MYSQL_OPT_NAMED_PIPE       = 2
%MYSQL_INIT_COMMAND         = 3
%MYSQL_READ_DEFAULT_FILE    = 4
%MYSQL_READ_DEFAULT_GROUP   = 5
%MYSQL_SET_CHARSET_DIR      = 6
%MYSQL_SET_CHARSET_NAME     = 7 
%MYSQL_OPT_LOCAL_INFILE     = 8
'mysql_status enum
%MYSQL_STATUS_READY         = 0
%MYSQL_STATUS_GET_RESULT    = 1
%MYSQL_STATUS_USE_RESULT    = 2
%MYSQL_ERRMSG_SIZE          = 200
%CLIENT_LONG_PASSWORD       = 1
%CLIENT_FOUND_ROWS          = 2
%CLIENT_LONG_FLAG           = 4
%CLIENT_CONNECT_WITH_DB     = 8
%CLIENT_NO_SCHEMA           = 16
%CLIENT_COMPRESS            = 32
%CLIENT_ODBC                = 64
%CLIENT_LOCAL_FILES         = 128
%CLIENT_IGNORE_SPACE        = 256
%CLIENT_CHANGE_USER         = 512
%CLIENT_INTERACTIVE         = 1024
%CLIENT_SSL					= 2048
%CLIENT_IGNORE_SIGPIPE      = 4096
%CLIENT_TRANSACTIONS        = 8196
'enum field types
%FIELD_TYPE_DECIMAL         = 0
%FIELD_TYPE_TINY            = 1
%FIELD_TYPE_SHORT           = 2
%FIELD_TYPE_LONG            = 3
%FIELD_TYPE_FLOAT           = 4
%FIELD_TYPE_DOUBLE          = 5
%FIELD_TYPE_NULL            = 6
%FIELD_TYPE_TIMESTAMP       = 7
%FIELD_TYPE_LONGLONG        = 8
%FIELD_TYPE_INT24           = 9
%FIELD_TYPE_DATE            = 10
%FIELD_TYPE_TIME            = 11
%FIELD_TYPE_DATETIME        = 12
%FIELD_TYPE_YEAR            = 13
%FIELD_TYPE_NEWDATE         = 14
%FIELD_TYPE_ENUM            = 247
%FIELD_TYPE_SET             = 248
%FIELD_TYPE_TINY_BLOB       = 249
%FIELD_TYPE_MEDIUM_BLOB     = 250
%FIELD_TYPE_LONG_BLOB       = 251
%FIELD_TYPE_BLOB            = 252
%FIELD_TYPE_VAR_STRING      = 253
%FIELD_TYPE_STRING          = 254   
     
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Type T_USED_MEM
    ptNext As T_USED_MEM Ptr
    dwLeft As Dword
    dwSize As Dword
End Type
Type T_MEM_ROOT
    ptFree As T_USED_MEM Ptr
    ptUsed As T_USED_MEM Ptr
    ptPreAlloc As T_USED_MEM Ptr
    dwMinMalloc As Dword
    dwBlockSize As Dword
End Type
Type T_MYSQL_OPTIONS
    dwConnectTimeout As Dword
    dwClientFlag As Dword
    bFlagCompress As Byte
    bFlagNamedPipe As Byte
    pszHost As Asciiz Ptr
    pszInitCommand As Asciiz Ptr
    pszUser As Asciiz Ptr
    pszPassword As Asciiz Ptr
    pszUnixSocket As Asciiz Ptr
    pszDb As Asciiz Ptr
    pszMyConfFile As Asciiz Ptr
    pszMyConfGroup As Asciiz Ptr
    pszCharsetDir As Asciiz Ptr
    pszCharsetName As Asciiz Ptr
    bFlagUseSSL As Byte
    pszSSLKey As Asciiz Ptr
    pszSSLCert As Asciiz Ptr
    pszSSLCa As Asciiz Ptr
    pszSSLCaPath As Asciiz Ptr
End Type
Type T_NET
    dwVio As Dword
    lSock As Long
    lFcntl As Long
    pbBuf As Byte Ptr
    pbBufEnd As Byte Ptr
    pbWritePos As Byte Ptr
    pbReadPos As Byte Ptr
    szLastError As Asciiz * %MYSQL_ERRMSG_SIZE
    lLastErrNo As Long
    lMaxPacket As Long
    lTimeOut As Long
    lPktNr As Long
    bError As Byte
    bFlagReturnErrNo As Byte
    bFlagCompress As Byte
    bFlagNoSendOk As Byte
    lRemainInBuf As Long
    lBufLength As Long
    lWhereb As Long
    plReturnStatus As Long Ptr
    bReadingOrWriting As Byte
    bSaveChar As Byte
End Type
Type T_MYSQL_FIELD
    pszNAME As Asciiz Ptr
    pszTable As Asciiz Ptr
    pszDefault As Asciiz Ptr
    lFieldType As Long
    dwLength As Dword
    dwMaxLength As Dword
    dwFlags As Dword
    dwDecimals As Dword
End Type
Type T_CHARSET_INFO
    dwNumber As Dword
    pszName As Asciiz Ptr
    pbCType As Byte Ptr
    pbToLower As Byte Ptr
    pbToUpper As Byte Ptr
    pbSortOrder As Byte Ptr
    dwStrxFrmMultiply As Dword
    pFuncStrColl As Dword
    pFuncStrxFrm As Dword
    pFuncStrnnColl As Dword
    pFuncStrnxFrm As Dword
    pbFuncLikeRange As Dword
    dwMbMaxLen As Dword
    pFuncIsMbChar As Dword
    pbFuncIsMbHead As Dword
    pMbCharLen As Dword
End Type
Type T_MYSQL
    tNet As T_NET
    pszGptr As Asciiz Ptr
    pszHost As Asciiz Ptr
    pszUser As Asciiz Ptr
    pszPassword As Asciiz Ptr
    pszUnixSocket As Asciiz Ptr
    pszServerVersion As Asciiz Ptr
    pszHostInfo As Asciiz Ptr
    dwPort As Dword
    dwClientFlag As Dword
    dwServerCapabilities As Dword
    dwProtocolVersion As Dword
    dwFieldCount As Dword
    dwServerStatus As Dword
    dwThreadId As Dword
    qAffectedRows As Quad
    qInsertId As Quad
    qExtraInfo As Quad
    ptFields As T_MYSQL_FIELD Ptr
    tFieldAlloc As T_MEM_ROOT
    bFlagFreeMe As Byte
    bFlagReconnect As Byte
    Toptions As T_MYSQL_OPTIONS
    szScramble As Asciiz * 9
    ptCharset As T_CHARSET_INFO Ptr
    dwServerLanguage As Dword
End Type
Type T_MYSQL_ROWS
    ptNext As Dword
    pszData As Asciiz Ptr
End Type
Type T_MYSQL_DATA
    qRows As Quad
    dwFields As Dword
    ptData As T_MYSQL_ROWS Ptr
    tAlloc As T_MEM_ROOT
End Type
Type T_MYSQL_RES
    qRowCount As Quad
    dwFieldCount As Dword
    dwCurrentField As Dword
    ptFields As T_MYSQL_FIELD Ptr
    ptData As T_MYSQL_DATA Ptr
    ptDataCursor As T_MYSQL_ROWS Ptr
    ptFieldAlloc As T_MEM_ROOT
    pszRow As Asciiz Ptr
    pszCurrentRow As Asciiz Ptr
    pdwLength As Dword Ptr
    tHandle As T_MYSQL Ptr
    bEof As Byte
End Type
                     
      
                                                                                                               
'***************************************************************************************************************
'connection management routines
Declare Sub      mySQL_close          Lib "libmySQLd.DLL" Alias "mysql_close"        (ptMYSQL  As T_MYSQL Ptr)
Declare Function mySQL_init           Lib "libmySQLd.DLL" Alias "mysql_init"         (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Function mySQL_server_init    Lib "libmySQLd.DLL" Alias "mysql_server_init"  (ByVal d1 As Long, ByVal d2 As Dword, ByVal d3 As Dword) As Long
Declare Sub      mySQL_server_end     Lib "libmySQLd.DLL" Alias "mysql_server_end"   ()
Declare Function mySQL_options        Lib "libmySQLd.DLL" Alias "mysql_options"      (ptMYSQL  As T_MYSQL Ptr, ByVal lOption As Long,_
                                                                                                              szArg As Any) As Long 
Declare Function mySQL_ping           Lib "libmySQLd.DLL" Alias "mysql_ping"         (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Function mySQL_real_connect   Lib "libmySQLd.DLL" Alias "mysql_real_connect" (ptMYSQL  As T_MYSQL Ptr, szHostName As Asciiz,_
                                                                                     szUserName As Asciiz, szPassword As Asciiz,_
                                                                                       szDbName As Asciiz, ByVal lPortNum As Long,_
                                                                                   szSocketName As Asciiz, ByVal lFlags As Long) As Long
'STATUS AND ERROR-reporting routines
Declare Function mySQL_errno          Lib "libmySQLd.DLL" Alias "mysql_errno"        (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Function mySQL_error          Lib "libmySQLd.DLL" Alias "mysql_error"        (ptMYSQL  As T_MYSQL Ptr) As String
'QUERY CONTRUCTION and execution routines
Declare Function mySQL_query          Lib "libmySQLd.DLL" Alias "mysql_query"        (ptMYSQL  As T_MYSQL Ptr, sQueryString As Asciiz) As Long
Declare Function mySQL_select_db      Lib "libmySQLd.DLL" Alias "mysql_select_db"    (ptMYSQL  As T_MYSQL Ptr, szDbName As Asciiz) As Long 
Declare Function mySQL_create_db      Lib "libmySQLd.DLL" Alias "mysql_create_db"    (ptMYSQL  As T_MYSQL Ptr, szDbName As Asciiz) As Long
Declare Function mySQL_drop_db        Lib "libmySQLd.DLL" Alias "mysql_drop_db"      (ptMYSQL  As T_MYSQL Ptr, szDbName As Asciiz) As Long
                                                                                     
'RESULT SET PROCessing routines
Declare Function mySQL_affected_rows  Lib "libmySQLd.DLL" Alias "mysql_affected_rows"(ptMYSQL  As T_MYSQL Ptr) As Long
Declare Sub      mySQL_data_seek      Lib "libmySQLd.DLL" Alias "mysql_data_seek"    (ptResult As T_MYSQL_RES Ptr, ByVal Offset As Quad)
Declare Function mySQL_fetch_field_direct Lib "libmySQLd.DLL" Alias "mysql_fetch_field_direct" (Ptresult As T_MYSQL_RES Ptr, _
                                                                                                 ByVal lFieldNum As Long) As Long
Declare Function mySQL_fetch_lengths  Lib "libmySQLd.DLL" Alias "mysql_fetch_lengths"(ptResult As T_MYSQL_RES Ptr) As Long
Declare Function mySQL_fetch_row      Lib "libmySQLd.DLL" Alias "mysql_fetch_row"    (Ptresult As T_MYSQL_RES Ptr ) As Long
Declare Function mySQL_field_count    Lib "libmySQLd.DLL" Alias "mysql_field_count"  (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Sub      mySQL_free_result    Lib "libmySQLd.DLL" Alias "mysql_free_result"  (ptMYSQL  As T_MYSQL Ptr)
Declare Function mySQL_info           Lib "libmySQLd.DLL" Alias "mysql_info"         (ptMYSQL  As T_MYSQL Ptr) As String
Declare Function mySQL_insert_id      Lib "libmySQLd.DLL" Alias "mysql_insert_id"    (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Function mySQL_num_fields     Lib "libmySQLd.DLL" Alias "mysql_num_fields"   (Ptresult As T_MYSQL_RES Ptr) As Long
Declare Function mySQL_num_rows       Lib "libmySQLd.DLL" Alias "mysql_num_rows"     (ptResult As T_MYSQL_RES Ptr ) As Long
Declare Function mySQL_store_result   Lib "libmySQLd.DLL" Alias "mysql_store_result" (ptMYSQL  As T_MYSQL Ptr) As Long
Declare Function mySQL_use_result     Lib "libmySQLd.DLL" Alias "mysql_use_result"   (ptMYSQL  As T_MYSQL Ptr) As Long  
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#If Not %Def(%FALSE)
    %FALSE = 0
#Endif
#If Not %Def(%TRUE)
    %TRUE = 1
#Endif
#If Not %Def(%NULL)
    %NULL = 0
#Endif
                   
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ Setup a connection to the embedded server.
'~~~ You can add any command line parameter here.
Function mySQL_server_connect (dataDir As Asciiz) As Long
    Dim ServerOptions(6) As String
    ServerOptions(1) = "prog"			'~~~ Program name. Ignored
    serverOptions(2) = "--datadir=" & dataDir	'~~~ Data directory. Where database will be created
    ServerOptions(3) = "--skip-innodb"		'~~~ Skip the InnoDB tables. InnoDB will add 20M to you data dir.
    ServerOptions(4) = "--skip-bdb"		'~~~ Skip Berkley tables. Speeds up access
    ServerOptions(5) = "--skip-networking"	'~~~ No need for TCP/IP with embedded server.
    ServerOptions(6) = "--language=./english"	'~~~ No need for TCP/IP with embedded server.
 
    Dim serverGroups(3) As String
    ServerGroups(1) = "embedded"
    ServerGroups(2) = "NULL"
  
    mySQL_server_init(Ubound(serverOptions()),Varptr(serverOptions(1)),Varptr(ServerGroups(1))
End Function

'~~~ Setup a connection to database.
Function mySQL_Connect (dbName As Asciiz) As Dword
    Local db As T_MYSQL Ptr
    db = mySQL_init( ByVal %NULL )
    If db = %NULL Then
        Msgbox "mySQL_init failed: no memory", 16, "MySQL error"
        Exit Function
    End If
    If mySQL_real_connect(db, ByVal %NULL, ByVal %NULL, ByVal %NULL, dbName, ByVal 0, ByVal %NULL, ByVal 0 ) = %NULL Then
        Msgbox "mysql_real_connect failed: " & Format$(mySQL_errno(db)), 16, "MySQL error"
        Exit Function
    End If
    Function = db
End Function

'~~~ Execute a SQL query and return the recordset
Function mySQL_execute(db As T_MYSQL Ptr, SQL As Asciiz) As Dword
    Local rs	As T_MYSQL_RES Ptr
    If mySQL_Query(db, SQL) Then
        Msgbox "mysql_query: " & Format$(mySQL_errno(db)), 16, "MySQL error"
        Exit Function
    End If  
    Function = mysql_store_result(db)
End Function

'~~~ Return a given field from a record row
Function mySQL_data(record As Asciiz Ptr, field As Long) As String
    Function = Parse$(@@record, Chr$(0), field)
End Function

------------------

posted March 04, 2003 05:14 AM            

	Dim ServerOptions(6) As String
	ServerOptions(1) = "prog"						'~~~ Program name. Ignored
	serverOptions(2) = "--datadir=" & dataDir		'~~~ Data directory. Where database will be created
	ServerOptions(3) = "--skip-innodb"				'~~~ Skip the InnoDB tables. InnoDB will add 20M to you data dir.
	ServerOptions(4) = "--skip-bdb"					'~~~ Skip Berkley tables. Speeds up access
	ServerOptions(5) = "--skip-networking"			'~~~ No need for TCP/IP with embedded server.
	ServerOptions(6) = "--language=./english"		'~~~ Needed for Error codes

posted March 04, 2003 08:41 AM              

Thanks

Jason

posted May 31, 2003 08:01 AM            

However, it fails running because there's a mismatch in the mySQL_real_connect function:

Declare Function mySQL_real_connect Lib "libmySQLd.DLL" Alias "mysql_real_connect" (ptMYSQL As T_MYSQL Ptr, szHostName As Asciiz,szUserName As Asciiz, szPassword As Asciiz,szDbName As Asciiz, ByVal lPortNum As Long,szSocketName As Asciiz, ByVal lFlags As Long) As Long

[..]

Function mySQL_Connect (dbName As Asciiz) As Dword
[...] If mySQL_real_connect(db, ByVal %NULL, ByVal %NULL, ByVal %NULL, dbName, 0, ByVal %NULL, 0 ) = %NULL Then

Couple of questions:

1. Are some fields optional? I'm using PBWin 7, and it doesn't seem to allow calling a function with less parameters than set in the declaration.

2. What about performances over using the client-server version?

Thx again
Fred.

posted May 31, 2003 08:08 AM            

However, I get a GPF when PB calls mySQL_server_init() in mySQL_server_connect() using the 6 items that you added. I installed MySQL 4 for Windows, and copied C:\mysql\share\english\errmsg.txt as "english" where the PB EXE lives per your indications. Could that be the issue?

Thx
Fred.

posted May 31, 2003 08:52 AM            

Dim lResult as LONG
lResult = mySQL_server_init(0,%NULL,%NULL)

For information, the resulting file is located under C:\MYSQL\DATA\ (I had to install the C/S version to get the embedded version of MYSQL). I assume an entry in the Registry or some INI file somewhere tells MySQL where to save data. That issue will have to be resolved when using the embedded SQL.

Also, the file is big : 10MB for just 10 fifty-character strings... but this might be optional ("030531 17:46:43 InnoDB: Setting file C:\mysql\data\ibdata1 size to 10 MB")

Finally, you can compress the 2MB DLL to 800KB using the open-source UPX.EXE (http://upx.sourceforge.net
), and after trying out SQLite last night, I'm concerned about performance using server-less solutions.

Thx
Fred.

posted May 31, 2003 05:35 PM            

You must use the include file as I have provided for the code
to work, as I have changed some of the code for Don's include
for the embedded server to work.

If you use mySQL_server_init(0,%NULL,%NULL) then the embedded server
uses the mySQL defaults which is to put the datafile in c:\mysql\data

However, you can override this with the array of switches that
you supply to the mySQL_server_init function.

For example: --datadir will specify the data directory and
--skip-innodb will turn off the inno option and save you 10-20M

I think the problem you are having is because of the error messages.
You need to copy the entire english directory from the installation
(which only contains 1 file) rather that rename the error.txt file to
English...

For those who are interested, here is the complete working example:
http://www.marcussen.com.au/PBmysql.zip

Cheers
Torben

posted June 02, 2003 08:33 AM            

Indeed, I thought that I only had to copy the err msg file and
rename it to "english", while MySQL expects to find the directory
of that name located at c:\mysql\share\ ....

As an aside, it's better to create a sub-directory eg. "./data/"
to avoid having the standard err file at the same level as the EXE.

Bottom line :
- MySQL DLL compiled with UPX = 800KB
- no install necessary, apart from copying the DLL, the err
msg directory from ./share/ and, optionally a ./data/ sub-directory

Bah, I brushed up a bit on using pointers ;-) Wonder if I should
give up on SQLite or investigate its abysal perfomance on large
DBs...

Thx again. You've been of a great help
Fred.

posted June 19, 2003 07:33 AM            

Any help getting BLOBs back out again would be greatly appreciated (see my posting in PB Win forum)!

Bill

Declare Function mySQL_escape_string  Lib "libmySQLd.DLL" _
   Alias "mysql_escape_string" (ByVal pszDest As Dword, _
   ByVal pszSource As Dword, ByVal sourceLength As Long) As Long

Function mySQL_Escape(source As String) As String
   Local pSrc     As Dword       ' pointer to source string
   Local pDst     As Dword       ' pointer to destination string
   Local ret      As Long        ' return value 
   Local sLen     As Long        ' length of source string
   Local sLeft    As Long        ' length of source string left to process
   Local maxBuf   As Long        ' guess at max length that MySQL will process
   Local dLen     As Long        ' amount of destination string already processed
   Local dest     As String      ' destination string
   
   sLen = Len(source)
   ' Size destination string to maximum possible length (see mySQL manual)
   dest = String$(1 + (sLen * 2), 0)
   ' get pointers to source and destination strings
   pSrc = StrPtr(source)
   pDst = StrPtr(dest)
   ' Don't overrun maximum buffer length
   ' -- feed in 64 k chunks
   maxBuf = 65536
   ' initialize amount of data remaining
   sLeft = sLen   
   Do While sLeft > 0
      ' process as much as we can / need to
      maxBuf = Min(sLeft, maxBuf)
      ' update amount of data remaining
      sLeft = sLeft - maxBuf
      ret = mySQL_escape_string(pDst, pSrc, maxBuf)
      ' update total length of string processed
      dLen = dLen + ret
      ' Update read / write offsets
      pDst = pDst + ret
      pSrc = pSrc + maxBuf
   Loop
   ' Truncate destination string and return it 
   Function = Left$(dest, dLen)
End Function

------------------

posted June 22, 2003 06:34 PM            

I have also changed the serverGroups to stop the program from
crashing (GPF) if there is an old my.ini file left over from
a previous install...

Cheers
Torben

posted June 23, 2003 02:03 PM            

As written above mySQL_data() only retrieves the first column / field in a row. You have to read the offsets to the other fields as in the revised code below. Note that I have used 0-based indices (Torben used 1-based).

Since binary data might contain Chr$(0)s I wrote a complementary function mySQL_binarydata() to read columns by length.

I also re-typed two of the functions as DWord -- this may be the unnecessary paranoia of an amateur programmer.

Again many thanks to Torben (and to Florent for his earlier examples) for an excellent interface to a very power DB engine.

Bill

----------------------

' Changed declares
Declare Function mySQL_fetch_lengths  Lib "libmySQLd.DLL" Alias _
  "mysql_fetch_lengths" (ptResult As T_MYSQL_RES Ptr) As Dword
Declare Function mySQL_fetch_row Lib "libmySQLd.DLL" Alias _
   "mysql_fetch_row"    (Ptresult As T_MYSQL_RES Ptr ) As Dword
'
'....
' Changed mySQL_data()
'~~~ Return a given field from a record row
'~~~ Use only for text data not containing Chr$(0)
Function mySQL_data(record As Dword Ptr, field As Long) As String
   ' Multiple fields are returned as an array of ASCIIZ strings
   ' index "field" is 0-based!
   Local columndata As Asciiz Ptr
   ' Get pointer to field-th item
   columndata = @record[field]
   Function = @columndata
End Function
'
'
' Added mySQL_binarydata()
'~~~ Return a given binary field from a record row
'~~~ Use when data might contain Chr$(0)
Function mySQL_binarydata(row As Dword Ptr, field As Long, fieldlengths As Long Ptr) As String
   '
   ' index "field" is 0-based !
   '
   
   ' fieldlengths is a pointer to a long array which holds 
   ' the length of each column / field in the current row.
   '
   '   Local fieldlengths As Long Pointer
   '
   '    ' After each call to mysql_get_row() update the column size array:
   '   row = mysql_get_row(rs)
   '   fieldlengths = mysql_fetch_lengths(rs)
   '   ...
   '  To read a binary field (index "field" is 0-based!):
   '   fielddata = mySQL_binarydata(row, field, fieldlengths)
   '

   Function = Peek$(@row[field], @fieldlengths[field])
End Function

------------------

posted June 29, 2003 02:14 AM            

There must be something wrong with me as I cannot find how to download libmysqld.dll
from the www.mysql.com site. My slow connection does not help.

Can someone point me to the right location please?

Regards

David

Monday June 30, 2003

I managed to get the libmysqld.dll by downloading the example from Torben's site URL
It works exactly as the code example and I can now build on the example.

Thanks again.

posted June 30, 2003 02:07 AM            

The DLL is part of the complete MySql package that you download
from the MySQL site. Just click on Downloads from the main page
and select the 4.0 Windows package.

Or, you can just click on the link to my example a couple of
posts up from this one. That includes the DLL.

Cheers
Torben

posted June 30, 2003 03:20 PM            

Incidently, are you in Australia?

Regards

David

posted March 10, 2004 06:47 AM            

quote:

---

Originally posted by Torben Marcussen:
Frederic,

For those who are interested, here is the complete working example:
http://www.marcussen.com.au/PBmysql.zip

---

I don't think the link above is working anymore. Does anyone have
that file ?

Thanks

-stein