PureBasic Survival Guide XIII - Databases
PureBasic Survival Guide
a tutorial for using purebasic for windows 5.20b5

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.13 11.07.2013

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 Table information

13.21 MySQL 5.1.38
13.22 Installation Server side
13.23 Installation Client side
13.24 SQLite to MySQL
13.25 PostgreSQL 8.4
13.26 Installation Server side
13.27 Installation Client side
13.28 MS SQL Express 2008
 

ALTERCREATEDELETEDROPINSERTSELECTUPDATE


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...

Some online resources:
SQLite

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
You'll find more information about SQLite here:
MySQL

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
You will find more information about MySQL here:
PostgreSQL

PostgreSQL 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
You will find more information about PostgreSQL here:
ODBC

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 a number of them, but sometimes you might have to add one, for example for MySQL.
 

32 bits versus 64 bits

Windows at its best! If you run software on a 64 bits platform you may encounter the following error:

The specified DSN contains an architecture mismatch between the Driver and Application
If that is the case you're using the wrong ODBC driver... See here.


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.

Please note:

  • 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 may be using a different engine than PureBasic (I had troubles with BEGIN / COMMIT)
  • you may opt for the MySQL Query Browser in combination with MySQL, or the build-in PostgreSQL query browser

13.2 Getting started: talk to me
 

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()
Creating a datafile is easy. Just create a normal, empty file, then reopen it using SQLite:
UseSQLiteDatabase()
;
CreateFile(1,"database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"Database.sqlite","","",#PB_Database_SQLite)
;
; fancy code goes here
;
CloseDatabase(1)
An empty file is of course of little use :-) So what are we going to store in it? And HOW are we going to do that?
 

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 )
In the example above, all columns are of the type 'TEXT'. (Note that this might not work in Microsoft SQL, where TEXT is deprecated.)

In PureBasic we'd have to send that instruction to the SQL engine using:

DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT )" )
At creation time we could create multiple colums like this:
CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )

SQL: adding columns

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 ;
(The semicolon would allow us to deliver multiple instructions at once, it can also be used as an end of line character / statement seperator...)


Multiple instructions

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

UseSQLiteDatabase()
;
CreateFile(1,"d:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"d:\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)
Our you could send a whole range of commands to the SQLite engine in a single string, by seperating the SQL instructions with a semicolon:
UseSQLiteDatabase()
;
CreateFile(1,"d:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"d:\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)
... and here's another variation:
UseSQLiteDatabase()
;
CreateFile(1,"d:\database.sqlite")
CloseFile(1)
;
OpenDatabase(1,"d:\database.sqlite","","",#PB_Database_SQLite)
;
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )" )
;
CloseDatabase(1)

SQL: deleting tables

Of course, we can also delete tables using:

DROP TABLE cars

SQL: the semicolon... to use or not to use?

Use them... Though some dialects do not seem to care, it's better to use them. 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" ) 
Turning it all into a full PureBasic program would give us something like this:
; survival guide 13_3_109 writing
; pb 5.50b2
;
UseSQLiteDatabase()
;
; create a file if there isn't one yet
;
If FileSize("d:\database.sqlite") < 0
  CreateFile(1,"d:\database.sqlite")
  CloseFile(1)
