PureBasic Survival Guide a tutorial for using purebasic for windows 4.40b3 Part 0 - TOC Part I - General Part II - Converts Part III - Primer I Part IV - Primer II Part V - Advanced Part VI - 2D Graphics I Part VII - 2D Graphics II Part X - Assembly Part XI - Debugger Part XII - VirtualBox Part XIII - Databases Part XIV - Networking Part XV - Regular Expressions Part XVI - Application Data Part XXVII - Irregular Expressions Part XXIX - Projects

Part XIII - Databases v1.12 10.04.2011 13.1 As I go... again. 13.2 Getting started: talk to me. 13.3 Reading and writing data. 13.4 Data types. 13.5 The Committed Programmer. 13.6 Who calls me a Blob? 13.7 MySQL. 13.8 Installation Server side. 13.9 Installation Client side. 13.10 SQLite to MySQL. 13.11 PostgreSQL. 13.12 Installation Server side. 13.13 Installation Client side.

13.1 As I go... again.
 

Welcome to my totally unstructured approach to databases, and may I end up where I want to be. (If I only knew where that would be :-)) I'm going to talk about SQLite and MySQL.
 

SQL.

I am NOT going to try to explain 'SQL' itself. Well, okay, perhaps a little but just as much as necessary to run and understand the examples. There are enough resources on the web to learn SQL. Of course, I could not entirely control myself :-) so here are a few topics...

• Creating tables
• Adding columns
• Deleting tables
• Multiple instructions
• The semicolon
• Writing data
• Selecting fields
• Reading data
• Sorting results
• Number of columns
• Number of rows
• Changing data
• Deleting data
• Commit

• W3Schools SQL Tutorial
• SQLite SQL functions

Small, compact, fast, and integrated into PureBasic, this is the database of choice for smaller applications, that reside on a single machine. SQLite is not intended to be used with large, corporate style databases with many users and transactions simultaneously.

• no need for external drivers or files
• fast, compact, stable
• doesn't use ODBC
• multi user on NTFS on a single machine
• NOT for multiuser in a network
• no need to install aditional drivers

• http://www.sqlite.org
• PureBasic and SQLite

MySQL is an open source database that IS suitable for larger, multi user applications. I've added a section below dedicated to installing and running MySQL. The approach is pretty much the same, so with very little work all examples should work under MySQL. MySQL on PureBasic uses ODBC...

• suitable for large databases, stable
• needs installation on client as well as server side
• needs ODBC due to license issues
• suitable for large multi-user applications on LAN's and WAN's

• http://www.mysql.com
• PureBasic and MySQL

MySQL is an alternative for MySQL. It's open source and is suitable for larger, multi user applications. PureBasic comes with embedded drivers so you can use it with or without ODBC drivers. More about installing and running PostgreSQL.

• suitable for large databases, stable
• needs installation on server side, optional installation on client side
• can use either embedded driver or ODBC
• suitable for large multi-user applications on LAN's and WAN's

• http://www.postgresql.com
• PureBasic and PostgreSQL

What is ODBC? http://www.tech-faq.com/odbc.shtml

ODBC is an interface to access different sorts of databases, such as MS Access, dBase, DB2, AQL etcetera. ODBC needs drivers, and MicroSoft has included some of them with every copy of Windows, but for MySQL you'll need to install one (which is easy, and free).

SQLite Database Browser.

There's a handy tool for checking our results when messing with SQLite databases, the SQLite Database Browser. I'm not sure if it's actively maintained (probably not :-)) but it seems to work well for basic things. Download the .zip file and use the program inside to investigate your SQLite database files. 

You can test your SQL instructions in this program using the third tab called 'Execute SQL'. Enter the instructions and then hit the 'Execute Query' button.

