mysql and freebasic

New to FreeBASIC? Post your questions here.
Post Reply
Eviltoaster
Posts: 26
Joined: Nov 08, 2007 16:59
Contact:

mysql and freebasic

Post by Eviltoaster »

can someone help me find or post a few examples of how i can read and post a few things to mysql db?

im using mysql.bi

and i tryed to understand the example mysql/mysql_test.bas.

where can i read more about mysql.bi is there any good beginner tutorials?
or some mysql FB tutorials ...

cuz i cant find any :S

please help me !
Last edited by Eviltoaster on Oct 31, 2008 9:17, edited 1 time in total.
Imortis
Moderator
Posts: 1923
Joined: Jun 02, 2005 15:10
Location: USA
Contact:

Post by Imortis »

If I understand correctly, in order to use the MySQL headers, you have to have the MySQL system setup and running on you computer. The only time I can get it to work it when I am on a computer with WAMP installed and running.

I don't think that WAMP itself has anything to do with it, but it's just the fact that WAMP sets up MySQL as well.

If you don't have MySQL set up on your computer, you can use SQLite. FB also has headers for it. I've used it a couple of times and it seems to work well.
maddogg6
Posts: 824
Joined: Dec 07, 2005 22:58
Contact:

Post by maddogg6 »

I used the forum search.. and found this..
http://www.freebasic.net/forum/viewtopi ... ight=mysql

Which basically states.... if you get the docs from/for MySQL from http://dev.mysql.com/doc/

Which shows how to use MySQL in C... just transpose that info for FB.

I hope that helped....
maddogg6
Posts: 824
Joined: Dec 07, 2005 22:58
Contact:

Post by maddogg6 »

Imortis wrote: I don't think that WAMP itself has anything to do with it, but it's just the fact that WAMP sets up MySQL as well.
Yes... thats the most convenient way to get MySQL running and doing something... but you can install mysql alone too..

You also can purchase a MySQL ODBC connector to access a remote MySQL Server... but, I would..

Export the remote MySQL databases (tables and data) - replicate it on your local machine's mySQL server... and use FB...

Or - if you just want to import CSV into MySQL (and have php) - the easiest thing is through PHPMyAdmin
http://www.phpmyadmin.net/home_page/index.php
- which is easy enough to instal on a remote server (providing you have permissions to do so) - and use its CSV import functions built into PHPMyadmin. And bypass FB all together... that is, if your just looking to accomplish this task at hand.

edit: if its a large database - you may want to replicate the remote database to your local machine, run phpMyadmin (use WAMP) import CSV in PHPMyAdmin locally - then export that database - and import that into the remote mysql server.... to avoid php time outs from uploading a large file -

or

make sure the CSV file is on the remote server and import from the php server....

sooo many ways to skin a cat these days... :P
cha0s
Site Admin
Posts: 5319
Joined: May 27, 2005 6:42
Location: USA
Contact:

Post by cha0s »

Another real easy way to get mysql up and runnin (on windows, linux ios just plain easy) is EasyPHP, which also installs apache and php for ya, which is cool.

edit: if you want to learn SQL syntax, you could try this place http://www.w3schools.com/sql/default.asp

I learned out of a book.... visit your library? :P
maddogg6
Posts: 824
Joined: Dec 07, 2005 22:58
Contact:

Post by maddogg6 »

just to clarify, in case its not obvious..

WAMP = Windows Apache MySQL PHP

So, Easy PHP is a WAMP. and would definitly work well in this case.
(I am a happy XAMPP user myself, but includes a lot of stuff you may not be interested in, and is not needed for this task)

WAMP make setting up a web server on a WIN PC real easy.
cha0s
Site Admin
Posts: 5319
Joined: May 27, 2005 6:42
Location: USA
Contact:

Post by cha0s »

Ah, I figured "WAMP" was a name of some software distribution. I heard of LAMP but now WAMP. Man, I wouldn't run a server actually connected to the net with Windows, but it could possibly be somewhat safe, I suppose. I'd rather just use Linux for that and sleep easy =P
maddogg6
Posts: 824
Joined: Dec 07, 2005 22:58
Contact:

Post by maddogg6 »

cha0s wrote:Ah, I figured "WAMP" was a name of some software distribution. I heard of LAMP but now WAMP. Man, I wouldn't run a server actually connected to the net with Windows, but it could possibly be somewhat safe, I suppose. I'd rather just use Linux for that and sleep easy =P
heh - I do frequently...
but I also have *friends* visit my WAMP site to transfer large files easily by sending links in email.
And...
I can turn the Apache and MySQL servers on/off easily (and do turn it off when not developing, actually, I have to turn it on to develop)