EndIf
;
; open the database file
;
OpenDatabase(1,"d:\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)
Note that you can specify the columns in the INSERT statement:
INSERT INTO cars ( firstname , lastname , brand , colour ) VALUES ( "mick" , "jagger" , "cadillac" , "black" ) 
The good part about that is you will have an easier time re-reading your code (it's sort of self documenting) and if you change the table column order, your code still will work! Unfortunately, GetDatabaseLong() etc. use the column number, not the column name :-(


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"
Try it in the SQLite Database Brower and you will get two entries back, 'jan peter' and 'mick'. We could finetune the selection by adding more critera, for example:
SELECT * FROM cars WHERE cars.colour = "black" AND cars.brand = "volvo"
Another option might be combining the results of multiple SELECT's using the keyword UNION:
SELECT * FROM cars WHERE cars.colour = "black"  UNION SELECT * FROM cars WHERE cars.brand = "mack"
The above should return all entries where the colour is black, and all entries where the brand is '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
And here's how we do it all together in PureBasic, reading from the database we created in the previous section. Note that I didn't implement error handling...
; 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, "d:\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)

Number of columns returned

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?
Not sure how many columns the query will return? Use the PureBasic DatabaseColumns() function:
UseSQLiteDatabase()
OpenDatabase(1, "d:\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)

Number of rows returned.

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 
In PureBasic:
UseSQLiteDatabase()
OpenDatabase(1, "d:\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)
Remeber to try out your queries using the SQLite Database Browser or the MySQL Query browser before you test them from within PureBasic. It will save you a lot of time... Or you could write your own little database browser (and post the result on the PureBasic forum to help other users)...


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' ;
Or perhaps we'll decide to remove all red cars:
DELETE FROM cars WHERE colour = 'red' ;
The two examples above show cases where the semicolon may help to avoid problems, as you are allowed to spread (some) instruction over two lines:
DELETE FROM cars
WHERE colour = 'red' ;
Here's a complete program which creates a table and inserts, changes, and deletes data:
; survival guide 13_3_115 changing and deleting
; pb 5.50b2
;
UseSQLiteDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
; create database
;
CreateFile(1,"d:\database.sqlite")
CloseFile(1)
;
; fill database
;
OpenDatabase(1,"d:\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)

13.4 Data types.
 

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
Run this program, and check the contents of the database using the SQLite Database Browser:
    UseSQLiteDatabase()
    If CreateFile(1,"d:\database.sqlite")
      CloseFile(1)
    EndIf
    OpenDatabase(1,"d:\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)

PureBasic types

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
For writing we have to embed our variables into the SQL INSERT string, like this:
; create file
;
UseSQLiteDatabase()
If CreateFile(1,"d:\database.sqlite")
  CloseFile(1)
EndIf
OpenDatabase(1,"d:\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)

13.5 The Committed Programmer
 

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,"d:\database.sqlite")
  CloseFile(1)
EndIf
OpenDatabase(1,"d:\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)
The code above will create a database, prepares 4 values to insert, and then commits the change. It will then do so again, but won't commit the data this time. If you use the SQLite Database Browser to browse the resulting database file, you will find it only contains one row.

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 ;

13.6 Who calls me a Blob?
 

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

Linking to images

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
The big disadvantage of this approach is the size increase, typically more than 30%... This happens because Base64 encoding tries to avoid any character that could cause problems to 'text only' applications...


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.

<< Still not done. Sorry. >>


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)
Again: what is a blob?

(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.
In the example above we create a database (in memory, nice trick, that one, and not mine) with four columns. Two of those columns are intended for blobs. (Column 0 and column 2.)


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' , ? ) ;")
So, the first blob replaces questionmark / placeholder number 0, which in this example means the first blob data ends up in column 0. A 1 is stored in column 1, then the next questionmark / placeholder number 1 is rep1, which ends up in column 2.
  • 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.
The second parameter of SetDatabaseBlob() specifies the index (which questionmark to replace). It does NOT specify a column number.


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)
Remember: we used column 0 for the first blob, column 1 for a long that's of no use in this example ;-) and column 2 for the second blob. Here we refer to the actual column number

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 )

13.7 Table information
 

You can have multiple databases, and multiple tables. It is possible to retrieve information on a table, but this only works after you have executed a SELECT statement.

The code below uses DatabaseColums() to retrieve the number of columns, then shows the name of each column using the DatabaseColumnName() command.

DatabaseQuery(1, "SELECT * FROM cars WHERE cars.colour = 'black' ORDER BY cars.brand ;" )
n = 0
x.s = ""
While n < DatabaseColumns(1)
  x.s = x.s + DatabaseColumnName(1,n)+"  "
  n = n+1
Wend
Debug x.s


<<>>


13.21 MySQL
 

If an SQL database is an essential part of your solution, you should consider PostgreSQL. MySQL has some license issues, and needs an ODBC connector.

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 :-)

The above was written in 2010. Who knows what has changed since then, so go and check that license before doing something stupid :-)


13.22 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 5.1.38 and 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.

Don't forget to open the appropriate port(s) in your firewall.
 


MySQL GUI Tools 5.0

Note: these seem to have been replaced by the MySQL Workbench.

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.


MySQL Workbench 5.2 CE

This seems to have replaced MySQL GUI Tools. I've tried this one on a client to execute queries and it worked fine.
 

Testing...

Start the MySQL administrator. Use the following settings:

  • server host: localhost
  • port: 3306
  • username: root
  • password: root
On the very first screen you'll find the server status. It should tell you the MySQL Server is running.


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
2. In the SQL Query Area, enter:
CREATE DATABASE PUREBASIC
SHOW DATABASES
3. Execute the query (using the lightning icon, or [Control] + [Enter] ). These statements will now create a database called 'purebasic'.

Obvously, you should only create it once... executing CREATE DATABASE PUREBASIC a second time will throw an error. It already existed :-)

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.

In the 2013 MySQL Workbench I received an error. Adding semicolons fixed it:

CREATE DATABASE PUREBASIC ;
SHOW DATABASES ;
Quite interesting...


13.23 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, for example on my Windows 7 box it could be accessed via Control Panel / Data Sources (ODBC).

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
The 'data source name' is the name under which the database is known on our client machine. It's the name we specify in our OpenDatabase() command. The 'database' name is the name of the database on the server itself. In other words, if the client program tries connects to a server known as 'mysql' the configuration above redirects it to the MySQL database 'purebasic' on the server 'sqlserver'.


64 bit users be aware! There are TWO different versions of ODBC on your machine!

Typically if you run into the following error, you're using the wrong one (mostly by trying to access the 64 bits ODBC from a 32 bits application):

The specified DSN contains an architecture mismatch between the Driver and Application
If that is the case you're using the wrong ODBC driver... You'll find the 32 bits version here:
c:\windows\sysWOW64\odbcad32.exe
On a 64 bits machine the control panel will open the 64 bits version, which is located here:
c:\windows\system32\odbcad32.exe
To make things even better, installing a 64 bits ODBC driver may list that driver in the 32 bits dialog, but it may not be installed at all! Thus a 32 bits program running on a 64 bits Windows may NOT BE ABLE TO ACCESS ODBC databases. Solution? Build 32 and 64 bits applications, and tell the user to install the proper one... unless you're using an ODBC driver which exists (and installs) as 32 AND 64 bits at the same time.

Sigh.

Anyway, if things worked out well, you should be seeing something like this:


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"
  Debug DatabaseError()
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
Here's the full setup again, running MySQL in a VirtualBox VM, executing the program above in PureBasic...

Of course, you could also install the MySQL Workbench on your client machine, to verify the results of your code, and experiment with SQL queries outside of PureBasic.


13.24 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
  7. add semicolons
  8. use BEGIN and COMMIT
You can also experiment with the SQL language using the MySQL GUI Tools or the MySQL Workbench.
 

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.25 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.26 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
4. Check your (Windows) firewall configuration. Open port 5432. You may have to enable file and printer sharing on your SQL server.

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

Don't forget to open the appropriate port(s) in your firewall.
 

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.27 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_27_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

ODBC

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
5. Hit the 'Test' button. If your firewall settings are all okay you should get a connection.

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



13.28 MS SQL Express 2008
 

Well, there should be no reason why not to try MicroSoft's latest :-) unfortunately I'm running XP in the VM, so I can't test it with 2012... Let's try it with an older version then: SQL Express 2008 on Windows XP.
 

Server side

Unfortunately, it's not as easy to setup. I've got no clue about all those MS SQL options, but the ones below got me started. Information on this page helped me to get started.

Note that I wanted explictly to communicate with the database via TCP, port, and SQL user / password. If you're using NT authentication this just might be a lot easier...

Here's how I acccomplished my goal:

  1. create a VM or build a real server
  2. make sure the VM has at least 256 MB (I managed to run MySQL, PostGreSQL and MS SQL simultaneously in 384 MB, let's not talk performance :-))
  3. download and install .NET 3.5 SP1, you might have to reboot
  4. download and install Windows Power Shell 1.0
  5. download and install Windows Installer 4.50
  6. do a Windows update to see if you've missed any patches (I got lots of 'm, took ages to install)
  7. download and install MS SQL Express 2008
  8. in the SQL Server Installation Center, pick 'stand-alone installation'
  9. next accept next etc. etc.
  10. Feauture Selection / Database Engine Services
  11. Server Configuration / Account: NT AUTHORITY\SYSTEM
  12. Database Engine Configuration: Mixed Mode, add a password (for example 'apekop'), add some users
  13. next / next / install etc. and finish installation
  14. Start / Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager
  15. RMB on SQL Server Network Configuration / Protocols, enable
  16. RMB on SQL Server Network Configuration / Protocols, properties
  17. tab Protocol
    1. Enabled: yes
    2. Listen All: yes
  18. tab IP Adresses
    1. IP1 / Active: yes
    2. IP1 / Enabled: yes
    3. IP1 / IP Address: 192.168.0.71 (my server's address)
    4. IP1 / TCP Dynamic Ports: empty
    5. IP1 / TCP Port: 1433
    6. IP2 / Active: no
    7. IP2 / Enabled: no
    8. IPAll / TCP Dynamic Ports: empty
    9. IPAll / TCP Port: 1433
  19. reboot the server (or stop and restart the SQL Server Services)
Took me ages to figure this out...

To test the above, start up SQL Server Management Studio, then...

  1. connect to a database
  2. Server name: 192.168.0.71
  3. Authentication: SQL Server Authentication
  4. Login: sa
  5. Password: apekop
Not the safest way to set up your system, or connect to the database, but for my purposes it was fine.

I then created a database:

  1. New Query
  2. CREATE DATABASE purebasic


Client side

On Windows 7 open Control Panel / Administrative Tools / Data Sources (ODBC). It's not visible on all machines, so if you can't find that option, enter ODBC in the Windows 7 search box. Notice the 32 vs. 64 bits issue!

  1. Add SQL Server
  2. Name: sqlexpress (this is the name I used in my PureBasic code)
  3. Server: 192.168.0.71
  4. Next
  5. select SQL Server authentication
  6. Client Configuration
    1. Server alias: 192.168.0.71
    2. TCP/IP
    3. Server name: 192.168.0.71
    4. unselect Dynamically determine port (you may have to click a few times before you can enter a port number)
    5. Port number: 1433
    6. Ok
  7. if you want to use the 'Test Data Source' button later, you'll have to enter a valid username and password (I used sa / apekop)
  8. Next
  9. Change the default database to: purebasic
  10. Next
  11. Next
  12. Test Data Source will only work if you filled in a username / password before

PureBasic example

Use the following code to check if your SQL Express 2008 setup is working:

; survival guide 13_27_101 sqlexpress
; pb 5.11
;
UseODBCDatabase()
;
Enumeration
  #cars_firstname
  #cars_lastname
  #cars_brand
  #cars_colour
EndEnumeration
;
If OpenDatabase(1,"sqlexpress","sa","apekop") = 0
  Debug "cannot open"
Else
  ;
  Debug "ok"
  DatabaseUpdate(1, "DROP TABLE cars" )
  DatabaseUpdate(1, "CREATE TABLE cars ( firstname VARCHAR(100) , lastname VARCHAR(100) , brand VARCHAR(100) , colour VARCHAR(100) ) ;" )
  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
Note that the PostgreSQL code differs from the SQL Express code. Microsoft has a slightly different flavour of the SQL language. In other words: what works on one SQL database might not work on another...