• this program seems to have some trouble using shared access, you may have to exit and restart it to give PureBasic access again to an open database
• it also appears to have some troubles with multiple instructions at once (grouped together using the semicolon)
• it is using an older version of SQLite, and not all newer features may have been implemented!! (it seems BEGIN / COMMIT isn't working)
• you may opt for the MySQL Query Browser in combination with MySQL, or the build-in PostgreSQL query browser.

AKA talk to the engine...

PureBasic supports different flavours of databases. To include all routines needed for accessing SQLite databases we add to our code:

UseSQLiteDatabase()

UseSQLiteDatabase()
;
CreateFile(1,"database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"Database.sqlite","","",#PB_Database_SQLite)
;
; fancy code goes here
;
CloseDatabase(1)

Talking to SQL.

SQL is more or less a programming language, a little like PureBasic. We 'throw' instructions at the SQL engine, which in turn takes certain actions. PureBasic has a very limited vocubalary related to databases, as it doesn't need much... It's the SQL engine that does the real work, be it SQLite or MySQL or whatever other beast hides behind an ODBC interface.

These are the core commands we need to talk to the SQL engine:

DatabaseError().

DatabaseError() returns the last error the database ran into. With this we can get a textual response from the database engine which further specifies the problem we may have ran into.

DatabaseUpdate()

DatabaseUpdate() sends a command to the database engine, but doesn't expect a reply. We use this to create tables, add colums, set fields etc. If this command returns a zero, then something went wrong (perhaps the SQL statement was wrong, or the database had problems). In those cases you can get more information about the error using DatabaseError().

DatabaseQuery()

DatabaseQuery() asks the database engine a question, and (may) trigger a number of replies. Each reply is retrieved with NextDatabaseRow(). DatabaseQuery() itself will return zero if it encountered an error.

When you are done with your query you have to call FinishDatabaseQuery() to avoid memory leaks.

SQL: Creating tables.

In a database we can have multiple tables. Each table consists of rows and colums, like this:
 

 

TABLE: cars
COLUMN: firstname	COLUMN: lastname	COLUMN: brand	COLUMN: colour
jan peter	balkenende	volvo	black
michael	schumacher	ferrari	red
johnny	fireman	mack	red
mick	jagger	cadillac	black

Each table has a name, so we can store multiple names in a database. In this case 'cars'. Let's create the table first. The SQLite command for creating a table with one column 'firstname' would be:

CREATE TABLE cars ( firstname TEXT )

DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT )" )

CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )

We could add columns to an existing table using this:

CREATE TABLE cars ( firstname TEXT ) ;
ALTER TABLE cars ADD lastname TEXT ;
ALTER TABLE cars ADD brand TEXT ;
ALTER TABLE cars ADD colour TEXT ;

Multiple instructions.

You could send the above to SQLite using multiple DatabaseUpdate() commands:

UseSQLiteDatabase()
;
CreateFile(1,"c:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
;
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT ) ;" )
DatabaseUpdate(1, "ALTER TABLE cars ADD lastname TEXT ;" )
DatabaseUpdate(1, "ALTER TABLE cars ADD brand TEXT ;" )
DatabaseUpdate(1, "ALTER TABLE cars ADD colour TEXT ;" )
;
CloseDatabase(1)

UseSQLiteDatabase()
;
CreateFile(1,"c:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
;
sql.s = "CREATE TABLE cars ( firstname TEXT ) ;"
sql = sql + "ALTER TABLE cars ADD lastname TEXT ;"
sql = sql + "ALTER TABLE cars ADD brand TEXT ;"
sql = sql + "ALTER TABLE cars ADD colour TEXT"
;
DatabaseUpdate(1, sql)
;
CloseDatabase(1)

UseSQLiteDatabase()
;
CreateFile(1,"c:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
;
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )" )
;
CloseDatabase(1)

Of course, we can also delete tables using:

DROP TABLE cars

SQL experts please correct me here. It looks like the semicolon is an optional component, which can be inserted whenever there could be doubt about the interpretation of a piece of SQL. Most of the time a CRLF at the end of each line seems to do the trick. Expert advise wanted here!

Fair warning: when spreading statements over multiple lines you may need or want to use semicolons... The results of a wrongly composed SQL statement can be disastrous...

13.3 Reading and writing data.
 

Can't read from an empty table, so let's start with writing. (I should have called this 'writing and reading data', I guess...)

Writing.

We've build our table using CREATE TABLE and now we need to put in some data. In SQLite we use INSERT and that would look something like this:

CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;
INSERT INTO cars VALUES ( "jan peter" , "balkenende" , "volvo" , "black" ) ;
INSERT INTO cars VALUES ( "michael" , "schumacher" , "ferrari" , "red" ) ;
INSERT INTO cars VALUES ( "johnny", "fireman" , "mack" , "red" ) ;
INSERT INTO cars VALUES ( "mick" , "jagger" , "cadillac" , "black" ) 

UseSQLiteDatabase()
;
; create a file if there isn't one yet
;
If FileSize("c:\database.sqlite") < 0
  CreateFile(1,"c:\database.sqlite")
  CloseFile(1)
EndIf
;
; open the database file
;
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
;
; delete our test table if it still existed (i'm ignoring errors here)
;
DatabaseUpdate(1, "DROP TABLE cars" )
;
; and (re)create it
;
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )" )
;
; now add some data
;
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'jan peter' , 'balkenende' , 'volvo' , 'black' )" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'michael' , 'schumacher' , 'ferrari' , 'red' )" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'johnny' , 'fireman' , 'mack' , 'red' )" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'mick' , 'jagger' , 'cadillac' , 'black' )" )
;
CloseDatabase(1)

