Rapid-Q Documentation by William Yu (c)1999 | Chapter 14

# 14. Programming in MySQL

  This chapter is a quick overview of what you can do with the QMYSQL component. It does not teach you about SQL or how to construct proper queries. It is assumed you have some prior knowledge of SQL, but not necessarily with MySQL.
  14.1 Quick Introduction
  MySQL is basically free for Unix and OS/2 platforms, but Rapid-Q only supports MySQL for Linux and Windows since my resources are limited to such machines. Under Windows, if you like MySQL, then you are required to pay a license fee after a 30 day trial period. Not to be confused with paying me of course, but if you want, I'll oblige. Why did I choose MySQL over other database servers? The reason is fairly simple, yet deserves some explanation anyway. MySQL is free, mostly, and it's multiplatform, which fits into Rapid-Q quite nicely. It's also easier for me to test, and quite simple to implement under Rapid-Q. It's fairly lightweight, fast, robust, and did I mention free? Perhaps in the future, other database servers will be supported, ie. through MyODBC or Window's ODBC.
  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
  After installing the MySQL server, test it to make sure it works (read their nice documentation and help files), you'll also need to copy LIBMYSQL.DLL (under Windows) and libmysqlclient.* (under Linux) to your library path directory. C:\WINDOWS\SYSTEM under Windows, and maybe /usr/lib under Linux (you can also just add a library path to the file instead of moving it). These library files are required by Rapid-Q, they should have come with your MySQL distribution, probably in C:\MYSQL\LIB and /usr/local/lib/mysql depending on your installation. If you already have MySQL installed, which is possible, make sure it's up to date, or if you have an SQL Administrator, check with the that person.
  14.2 Connecting to MySQL
  The introduction assumed that you wanted to install a MySQL server on your machine, but you can access a MySQL database from anywhere, as long as you have access of course. If you don't want to install a MySQL server (why wouldn't you?) then make sure you have the proper client libraries, ie. LIBMYSQL.DLL. To begin with, I'll assume you have the MySQL server installed. To connect to your local MySQL server is simple enough:

                  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
  Write your queries like you would normally, for example, if you want to select everything from a certain table, say you have a table named user, then you can do this:

                  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:
  Since the result is stored sequentially, you have to fetch each row one at a time (not necessarily true, but assume we have no other choice). To do this is quite simple:

                  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
  As you may notice, it would be nice if the results could just be preloaded into a string grid instead of having to fetch everything and doing it manually. Unfortunately I haven't got any motivation to do this right now, but you can use this simple code until I get off my butt and implement it internally:

                  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
  Blobs are just binary data that require special processing. Since blobs can contain NULL characters and other special characters that affect MySQL and Rapid-Q, you'll need to do some special processing with your binary data. Please note that blob sizes vary, depending on the field type. A BLOB, by default can hold up to 65535 bytes (a LONGBLOB should be able to handle 2^32-1 bytes). If you want to change the buffer size, you'll have to read up on it (max_allowed_packet). To store binary data, like images, you'll have to preprocess certain characters before you can send it over to your MySQL database.

                  $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
  In the previous section, we uncovered the natural way of storing and retrieving blobs (binary data). However, there is an easier and faster way of doing the samething.

                  ''-- 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
  Using MySQL can save you a lot of time and effort, since you don't need to design your own database file structure, and searching is quite fast. Not to mention that MySQL is multiplatform, so you can use MySQL with Windows and Linux. If you already have prior knowledge with programming in MySQL, you'll probably notice some differences. For example,

                  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