PowerBASIC Forums
  Source Code
  Embedded MySQL Server (Page 1)

Post New Topic  Post A Reply
profile | register | preferences | faq | search

UBBFriend: Email This Page to Someone!
This topic is 2 pages long:   1  2 
next newest topic | next oldest topic
Author Topic:   Embedded MySQL Server
Torben Marcussen
Member
posted March 01, 2003 11:35 PM     Click Here to See the Profile for Torben Marcussen     Edit/Delete Message   Reply w/Quote
Updated June 23, 2003


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ 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

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


[This message has been edited by Torben Marcussen (edited June 22, 2003).]

IP: Logged

Torben Marcussen
Member
posted March 04, 2003 05:14 AM     Click Here to See the Profile for Torben Marcussen     Edit/Delete Message   Reply w/Quote
It seems the dll needs access to the error codes
which can be found under the share directory in the
mysql installation. Just grap the language of your choice
and create a link to it in the serverOptions. EG:


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

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

IP: Logged

jforgey
unregistered
posted March 04, 2003 08:41 AM           Edit/Delete Message   Reply w/Quote
This is great! does this mean I would just need the DLL and not the complete mySQL installation?

Thanks

Jason

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

IP: Logged

Frederic Faure
Member
posted May 31, 2003 08:01 AM     Click Here to See the Profile for Frederic Faure     Edit/Delete Message   Reply w/Quote
Thx much Torben for sharing this piece of code :-)

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.

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

IP: Logged

Frederic Faure
Member
posted May 31, 2003 08:08 AM     Click Here to See the Profile for Frederic Faure     Edit/Delete Message   Reply w/Quote
Oops. I guess I got a different version of the MYSQL.INC file. The one above does have the right number of parameters. My bad :-)

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.

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

IP: Logged

Frederic Faure
Member
posted May 31, 2003 08:52 AM     Click Here to See the Profile for Frederic Faure     Edit/Delete Message   Reply w/Quote
Solved by cheating, and using this:

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.

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

IP: Logged

Torben Marcussen
Member
posted May 31, 2003 05:35 PM     Click Here to See the Profile for Torben Marcussen     Edit/Delete Message   Reply w/Quote
Frederic,

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

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

IP: Logged

Frederic Faure
Member
posted June 02, 2003 08:33 AM     Click Here to See the Profile for Frederic Faure     Edit/Delete Message   Reply w/Quote
Thx a bunch Torben :-)

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.

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

IP: Logged

Bill Fletcher
Member
posted June 19, 2003 07:33 AM     Click Here to See the Profile for Bill Fletcher     Edit/Delete Message   Reply w/Quote
Addition to MySQL.inc to escape special characters in a string so it can be included in an SQL statement. It maps ASCII 0 13 10 onto \0 \n \r etc and encodes ' to \' .

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


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


[This message has been edited by Bill Fletcher (edited June 19, 2003).]

IP: Logged

Torben Marcussen
Member
posted June 22, 2003 06:34 PM     Click Here to See the Profile for Torben Marcussen     Edit/Delete Message   Reply w/Quote
I have updated the above code to fix the type of the record
field....

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

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

IP: Logged

Bill Fletcher
Member
posted June 23, 2003 02:03 PM     Click Here to See the Profile for Bill Fletcher     Edit/Delete Message   Reply w/Quote
Just one more footnote before our friedly moderators slap a padlock on this thread.

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


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


[This message has been edited by Bill Fletcher (edited June 23, 2003).]

IP: Logged

David L Morris
Member
posted June 29, 2003 02:14 AM     Click Here to See the Profile for David L Morris     Edit/Delete Message   Reply w/Quote
Hi Torben and others

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.

[This message has been edited by David L Morris (edited June 29, 2003).]

IP: Logged

Torben Marcussen
Member
posted June 30, 2003 02:07 AM     Click Here to See the Profile for Torben Marcussen     Edit/Delete Message   Reply w/Quote
Hi David,

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

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

IP: Logged

David L Morris
Member
posted June 30, 2003 03:20 PM     Click Here to See the Profile for David L Morris     Edit/Delete Message   Reply w/Quote
Thanks Torben. My intro to MySQL was last weekend in Sydney, when I spotted a $14.95 book
in the news agent "Introduction to MySQL" which included a CD. I was very impressed and recalled
your posting in this forum. Hence, I am on my way.

Incidently, are you in Australia?

Regards

David

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

IP: Logged

Stein Oiestad
Member
posted March 10, 2004 06:47 AM     Click Here to See the Profile for Stein Oiestad     Edit/Delete Message   Reply w/Quote
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

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

IP: Logged


This topic is 2 pages long:   1  2 

All times are EasternTime (US)

next newest topic | next oldest topic

Administrative Options: Close Topic | Archive/Move | Delete Topic
Post New Topic  Post A Reply
Hop to:

Contact Us | PowerBASIC BASIC Compilers

Copyright 1999-2005 PowerBASIC, Inc. All Rights Reserved.


Ultimate Bulletin Board 5.45c