INSERT INTO cars ( firstname , lastname , brand , colour ) VALUES ( "mick" , "jagger" , "cadillac" , "black" ) 

Reading and sorting.

In SQL we can read from a table using the SELECT keyword. If we would like to get all rows where the colour of the car is black, we'd use:

SELECT * FROM cars WHERE cars.colour = "black"

SELECT * FROM cars WHERE cars.colour = "black" AND cars.brand = "volvo"

SELECT * FROM cars WHERE cars.colour = "black"  UNION SELECT * FROM cars WHERE cars.brand = "mack"

To return the results in a specific order, we'd add ORDER BY:

SELECT * FROM cars WHERE cars.colour = "black" ORDER BY cars.brand

; survial guide 13_3_110 reading and sorting
; pb 4.40b1
;
UseSQLiteDatabase()
;
; enumeration helps us to find the right colums back
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
; open the database file
;
OpenDatabase(1, "c:\database.sqlite","","",#PB_Database_SQLite )
;
; select a number of rows (all cars that are black, then sort by brand)
;
DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand" )
;
; retrieve the results
;
While NextDatabaseRow(1) > 0
  x.s = GetDatabaseString(1,#cars_firstname) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_lastname) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_brand) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_colour)
  Debug x.s
Wend
;
FinishDatabaseQuery(1)
CloseDatabase(1)

PureBasic has a few commands that may make life easier, though not everything exists in a basic keyword. Thus, sometimes, you have to use SQL'ish approaches. Here are two examples:

• how many rows are returned upon our query?
• how many columns does our selection feature?

UseSQLiteDatabase()
OpenDatabase(1, "c:\database.sqlite","","",#PB_Database_SQLite )
DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand" )
;
; find the number of columns in the result of the query
;
Debug DatabaseColumns(1)
FinishDatabaseQuery(1)
CloseDatabase(1)

Want to know in advance how many rows a query will return? Use the SQL COUNT function:

SELECT COUNT(*) FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand 

UseSQLiteDatabase()
OpenDatabase(1, "c:\database.sqlite","","",#PB_Database_SQLite )
;
; find the number of rows the query would return
;
DatabaseQuery(1, "SELECT COUNT(*) FROM cars WHERE cars.colour = 'black'" )
NextDatabaseRow(1)
Debug GetDatabaseString(1,0)
FinishDatabaseQuery(1)
CloseDatabase(1)

Changing and deleting.

You can change any field using the following SQL command UPDATE. The following line changes each and every colour of the cars belonging to the person with the last name 'balkenende':

UPDATE cars SET colour = 'red' WHERE lastname = 'balkenende' ;

DELETE FROM cars WHERE colour = 'red' ;

DELETE FROM cars
WHERE colour = 'red' ;

;
UseSQLiteDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
; create database
;
CreateFile(1,"c:\database.sqlite")
CloseFile(1)
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
DatabaseUpdate(1, "DROP TABLE cars" )
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'jan peter' , 'balkenende' , 'volvo' , 'black' ) ;" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'michael' , 'schumacher' , 'ferrari' , 'red' ) ;" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'johnny' , 'fireman' , 'mack' , 'red' ) ;" )
DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'mick' , 'jagger' , 'cadillac' , 'black' ) ;" )
;
; change one car to blue, one to black
;
DatabaseUpdate(1, "UPDATE cars SET colour = 'red'"+#CRLF$+"WHERE lastname = 'balkenende' ;" )
DatabaseUpdate(1, "UPDATE cars SET colour = 'black'"+#CRLF$+"WHERE lastname = 'schumacher' ;" )
;
; remove all cars belonging to schumacher 
;
DatabaseUpdate(1, "DELETE FROM cars WHERE lastname = 'schumacher' ;" )
;
; display all black cars
;
DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand ;" )
While NextDatabaseRow(1) > 0
  x.s = GetDatabaseString(1,#cars_firstname) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_lastname) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_brand) + " , "
  x.s = x.s + GetDatabaseString(1,#cars_colour)
  Debug x.s
Wend
FinishDatabaseQuery(1)
CloseDatabase(1)

SQLite types.

Regular (if there is regular...) SQL supports different data types. There are however many different implementations. SQLite keeps it relative easy: you can read and write in any type, regardless of the defined column type. This is NOT the case with other SQL implementations!

• in SQLite, any type of data may be stored in any column
• SQLite tries to autoformat data according to the defined column type

UseSQLiteDatabase()
If CreateFile(1,"c:\database.sqlite")
  CloseFile(1)
EndIf
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
DatabaseUpdate(1, "CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB)" )
;
; add some text strings
;
DatabaseUpdate(1, "INSERT INTO types VALUES ( 'text1' , 'text2' , 'text3' , 'text4' ) ;" )
;
; now add some integer numbers
;
DatabaseUpdate(1, "INSERT INTO types VALUES ( 5 , 6 , 7 , 8 ) ;" )
CloseDatabase(1)

Whilst SQLite converts the datatypes for us on the fly, we still need to store them into PureBasic variables. For your convenience we got a few different models on offer...

For reading we can use GetDatabaseLong() and its brethern, as listed below:

 

variable type	read	write
.b byte	a.b = GetDatabaseLong()	x.s = Str(a.b)
.w word	b.w = GetDatabaseLong()	x.s = Str(b.w)
.l long	c.l = GetDatabaseLong()	x.s = Str(c.l)
.i integer	d.i = GetDatabaseQuad()	x.s = Str(d.i)
.q quad	e.q = GetDatabaseQuad()	x.s = Str(e.q)
.f float	f.f = GetDatabaseFloat()	x.s = StrF(f.f)
.d double	g.d = GetDatabaseDouble()	x.s = StrD(g.d)
.s string	s.s = GetDatabaseString()	x.s = s.s

;
; create file
;
UseSQLiteDatabase()
If CreateFile(1,"c:\database.sqlite")
  CloseFile(1)
EndIf
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
;
; create table
;
DatabaseUpdate(1, "CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB) ;" )
;
; a regular insert
;
DatabaseUpdate(1, "INSERT INTO types VALUES ( 5 , 6 , 7 , 8 ) ;" )
;
; an insert storing different variable types
;
a.b = 1
b.w = 2
f.f = 3
g.d = 4
DatabaseUpdate(1, "INSERT INTO types VALUES ( " +Str(a.b)+ " , " +Str(b.w)+ " , " +StrF(f.f)+ " , " +StrD(g.d)+ " ) ;" )
;
CloseDatabase(1)

To be committed, or not to be committed, that's the question.

And indeed it is.

In SQLite, data is immediately stored in the database ('autocommit') as soon as a query or update is send. That is, if we don't tell SQLite to wait... It is possible to group a number of instructions together, and have them executed at once. If, for some reason, we want to cancel our transaction, we can do a rollback before we commit ourselves...

UseSQLiteDatabase()
If CreateFile(1,"c:\database.sqlite")
  CloseFile(1)
EndIf
OpenDatabase(1,"c:\database.sqlite","","",#PB_Database_SQLite)
DatabaseUpdate(1, "CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB) ;" )
DatabaseUpdate(1, "BEGIN ;")
DatabaseUpdate(1, "INSERT INTO types VALUES ( 1 , 2 , 3 , 4 ) ;" )
DatabaseUpdate(1, "COMMIT ;")
DatabaseUpdate(1, "BEGIN ;")
DatabaseUpdate(1, "INSERT INTO types VALUES ( 5 , 6 , 7 , 8 ) ;" )
DatabaseUpdate(1, "ROLLBACK ;" )
CloseDatabase(1)

Again, the Survival Guide is not about teaching you SQL or even SQLite :-) so have a good look at the SQLite documentation for the different transaction types.

The extracted SQLite instructions (doesn't work when entered on the 'Execute SQL' panel inside the SQLite Database Browser, perhaps because that one uses an older version of SQLite?)...

CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB) ;
BEGIN ;
  INSERT INTO types VALUES ( 1 , 2 , 3 , 4 ) ;
COMMIT ;
BEGIN ;
  INSERT INTO types VALUES ( 5 , 6 , 7 , 8 ) ;
ROLLBACK ;

I never would. Never. At least not in your face :-)

A blob is a mechanism to store binary information (images etc.) in your database. Until 4.40b1 we had to use alternative tricks, but now we have native blob's... Here are the options:

1. store the images elsewhere and link to them
2. store the images as Base64
3. store the images as Yenc
4. do the real thing with Blob's

It may sound funny, but sometimes the right way to store images inside a database is not to store them inside the database... euh... argh! :-)

Seriuosly, imagine you have a relative small database and a large collection of AutoCad drawings, stored on some drive. Those drawings are updated using AutoCad, so AutoCad needs access to them. It would be a pain in the ... if you would have to export the drawings each and every time from the database, store them locally, edit them, then import them back into the database.

In those cases it may be a very valid option to simply create a text field with a path to the document.

One note though: it may be wise to think in advance how to deal with (absolute) paths, and what must be done if files are moved from one drive to another... Manual renaming may not be such a 'comfortable' option...

Base64.

So, how can we store binary informaton then? We could store them in a string (though we have to make sure the resulting string complies to the rules, ie. no CHR(0) NULL characters are allowed). One way to do this is use Base64 encoding, this is an old encoding scheme used to transport binary data over media that do not support binary data. The example from the help file shows how to use this with a string as an example. Of course nothing would stop you to encode an image, just point towards the place in memory where the image is located, and pass on the length... See the help file.

(By the way, you need to switch off 'Enable ASM Inline Support' in 'Compiler Options' for the sample below, otherwise the PureBasic compiler thinks test, dec and enc are assembly instructions :-))

test.s = "This is a test string!" 
dec.s = Space(1024)
enc.s = Space(1024) 
; 
Debug Base64Encoder(@test, StringByteLength(test), @enc, 1024)
Debug enc 
; 
Debug Base64Decoder(@enc, StringByteLength(enc), @dec, 1024)
Debug dec

Yenc.

Yenc is the modern day version of Base64. It's immense popular due to its use in newsgroups where it is used to spread binaries. PureBasic does not have a native Yenc onboard but you can code one.

Ah. Still working on this... more to come once it's done...

Blob.

4.40b1 brought us the Blob. (Actually, I think McDonalds was first, but I might be wrong :-)) There's little explanation in the not-yet-ready docs of the beta, but thanks to my exceptional sleuthing talents (and the answers in the forum :-)) here's what's going on:

UseSQLiteDatabase()
OpenDatabase(0,":memory:","","")
DatabaseUpdate(0, "CREATE TABLE rubbish ( blob1_data BLOB , nouse INTEGER , blob2_data BLOB ) ; ")
;
a.l = 0
c.l = 2
SetDatabaseBlob(0,0,@a,4)
SetDatabaseBlob(0,1,@c,4)
DatabaseUpdate(0, "INSERT INTO rubbish ( blob1_data , nouse , blob2_data ) VALUES ( ? , '1' , ? ) ;")
;
a = 0
b = 0
c = 0
DatabaseQuery(0,"SELECT * FROM rubbish")
NextDatabaseRow(0)
GetDatabaseBlob(0,0,@a,4)
b = GetDatabaseLong(0,1)
GetDatabaseBlob(0,2,@c,4)
Debug a
Debug b
Debug c
;
FinishDatabaseQuery(0)
CloseDatabase(0)

(No, it's not a McDonalds customer :-) ...and for the lawyers amongst us: this is artistic freedom, not a specific snarl at a specific company. Personally, IMHO etc. I think the hamburgers are horrible, but compared to the cardboard pieces the great belgian chain Quick serves they are haute cuisine...B ut that's personal opinion. Artistic freedom. Disclaimers, right? Sigh. Let's go on.)

• A blob is a set of raw, binary data. You can store anything in it, for example images or sounds.
• It may not always be the best option to store images inside your database. Design carefully.

SetDatabaseBlob().

With the statement SetDatabaseBlob() we prepare some data to be 'blobbed'. If you look at the DatabaseUpdate() statement in the sample code above, you will find two questionmarks. We're using these questionmarks as placeholders, and these questionmarks / placeholders will be replaced with with our blob data upon execution.

In the example above I used two longs (each 4 bytes long). The position in memory where a.l resides is my first blob. The position in memory where b.l resides is my second blob. The SECOND parameter of SetDatabaseBlob() tells PureBasic which questionmark / placeholder will be replaced with the blob. These placeholders are numbered from zero upwards. Zoom in on these three lines:

SetDatabaseBlob(0,0,@a,4)
SetDatabaseBlob(0,1,@c,4)
DatabaseUpdate(0, "INSERT INTO rubbish ( blob1_data , nouse , blob2_data ) VALUES ( ? , '1' , ? ) ;")

• Column 0 aka blob1_data) - first placeholder / questionmark number 0 (remember: start counting at zero) - it is replaced by the data we prepared with SetDatabaseBlob() in this case the memory contents at @a
• Column 1 aka nouse - we store '1'.
• Column 2 aka blob2_data - second placeholder / questionmark number 1 - it is replaced by the data we prepared, in this case the memory contents at @b.

GetDatabaseBlob().

Zoom in on the sample code above, and look for these three lines:

GetDatabaseBlob(0,0,@a,4)
b = GetDatabaseLong(0,1)
GetDatabaseBlob(0,2,@c,4)

The second parameter of GetDatabaseBlob() speficies the table column number. This is NOT the same as the 'index' used by SetDatabaseBlob().

DatabaseColumnSize().

The command DatabaseColumnSize() returns the size of the specified column of the selected row. This means you can insert a blob and later retrieve first the blob size, allocate memory, then retrieve the blob data using the following approach:

buffer_l.i = DatabaseColumnSize( 0 , 0 )
buffer_p.i = AllocateMemory( buffer_l )
GetDatabaseBlob( 0 , 0 , buffer_p , buffer_l )

MySQL is one of the most well known SQL server packages. Note that there is dispute about the usability / legality in a commercial environment, due to some license restrictions. It appears to me that you can use it, but that you will have to use ODBC to avoid license issues. You may consider PostgreSQL as an alternative.

Again, this is not a tutorial on SQL, MySQL, or ODBC. I'm only interested in setting things up so I can use PureBasic to mess around with them :-) Still it is worth it to have another look at the issue in case we'd become too succesful :-)
 