It basically just opens a few ports needed to operate as a web server. It plays well with Zone Alarm, and I have a router / firewall too - not to mention, I dont have any personal info stored on my PC (no quickbooks, no social security # etc... maybe my address, but, that can be hacked from new egg along with CC info, a much likelier target than my dynamic IP - even tho its broadband, it changes about 1/month)

So, I wouldn't worry too much about security, any further than I do for windows in general... turning off my cable modem is also an added safety feature, but I never do because of my firewalls.

But its definitely worth noting, DO NOT KEEP A WAMP running 24/7 if you care about that PC's security or performance.

I can also run MySQL and not apache/PHP with XAMPP too - again, for security, I turn MySQL server off when I am not using it.
kazmo
Posts: 16
Joined: Dec 30, 2006 15:17
Location: USA

Re: mysql and freebasic

Post by kazmo »

Eviltoaster wrote:can someone help me to find or post a few examples...........
Here is a sample I have. I intended to persue this further and post the samples/examples, but have been sidetracked for several months now.

If you can get over the fact that it was coded by a procedural dinosaur, it may get you started. Maybe one of the OO experts can clean it up.

This was coded and tested with v.16b. I have not yet installed and tested with a more recent FB release nor do I currently have time to do it.

Hope it at least helps.

Code: Select all

''  mysqlkaz2.bas -  This is a simple sample FreeBASIC program that demonstrates connecting to a mysql database
''      and perfoming a simple SQL select on a test database.  Sample MySQL DDL is included(at the bottom).
''  This program will crash if the test table and database are not defined within mysql.
''
''  This program was created using FreeBASIC v0.16beta, in a WindozeXP environment, running MySQL 5.0.19  
''
''  See the pertinent MySQL API at http://dev.mysql.com/doc/refman/5.0/en/apis.html  for MySQL version 5
''
''  The MySQL test  database can be created using the MySQL  DDL attached as comments at the end of this
''  sample program.  If you don't know what that is, then some research into MySQL might be useful before proceeding.......
''  Alternatively, it should be pretty simple to modify this sample to access an actual table or view you already have at hand.
''
''  copyright 2007, der kazmo.  All rights to use, abuse, and peruse this picayune bit of sample code are granted fully, and in 
'' perpetuity to anybody anywhere.  Actual resposibility for the use, misuse, or abuse of this sample rest wholly with the 
'' party making said usage, and not der kazmo, his heirs, or anybody else.  If you melt you mommas 'puter, well, that's on you.
''

option explicit

#include once "mysql\mysql.bi"
#define NULL 0	

	dim db as MYSQL ptr	'??????????say whu
	dim as string  dbname, host, usereyedee, passwerd
	dim table01 as string
	dim conn as MYSQL ptr
	dim rc as integer
	
	rc = 0
	''
	''  Initialize the address for a MySQL database object
	db = mysql_init( NULL )
	dbname = "fbtest"
	table01 = "testtable1"
	host = "localhost"
	usereyedee = "bob"
	passwerd = "bobspassword"
	''
	''  Connect to the MySQL database server running on 'host'.  This example passes the userid(usereyedee) and password(passwerd) 
	if( mysql_real_connect( db, host, usereyedee, passwerd, NULL, MYSQL_PORT, NULL, 0 ) = 0 ) then
		print "Can't connect to the mysql server on port"; MYSQL_PORT
		mysql_close( db )
		end 1
	end if
	
	''  The mysql_real_connect API call will set the mysql_errno.  This can be tested.
	''  It may give more specific information if the mysql_real_connect fails to connect and return a database pointer value.
	''  Note:  It would seem that not all of the MySQL API calls actually set the mysql_errno.  Go Figure.  caveat emptor
	rc = mysql_errno( db )
	if rc = 0 then
		print "connected successfully to mysql, select database next"
	else 
		print "bad connect,  RC ="; *mysql_error( db )
	end if
	
	''
	'' Here, we send mysql a command to set the current database(dbname).  This is going to be the database holding our data table
	if( mysql_select_db( db, dbname ) = 0 ) then
		print " Selected "; dbname; " as the current database"
	else
		print "bad connect, "; "mysql_error #"; mysql_errno (db ); "- mysql error->>"; *mysql_error( db )
		print "Can't select the "; dbname; " database !"
		mysql_close( db )
		end 1
	end if
	''
	'' Display the clist, host and server info, plus some windoze environment stuff.
  	print " "
	print "Client info: "; *mysql_get_client_info()
    	print "Host info: "; *mysql_get_host_info( db )
    	print "Server info: "; *mysql_get_server_info( db )
	print " "
	Print "Home drive: "; Environ("HOMEDRIVE") 
	Print "Home path: "; Environ("HOMEPATH") 
	Print "freebase: "; Environ("freebase") 
	Print "java_home: "; Environ("java_HOME") 
	print " "
	print "User domain: "; Environ("userdomain")
	print "User profile: "; Environ("userprofile")
  	print "User Name: "; Environ("username")
	print " "
	
	
	'' declare variables used for querying the database
  	dim fd as MYSQL_FIELD ptr
	dim fields(24) as string
	dim fieldstr as string
	
	dim res as MYSQL_RES ptr
  	dim row as MYSQL_ROW
	dim rowstr as string
	dim query1 as string
	dim qlen1 as Unsigned Long
	dim qrows1 as unsigned long
	
	dim as integer l, x, j, k
	
	''build the sql statement into a string variable, and get the length of the string.
	''  This sample query selects three columns from the TESTTABLE1 table, which
	''  is defined within my FBTEST database in my mysql sandbox
	query1 =    "select testid, testname, testfone" 
	query1 +=	" from " + dbname + "." + table01      
''	query1 +=	" order by testname"			''more fun
''
''	query1 +=	" where testid > 4444 order by testname"	  ''more fun
''

	qlen1 = len( query1 )
	
	'' Send the query to the connected database.   This particular mysql API returns a non zero value on failure.
	rc = mysql_real_query ( db, query1, qlen1 )
	if rc = 0 then	
	else 
		print "Query1 error,  RC ="; mysql_errno( db ); "- mysql error->>"; *mysql_error( db )
	end if
	
	
	'' After the query succeeds, call the database to store the result set from the query
	''  and test the mysql_errno for failure if you so choose.
	''
	''  The mysql_store_result function returns the entire result set to the client
	''  If you chose to return the entire result set, the mysql_fetch_row function is used iteratively to
	''  access each row sequentially in the result set for processing.
	''
	''Note:
	''  An alternative (e.g., when the result set from the query is known to be large) 
	''  is to use the mysql_use_result function. 
	'' Each method has its own pros/cons.  That choice is subjective.
	''
	'' Note2:  You must(I say twice Must) call either the mysql_store_result or mysql_use_result
	'' for any query that returns data, per the MySQL API.  They seem quite adamant.  If you don't, your girlfriend will leave you 
	''for your best friend, and she'll take your dog with her, and tell all your other friends fibs about you.  Okay...maybe just unpredictable results....
	'' Additionally, the mysql_use_result mechanism requires that you iterate through the entire result set, or you will get bogus results on the
	''  following query.
	'' In this sample, the mysql_num_rows will be zero using the mysql_use_results mechanism,
	''  but will be the actual number of rows using mysql_store_result
	
	res = mysql_store_result( db  )  ''MYSQL RETURNS THE ENTIRE RESULTS SET
''	''res = mysql_use_result( db  )     ''MYSQL RETURNS A SINGLE ROW WITH EACH FETCH
	if mysql_errno(db) = 0 then	
		print "The query returned ";
		qrows1 = mysql_num_rows ( res )
		print "  -->  " + str(qrows1) + "  rows from " + dbname + "." + table01    
	else 
		qrows1 = 0
		print "Query1 error,  RC ="; mysql_errno( db ); "- mysql error->>"; *mysql_error( db )
		mysql_close( db )
		end 1
	end if

	'' print some simple headings...............
  	print "Query results:"
	Print "Query results for: ";  Query1
	print "Query results"
	print " "
	print " Columns ";
	
	''
	'' This next chunk of code will access some of the metadata returned in the result set.  This piece will 
	''  store and print the column names for the fields selected by the query.  
	''      (Fields?  Fields???  Dang, I'll bet there's some GoTos lurking in that object code too!  Run!  Hide!!)
	fd = mysql_fetch_field( res )
	j = mysql_num_fields( res )   
	l = 1
	x = 0
	for k = 1 to j
		
		do until (fd = NULL)
			print "  ------>"; k; " ";
				fields(k) = *fd->name
				print fields(k); 
			print "  ||  ";
			fd = mysql_fetch_field( res )
			k += 1
		loop					'' GoTo the end of the loop
		print " "
	next
		
	''  Loop through the stored result set of the query sequentially.
	'' The mysql_fetch_row function returns null after the last row is fetched, or if the query 
	'' does not have any results, or if an error is encountered.  You can test mysql_errno for nonzero.  I got 
	'' lazy.  But if you get an error from mysql and you don't handle it, your program will crash.... :o
	'' Try it and see.  
		
	''  Note:  Normally, one would know the structure of the returned data well enough to be able to handle each column.
	''  One might store the data into an array, or more likely handle each row individually.  This bit of kit could be used to call 
	'' a function that handles each row.  This is just a sample....several ways to skin that particular beast.
	do 
		row = mysql_fetch_row( res )
		if( row = NULL ) then
			mysql_free_result( res )
			exit do   			'' GoTo the end of the loop
		end if
				
		print "Table Row #"; l; " :-"; 
		
		l += 1
		for k = 0 to j-1
			print "  Col #"; k+1; " "; 
			if( row[k] = NULL ) then
				print "NULL";
			else
				rowstr = *row[k]
				print rowstr; 
			end if
			print "  ||  ";
		next 
		print " *"
	loop
	    
	''  Okay, I just hate house keeping, but a clean house is a Good Thing.  If the  mysql_init worked, the db object should be freed
	''  before exiting the program.
	mysql_close( db )
	end 0
''
''
''  sample MySQL DDL that goes along with this program.   
''  
''        
''        #--
''        #-- create a database for freebasic examples called fbtest
''        CREATE DATABASE fbtest;
''        
''        #--
''        #-- create a table named "testtable1" in the fbtest database
''        CREATE TABLE fbtest.testtable1
''        (testid char(08),
''         testname char(15),
''         testfone char(15));
''        
''        #--
''        #-- insert a few records into the table
''        INSERT INTO fbtest.testtable1 VALUES
''         ( "1111", "Jim ", "555-1212 " ),
''         ( "2222", "Jimbo ", "555-1212 " ),
''         ( "3333", "James ", "555-1212 " ), 
''         ( "4444", "Jaimie ", "555-1212 " ),
''         ( "5555", "JimSlim ", "555-1212 " ),
''         ( "6666", "Jimm ", "555-1212 " ),
''         ( "7777", "Jimberly ", "555-1212 " ),
''         ( "8888", "Jimmaman ", "555-1212 " ),
''         ( "9999", "Jimbalaya ", "555-1212 " ),
''         ( "0000", "Jimmmy ", "555-1212 " );
''        
''        #--
''        #-- create a bogus user named bob on the localhost
''        create user 'bob'@'localhost';
''        
''        #--
''        #--  grant bob some basic privledges for testing 
''        grant usage on fbtest.testtable1 TO 'bob'@'localhost';
''        grant select, insert, update
''              on fbtest.testtable1 to 'bob'@'localhost';
''        
''        #--
''        #--  give bob a (: really ultra hack proof and secure :) password
''        #--  
''        SET PASSWORD FOR 'bob'@'localhost' = PASSWORD('bobspassword'); 
''        #--
''        #--  that's enough to get started with the freebasic samples.
''        \q
ciw1973
Posts: 157
Joined: Jun 12, 2007 15:03
Location: Isle of Man (United Kingdom)

Post by ciw1973 »

Whilst the P in WAMP is generally and most commonly considered to be PHP, it can also be Perl or Python.
Eviltoaster
Posts: 26
Joined: Nov 08, 2007 16:59
Contact:

Post by Eviltoaster »

whow .. thanks for all answers

but the server is not the problem im using xampp when i programme xhtml/css/PHP/SQL, so im all ready used to that program and it takes like 30 sec to install to ...but you get much crap with it :S

thanks a lot Kazmo ! that was just what im looking for..

now i hope i can understand this a bit more!


EDIT: im usng 0.16b to, so dont worry..
mmbcastle
Posts: 16
Joined: May 05, 2021 11:46
Location: Tennessee, USA
Contact:

Re: mysql and freebasic

Post by mmbcastle »

You also can purchase a MySQL ODBC connector to access a remote MySQL Server...
Does FreeBASIC (specifically MySQL.bi) work with the free MySQL ODBC driver? I don't mind installing a local MySQL instance for development. I often do that. However, I need to access servers for production.

While I'm bumping an old thread, has anyone written an OO wrapper for MySQL.bi yet (like the one for SQLite)? If not, I may do that as my first "pure" FB library project.

I'm rather hooked on how PHP does it. The old QB projects I'm converting use an internally developed "database" library and no access to MySQL, etc (since a lot the code is 29 years older than MySQL, 5 years older than ODBC, 2 years older than MS SQL Server, and only 9 years younger than SQL itself). However, going forward I will need MySQL support for new development (may as well drop VB too while I'm getting away from MS products).
Post Reply