14.1 Quick Introduction
- Required files:
- MySQL server version 3.22.xx or better
- Rapid-Q MySQL Libraries
- MySQL Client if you don't want to run a server
14.2 Connecting to MySQL
DIM MySQL AS QMYSQL
Host$ = ""
User$ = ""
Password$ = ""
MySQL.Connect(Host$, User$, Password$)
IF MySQL.Connected THEN PRINT "Successfully connected"
Change User$ and Password$ to suit your needs. Host$ can also take the value of
"localhost" but is not necessary in this case. Now to connect to a remote MySQL
server located, say, in allsql.com
DIM MySQL AS QMYSQL
Host$ = "allsql.com"
User$ = "anonymous"
Password$ = "guest"
MySQL.Connect(Host$, User$, Password$)
IF MySQL.Connected THEN PRINT "Successfully connected"
Most servers will use the standard MySQL port 3306, but if you know the exact
port, then you can use the method RealConnect.
DIM MySQL AS QMYSQL
Host$ = "allsql.com"
User$ = "anonymous"
Password$ = "guest"
Port% = 12345
MySQL.RealConnect(Host$, User$, Password$, "", Port%, "", 0)
IF MySQL.Connected THEN PRINT "Successfully connected"
See appendix section for the parameters of QMYSQL.RealConnect.
It is suggested by the MySQL documentation that you use RealConnect instead
of the using Connect.
14.3 How to perform queries and obtain the results
DIM MySQL AS QMYSQL
'' First connect to server
'' Use database
MySQL.SelectDB("mysql")
'' Perform query
MySQL.Query("select * from user")
The result of this query is stored internally, and sequentially.
To obtain the result, you'll need to Fetch it. You can visualize a 2 dimensional
table:
WHILE MySQL.FetchRow '' Get next row
MySQL.FieldSeek(0) '' Reset field position
FOR I = 0 TO MySQL.FieldCount-1
PRINT MySQL.Row(I) '' Read current row element I
NEXT
WEND
MySQL.FetchRow fetches the next row. As long as there
are still rows available to fetch, MySQL.FetchRow returns TRUE (non-zero number).
MySQL.FieldCount is used to count how many fields per row there are in the
current table. The rest is self explanatory.
14.4 Interfacing QMYSQL with QSTRINGGRID
SUB ExecuteQuery(MySQL AS QMYSQL, Grid AS QSTRINGGRID, Query AS STRING)
IF MySQL.Query(Query) = 0 THEN
'' Failed to execute
EXIT SUB
END IF
DEFINT I = 0, J
''-- Write header
Grid.ColCount = MySQL.FieldCount
WHILE MySQL.FetchField
Grid.Cell(I,0) = MySQL.Field.Name
I++
WEND
Grid.RowCount = MySQL.RowCount+1
J = 1
WHILE MySQL.FetchRow '' Get next row
MySQL.FieldSeek(0) '' Reset field position
FOR I = 0 TO MySQL.FieldCount-1
Grid.Cell(I,J) = MySQL.Row(I)
NEXT
J++
WEND
END SUB
''-- Example
ExecuteQuery(MySQL, MyGrid, "select * from user")
Obviously, you can interface QMYSQL with any component, or components, you want.
14.5 Storing and retrieving blobs
$INCLUDE "RAPIDQ.INC"
DIM MySQL AS QMYSQL
''-- Connect to database code, etc...
DIM File AS QFILESTREAM
File.Open("test.bmp", fmOpenRead)
''-- Read file as binary, preserves NULL chars
Buffer$ = File.ReadBinStr(File.Size)
''-- Here's the special processing
''-- Converts NULLs to \0 and others
ProcessedBuffer$ = MySQL.EscapeString(Buffer$, File.Size)
''-- Insert blob in our table
MySQL.Query("insert into MyImageTable values('Label 1', '" + _
ProcessedBuffer$ + "')")
File.Close
MySQL.Close
Since we're dealing with binary data, you have to be aware that we don't use LEN()
or any other STRING processing functions with our binary data. Note that we can
store text data in blob fields, in which case you can use any STRING processing
functions you want. You may notice that MySQL.EscapeString
converts the binary data to a usable format that can be passed to the MySQL database.
Basically all it does is convert NULLs to '\0', ' to \', " to \", and CRLF to \r and \n
respectively. Once parsed, the new string is returned which you can use in a
query. So far so good, now we consider retrieving our blobs:
$INCLUDE "RAPIDQ.INC"
$INCLUDE "MYSQL.INC"
DIM MySQL AS QMYSQL
''-- Connect to database code, etc...
DIM File AS QFILESTREAM
File.Open("out.bmp", fmCreate) ''-- Create new file
''-- Your query here
MySQL.Query("select * from MyImageTable")
WHILE MySQL.FetchRow
MySQL.FieldSeek(0)
MySQL.FetchLengths ''-- Required for binary data
FOR I = 0 TO MySQL.NumField-1
MySQL.FetchField
IF MySQL.Field.Type = FIELD_TYPE_BLOB THEN
''-- Read binary data from table
Buffer$ = MySQL.RowBlob(I, MySQL.Length(I))
File.WriteBinStr(Buffer$, MySQL.Length(I))
ELSE
Buffer$ = MySQL.Row(I)
END IF
NEXT
WEND
File.Close
MySQL.Close
Retrieving blobs from the database is a little more confusing than storing blobs.
The first thing you have to note is that reading binary data and text strings are 2
different operations. Since text strings are NULL terminated, you don't have to
worry about how many bytes to read, this is automatically calculated for you.
However, since binary data can contain NULL characters, this makes reading
blobs a little bit tricky. You have to fetch the lengths of each field, since
we have blobs in our table, this is done by calling MySQL.FetchLengths
for each row. Then to find out the length of each field, you use the internal array
MySQL.Length(index%) which returns the length for field index%.
Why do we have to do this? Because we need to know the size of our blob before we can read it in.
To read binary data from our database, you use the specialized function
MySQL.RowBlob(Row%, Bytes%) which returns a binary
string. With this binary string, you can write the data to a file or memory, or
manipulate the data directly. In the above example, we're just writing the data
to a file.
14.6 Using LOADBLOB and SAVEBLOB
''-- Recall the natural way
ProcessedBuffer$ = MySQL.EscapeString(Buffer$, File.Size)
MySQL.Query("insert into MyImageTable values('Label 1', '" + _
ProcessedBuffer$ + "')")
''-- Using LOADBLOB instead
ProcessedBuffer$ = MySQL.LoadBlob("test.bmp")
MySQL.Query("insert into MyImageTable values('Label 1', '" + _
ProcessedBuffer$ + "')")
Using LOADBLOB eliminates the use of MySQL.EscapeString
since it is just implemented internally for you. It simply takes a single argument
(the name of the file to include), and returns the processed string.
To retrieve a blob from the database and save it to a file, you can try this:
WHILE MySQL.FetchRow
MySQL.FieldSeek(0)
'' MySQL.FetchLengths
FOR I = 0 TO MySQL.NumField-1
MySQL.FetchField
IF MySQL.Field.Type = FIELD_TYPE_BLOB THEN
''-- Save data to file
MySQL.SaveBlob(I, "temp.bmp")
ELSE
Buffer$ = MySQL.Row(I)
END IF
NEXT
WEND
In this instance we can ignore using MySQL.FetchLengths
since it is, again, implemented internally for you. MySQL.SaveBlob takes two
arguments. The first is the index (or field number if you will), and the second argument is the
name of the file to save to. There are no return values associated with this function.
14.7 In conclusion
I = MySQL.SelectDB("mysql")
Under Rapid-Q, this function returns 0 if database cannot be opened, and a non-zero number
otherwise. However, if you've used LIBMYSQL.DLL before, this function (called
mysql_select_db) returns 0 on success and -1 on failure.
So basically, just reverse your way of thinking when using Rapid-Q.
| Prev Chapter | Contents | Next Chapter |