License.

Some notes on the license issue... I'm not a lawyer (otherwise I would be working for a patent troll, be indencently rich, and not be writing this :-)) so take the following at face value... In other words, my humble opinion, your mileage may vary, at your own risk, disclaimers etc. etc. etc...

Unless you BUY a license of MySQL you need to adhere to the GPL. As far as I can tell nothing is going to stop you from using MySQL in a non-commercial or commercial project, as long as you adhere to the license. The key is the term 'derivative work'.

1. Embedding GPL'ed SOURCE in your code would clearly force you to use the GPL license for your whole product.

2. STATIC LINKING of a GPL'ed library makes the GPL'ed code and resulting program part of your program and thus forces you to use the GPL license.

3. DYNAMIC LINKING is a bit more tricky. Strictly spoken the functionality of the DLL becomes part of your program, but the code itself does not. The jury is still out on this one. If your program would use functionality exposed by an under GPL developed and distributed DLL, which DLL was developed and distributed for such a purpose, and which DLL is not distributed as part of your program, then you're probably not obliged to go GPL. However, it's inside the grey zone.

4. If the whole GPL'ed program is an integral element of your solution, and you would install it (perhaps automatically) as part of your (commercial) solution, you'd be entering the grey zone as well. Most definitely if you would hide such an installation from the user (as part of a commercial package).

