MySQL ! Connect to it?

Blitz3D Forums/Blitz3D Beginners Area/MySQL ! Connect to it?

Apollonius(Posted 2003) [#1]
I'm a newbie to BlitzPlus,
I would like to connect to a mysql database
example:
db name: blitz_db
user: blitz_user
password: blitz_pass

How would you connect to it and read data and write data?

Make it simple, because I'm stupid ^.^
(remember that the database is not on the same computer)


Ice9(Posted 2003) [#2]
I don't have blitzplus but have you looked at SQLite?


Apollonius(Posted 2003) [#3]
never heared of it


Ice9(Posted 2003) [#4]
http://www.hwaci.com/sw/sqlite/

There is the command line interface and also
a dll you can use. It's very small but has
most all of the features you would find in any
database. There are also some tools you can use
to convert access databases and MySQL databases
to SQLite databases. I've been using it in another
language to write some business stuff and it works
well enough for my needs. It also doesn't work as
a server so if that's what you were looking for
it may not be good fro your needs.


jfk EO-11110(Posted 2003) [#5]
As far as I see it must be pretty simple to send a MySQL Query to a MySQL Database, or do get something wrong here?

However, iana.org lists the following ports:

mysql 3306/tcp MySQL
mysql 3306/udp MySQL

If you want to then you could install EasyPHP 1.6 locally (includes PHP, MySQL and Apache, one click install) and then try to access it from the Blitz App.


Apollonius(Posted 2003) [#6]
What i wanted to do is connect to a database on another server

like www.bob.com
sql_db: bob_database
sql_user: bob
sql_password: pass
... doesnt seem to be possible


jfk EO-11110(Posted 2003) [#7]
Of course, apache don't cares if the client is on the same machine, for apache it's the same as if it's from an other machine. Simply use 127.0.0.1 as the IP. The only diffrence is you can probably use some Admin Useraccounts for the database that work only fir Localhost, but this isn't a problem.

It's not the way that you access the files or something - in esyphp you access a Php-handled MySQL Interface using a Browser over Localhost.

All you need now is some information about how PHP is sending/receiving Data from/to MySQL. I am shure there's a big lot of inforamtion about it in the internet, search for MySQL Database Access Protocol or something.

Of course, this is a bit off topic in the beginners Forum, however.
http://www.mysql.com/portal/sites/programming/index.html

EDIT:
I am not shure how hard it's gonna be if Blitz will do the Part of PHP and send right the MySQL Queries in their original Form. But one thing is for shure: I guess the Database you want to access has already a PHP Interface on that Server? SO it would be REAL easy to simulate a Browser Client that simply accesses a PHP URL.


jfk EO-11110(Posted 2003) [#8]
These days I am learning PHP and MySQL because I need it in my Job. I found it's pretty easy, although I don't know exactly what PHP is doing with those MySQL Querys behind the Stage.

As soon as I find some Time I will make some tests, because I would really like to access the MySQL Database directly by Blitz too.

In PHP it goes like this:

-Connecting to database using Password, User etc.
-Sending a special MySQL Query String, eg. a Request to access the content of an entry.
-Receive it as a string, tables delimited by whatever.

It all looks simple as a HTTP Request using some Parameters like a Password etc. So I will try to simply send those Queries as plain text to the mentioned TCP Port and then try to receive some answers. (Yes, there are several MySQL-Versions, including packed and unpacked Transfer etc.)

Maybe I am wrong and it is real complicated, I'll let you know.


jfk EO-11110(Posted 2003) [#9]
Ok, I just tried it and it seems to be a book with seven seals, or, as someone said:
"The Scientists have taken the keys of knowledge and have hidden them."

Fact is if I try to connect over TCP Port 3306 a Connection is establised, but on my user password etc Request I got some ascii Chars including the words "Bad Handshake". I wasted some hours to find a description of the MySQL Login Handshake Procedure, but I didn't find anything useful, even on mysql.com. So I think maybe we better go an other way that makes it pretty simple:

Use mysql.exe that comes with the Mysql Server Distribution in Batch mode. This is a Database Client that will take Username, Password, Host and Databasename to connect, including a Batchfile-Name to process. The Call in Blitz might look like this:

execfile "command /c mysql --user=NAME --host=URL --password=PASS --database=DATABASENAME <batch.txt"

or, using short commands:
execfile "command /c mysql -uUSER -hHOST -pPASS -dDATABASE <batch.txt"

Where of course USER is you username and so on.

If this is done locally then the password (-pPASS) can be omitted completely and Username would be "root" (-uroot).

To make Blitz able to read the Answers of the Database Server, you can as well define an Output File, so you can read that File after the Process:

a$="answer.txt"
if filetype(a$)=1 then deletefile(a$)
execfile "command /c mysql -uUSER -hHOST -pPASS -dDATABASE <batch.txt >answer.txt"
while filetype(a$)<>1
wend
delay 100
r=readfile(a$)
while eof(r)=0
print readline(r)
wend
closefile r

The Batchfile meanwhile includes all neccessary MySQL Commands, such as SELECT, WHERE,CREATE etc.

Let's say your Database Table named "Userinfo" has some Columns named Username, Picture1, CIndex (where CIndex would be used as Entry Index with autoincrement), then you could receive the Content of the Database with the following Batchfile:

SELECT Username, Picture1, CIndex FROM Userinfo ORDER BY CIndex DESC;


That's about it. To get more information about Mysql Commands read the Manuals, Tuts, use the HELP Command in mysql.exe and so on.


jfk EO-11110(Posted 2003) [#10]
Hmm - guess this became a monologue :)


podperson(Posted 2003) [#11]
Actually I think this is an interesting topic. A nice set of wrapper functions for chatting to a MySQL (or PostGreSQL even better) database would be very useful for, for example, setting up robust persistent world games.


jfk EO-11110(Posted 2003) [#12]
THX :) For all those who want to install an Apache Server, PHP 4 and a MySQL Database on their Computer with one click have a look at http://www.easyphp.org/ . If you have them on your local Machine it makes it much easier to experiment. It's a 10MB Download. Unfortunately it's a french Distribution, so some of the Errormessages are french :) However, there are some Language Config Tools.


Jamie Priest(Posted 2004) [#13]
Has anybody successfully connected sqlite bb3d together? I've had a quick look but had trouble building the decls file for it?