The above doesn't stop you from using MySQL. Using functionality provided / exposed by MySQL ODBC connector software, or communicating with the MySQL server is not considered derivative work so should be fine. You (probably) cannot automagically install MySQL and / or the MySQL ODBC connector, and you (definitely) cannot embed a MySQL library in your program. But nothing is going to stop you from installing or using a MySQL platform as part of a commercial and / or non-GPL'ed solution, it just cannot be part of your program.

Too risky? Go PostgreSQL :-)

13.8 Installation server side.
 

You might want to run this on a dedicated machine, server, or virtual machine. I used VirtualBox to setup such a 'dedicated' MySQL server on a virtual machine... Read more about using VirtualBox here. MySQL needs one side to be a server, and the other to be a client, but nothing is stopping you to install server and client software on the same machine. (Frankly, if you're just going to use MySQL as a simple local database, there's very little reason to use a dedicated machine, but then again why are you not using SQLite then?)

There are complete packages around including tools, configuration etc. such as Xampp. They may make your life easier, especially if you're interested in building applications for the web. I may revisit Xampp once I find the courage to use PureBasic for web applications... For now, it's though enough as it is :-)
 

Machine and Windows.

Create a VM if you're going to use VirtualBox or something similar. You may consider assigning a fixed IP to your server. I changed the name of my dedicated MySQL server to 'sqlserver'.

MySQL essentials 5.1.

1. Download the MySQL server essentials package and install it. I used 5.1 and ticked the option 'custom' as I like to think of myself as an expert (which, obviously, I am not :-))... Note: when reinstalling MySQL it couldn't start the service, no matter what I tried. In the end, I cloned a new VM and reinstalled... (I should have used a snapshot in VirtualBox but I forgot to create one :-)) I think I messed up a password which wasn't removed during de-installation, but that's the good thing about imaging and / or virtual machines... restoring is a lot easier.

2. The default port is 3306. Tick the box 'add firewall exception' if you're using Windows firewall. Note: this may not suffice, and you might have to allow traffic manually! When in doubt, check functionality by shutting down the firewall temporary. Oh, and if you are using the host name on the client to find the server, enable 'file and printer sharing' on that server otherwise the client won't find the host!

3. Tick 'best support for multilingualism'. Install as a Windows service and have it launched automatically. Also tick the option 'include bin directory in path'. Next etc....

4. Modify security settings and enter a new root password. For simplicity I used the password 'root' here... not very safe, but this is a test environment, not a production server. Next etc....

5. Hit the 'execute' button and hope it doesn't crash during installation. (It did here twice...) Next etc....

Tada. You've now got your own MySQL server up and running as a service on your virtual machine. Congratulations.

MySQL GUI Tools 5.0.

These tools make your life a little easier, unless you're a die-hard that likes the command prompt....

1. Download and install the GUI tools package and install it. I used 5.0 and did install all except the MySQL migration toolkit. Next, install, finish etc, you know the drill...

2. Under Windows Start / Programs you'll find a new folder MySql. Start the MySQL Tray Monitor. Click on it with the RMB.

3. Switch ON the option Monitor Options / Launch Monitor After Login.

4. There's another tool you'll find there called MySQL Administrator. You may want to drag it onto the desktop for quick access.

You can now test your setup...

Note that you can install the MySQL GUI Tools on your clients, if you want to. Especially the MySQL Query Browser may come in handy if you want to experiment with the SQL language itself.
 

Testing...

Start the MySQL administrator. Use the following settings:

• server host: localhost
• port: 3306
• username: root
• password: root

Adding a user.

Start the MySQL Administrator on your new SQL server and log in as root. Select Use Administration / Add New User. I added a new user called 'user' with password 'user'... (Yes, I'm a very creative person.)

Creating a database.

1. Start the MySQL Query Browser. This one came with the MySQL GUI Tools...

• server host: localhost
• port: 3306
• username: root
• password: root
• default schema: test

CREATE DATABASE PUREBASIC
SHOW DATABASES

In all future calls we might use the MySQL Query Browser with 'default schema' set to purebasic, we just needed that build-in 'test' database this one time to start up the query browser and create our own first database. The program is supposed to let us connect to the MySQL service without a database given, but I didn't get through without one.

13.9 Installation client side.
 

There is more than one way to talk to the MySQL server, but from within PureBasic the easiest one is using ODBC.
 

MySQL ODBC Connecter.

1. Download the MySQL ODBC connector for Windows and run it.

2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine...

3. Add MySQL ODBC driver.

• data source name: mysql
• server: sqlserver (this is the name I gave my dedicated MySQL server)
• posrt:3306
• user: root
• password: root
• database: purebasic

4. Hit the 'Test' button. If the ODBC connector cannot connect to our MySQL Server then most likely a firewall is causing the problem, either on the client or on the server. Try it with the firewalls turned off. If that works, you might test with the IP address of the server instead of its name, ortry

5. Run the program below. It should create a little table and produce the same results as our SQLite version...

; survival guide 13_9_110 mysql
; pb 4.40b1
;
UseODBCDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
If OpenDatabase(1,"mysql","root","root",#PB_Database_ODBC) = 0
  Debug "cannot open"
Else
  ;
  DatabaseUpdate(1, "DROP TABLE cars" )
  DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'jan peter' , 'balkenende' , 'volvo' , 'black' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'michael' , 'schumacher' , 'ferrari' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'johnny' , 'fireman' , 'mack' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'mick' , 'jagger' , 'cadillac' , 'black' ) ;" )
  ;
  DatabaseUpdate(1, "UPDATE cars SET colour = 'red'"+#CRLF$+"WHERE lastname = 'balkenende' ;" )
  DatabaseUpdate(1, "UPDATE cars SET colour = 'black'"+#CRLF$+"WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseUpdate(1, "DELETE FROM cars WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand ;" )
  While NextDatabaseRow(1) > 0
    x.s = GetDatabaseString(1,#cars_firstname) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_lastname) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_brand) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_colour)
    Debug x.s
  Wend
  FinishDatabaseQuery(1)
  CloseDatabase(1)
  ;
EndIf

13.10 SQLite to MySQL.
 

From a PureBasic and SQL point of view there is little difference between MySQL and SQLite. It's important to keep in mind that MySQL is more strict on data types.

To turn the SQLite examples above into MySQL, do the following:

1. Install the neccessary software on server and client.
2. Create a database and users on the server.
3. Speficy the right database on the client (ODBC configuration).
4. Remove the creation of a local file (not needed for MySQL).
5. Replace UseSQLiteDatabase() with UseODBCDatabase().
6. Change the parameters for the OpenDatabase() command.

In SQLite with a single user application you might skip BEGIN / COMMIT but in multi user applications in a network I would strongly advise to use them.

13.11 PostgreSQL.
 

PostgreSQL is 'the other' open source database, but it doesn't suffer from GPL issues. This means that PostgreSQL drivers / libraries can be linked with / embedded in other programs. Which is exactly what PureBasic did :-)

You will always need to install PostgreSQL on the server side. You may chose to use ODBC on the client side, or use the onboard drivers of PureBasic.

I found the installation and configuration of MySQL marginally easer, and the MySQL GUI tools are nice, especially the MySQL Query Browser. However PostgreSQL contains a similar tool, and if you look around on the Internet you'll find some alternatives, I'm sure.

13.12 Installation server side.
 

PostgreSQL needs one side to be a server, and the other to be a client, but nothing is stopping you to install server and client software on the same machine. (Frankly, if you're just going to use PostgreSQL as a simple local database, there's very little reason to use a dedicated machine, but then again why are you not using SQLite then?)
 

Machine and Windows.

Create a VM if you're going to use VirtualBox or something similar. You may consider assigning a fixed IP to your server. I created a new VM and changed its name to 'sqlserver'. In fact, I installed MySQL and ProgreSQL on the same VM without any problems.
 

PostgreSQL 8.4.1.

1. Download PostgreSQL 8.4.1. Use the regular package and install it.

2. The default port is 5432. Choose eventual passwords wisely. (I did not, so 'postgres' it is, everywhere :-)) Install.

3. Look for a file called 'pg_hba.conf'. It's in the PostgreSQL folders somewhere. If your local network runs in the 192.168.0.x range, then you will have to add the following line:

host all all 192.168.0.0 255.255.255.0 md5

5. You may want to put the link 'Postgress pgAdmin III' on your desktop for easy access.

6. Start up pgAdmin. Connect to (doubleclick) the PostgreSQL server (localhost port 5432 user postgres password postgres).

7. RMB on 'databases' and add a new database called 'purebasic'.
 

The PostgreSQL query browser.

PostgreSQL also includes a query browser. To use it do the following:

1. Start up pgAdmin.

2. Select 'purebasic' under 'Databases'.

3. EIther select Tools / Query tool, or hit [Control] + [E]. Note that some commands that work under MySQL don't work on PostgreSQL.

13.13 Installation client side.
 

None.

:-)

Seriously, you could do an ODBC client side install, but PureBasic already contains the PostgreSQL library, so there's no real need.

Use the following code to check if your PostgreSQL setup is working:

; survival guide 13_13_100 postgresql
; pb 4.40b3
;
UsePostgreSQLDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
If OpenDatabase(1,"host=sqlserver port=5432 dbname=purebasic","postgres","postgres") = 0
  Debug "cannot open"
Else
  ;
  DatabaseUpdate(1, "DROP TABLE cars" )
  DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'jan peter' , 'balkenende' , 'volvo' , 'black' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'michael' , 'schumacher' , 'ferrari' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'johnny' , 'fireman' , 'mack' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'mick' , 'jagger' , 'cadillac' , 'black' ) ;" )
  ;
  DatabaseUpdate(1, "UPDATE cars SET colour = 'red'"+#CRLF$+"WHERE lastname = 'balkenende' ;" )
  DatabaseUpdate(1, "UPDATE cars SET colour = 'black'"+#CRLF$+"WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseUpdate(1, "DELETE FROM cars WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand ;" )
  While NextDatabaseRow(1) > 0
    x.s = GetDatabaseString(1,#cars_firstname) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_lastname) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_brand) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_colour)
    Debug x.s
  Wend
  FinishDatabaseQuery(1)
  CloseDatabase(1)
  ;
EndIf

Of course, I couldn't help myself and had to try :-)

1. Get the ODBC connector software, the one I used I found on the PostgreSQL website, under file browser / odbc / versions / msi / psqlodbc_08_04_0100.zip.

2. Unzip and install it.

2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine...

4. Add PostgreSQL ODBC driver.

• data source: postgresql
• server: sqlserver
• port: 5432
• user: postgres
• password: postgres
• database: purebasic

6. Try the following program:

; survival guide 13_13_200 postgresql via odbc
; pb 4.40b3
;
UseODBCDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
If OpenDatabase(1,"postgresql","postgres","postgres",#PB_Database_ODBC) = 0
  Debug "cannot open"
Else
  ;
  DatabaseUpdate(1, "DROP TABLE cars" )
  DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'jan peter' , 'balkenende' , 'volvo' , 'black' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'michael' , 'schumacher' , 'ferrari' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'johnny' , 'fireman' , 'mack' , 'red' ) ;" )
  DatabaseUpdate(1, "INSERT INTO cars VALUES ( 'mick' , 'jagger' , 'cadillac' , 'black' ) ;" )
  ;
  DatabaseUpdate(1, "UPDATE cars SET colour = 'red'"+#CRLF$+"WHERE lastname = 'balkenende' ;" )
  DatabaseUpdate(1, "UPDATE cars SET colour = 'black'"+#CRLF$+"WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseUpdate(1, "DELETE FROM cars WHERE lastname = 'schumacher' ;" )
  ;
  DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand ;" )
  While NextDatabaseRow(1) > 0
    x.s = GetDatabaseString(1,#cars_firstname) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_lastname) + " , "
   x.s = x.s + GetDatabaseString(1,#cars_brand) + " , "
    x.s = x.s + GetDatabaseString(1,#cars_colour)
    Debug x.s
  Wend
  FinishDatabaseQuery(1)
  CloseDatabase(1)
  ;
EndIf