FB - MySql

New to FreeBASIC? Post your questions here.
ike
Posts: 383
Joined: Jan 17, 2011 18:59

FB - MySql

Postby ike » May 17, 2013 23:11

I have MySql database on Internet. PHP code to enter data is written by somebody else, but I have all code

I need to write a program to connect to database, read some fields in a table and save it to local disk on my PC in a row text file.

QUESTION:

1. Assuming Mysql on Internet works well, WHAT FILES (libs and drivers ..) I have to have on my PC? Are those included in FB 0.24?
And what about 0.90 version?

2. Does it matter what version of mysql is on The Net?

Thx

Ike
TJF
Posts: 3486
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: FB - MySql

Postby TJF » May 18, 2013 7:12

ike wrote:I need to write a program to connect to database, read some fields in a table and save it to local disk on my PC in a row text file.

I guess you wont adapt the php code but want to write new code in FB.

ike wrote:1. Assuming Mysql on Internet works well, WHAT FILES (libs and drivers ..) I have to have on my PC? Are those included in FB 0.24?
And what about 0.90 version?

You'll need to
  • #INCLUDE ONCE "mysql/mysql.bi" (included in FB)
  • the binaries (you'll have to download and install, ie 'mysql.dll' in your project folder -- I recommend 'mariadb')

ike wrote:2. Does it matter what version of mysql is on The Net?

Since you're asking for a simple data querry the version shouldn't matter.
pestery
Posts: 493
Joined: Jun 16, 2007 2:00
Location: Australia

Re: FB - MySql

Postby pestery » May 20, 2013 9:15

You can get the .dll (and the .lib) from, http://dev.mysql.com/downloads/connector/c/
To link to the .dll either place it in the same directory as your code at compile time so the linker can find it, or use a tool to convert the .lib file to a .a file, because the linker can't actually use a .lib file. The tool I use is simple enough. You can get it from here if your interested.

As TJF mentioned, just #Include "mysql/mysql.bi" in your code to use MySQL. However, the headers included with FB seem to be quite old, from MySQL version 4.0.17 I think. I'm currently trying to port a newer version, version 6.1.0. I can let you know how it goes.
ike
Posts: 383
Joined: Jan 17, 2011 18:59

Re: FB - MySql

Postby ike » May 21, 2013 16:38

Code: Select all


#include once "mysql\mysql.bi"
#define NULL 0   
   
   dim conn as MYSQL ptr
   dim res as MYSQL_RES ptr
   dim row as MYSQL_ROW ptr
   
   dim server as zstring ptr
   dim user as zstring ptr
   dim password as zstring ptr
   dim database as zstring ptr
   
   *server = "IKE.db.6891333.hostedresource.com"
   *user = "IKE"
   *database = "IKETEST"
   *password = "IKE@2013"
   
   conn = mysql_init(NULL)

   'if( mysql_real_connect( conn, server, user, password, database, MYSQL_PORT, NULL, 0 ) = 0 ) then
   if( mysql_real_connect( conn, server, user, password, database, 0, NULL, 0 ) = 0 ) then
   
        print *mysql_error(conn)
      print "Can't connect to the mysql server on port"; MYSQL_PORT
      mysql_close(conn)
      end 1
   end if
   
   if (mysql_query(conn, "show tables") = 0)  then
      print *mysql_error(conn)
      end 1
   end if
   
   res = mysql_use_result(conn)
   
   print("MySQL Tables in mysql database:")
   while ((row = mysql_fetch_row(res)) <> NULL)
      print(row[0])
   wend
   
   mysql_free_result(res)
   mysql_close(conn)




I just copied libmysql.dll into program folder, compile fine, but I am getting this error:

Can't connect to MySQL server on 'localhost' (10061)
Can't connect to the mysql server on port 3306


I HAVE VERY LIMITED EXPIRIENCE WITH SQL
TJF
Posts: 3486
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: FB - MySql

Postby TJF » May 21, 2013 19:54

ike wrote:..., but I am getting this error:

Can't connect to MySQL server on 'localhost' (10061)
Can't connect to the mysql server on port 3306

There's a problem connecting to the server. It doesn't connect to 'IKE.db.6891333.hostedresource.com', but tries to connect to 'localhost' instead, where obviously no MySql server is present.

Do you have a firewall running, blocking/redirecting ports? Or does any other software redirect the ports?
ike
Posts: 383
Joined: Jan 17, 2011 18:59

Re: FB - MySql

Postby ike » May 21, 2013 20:19

I install WAMPSERVER and it works but I am gettin CRASH on this line


print mysql_num_fields(res)



Code: Select all

#include once "mysql\mysql.bi"
#define NULL 0   
   
   dim conn as MYSQL ptr
   dim res as MYSQL_RES ptr
   dim row as MYSQL_ROW
   
   dim server as zstring ptr
   dim user as zstring ptr
   dim password as zstring ptr
   dim database as zstring ptr
   
   *server = "IKE.db.6891333.hostedresource.com"
   *user = "IKE"
   *database = "IKETEST"
   *password = "IKE@2013"
   
   conn = mysql_init(NULL)

   if( mysql_real_connect( conn, server, user, password, database, MYSQL_PORT, NULL, 0 ) = 0 ) then
        print *mysql_error(conn)
      print "Can't connect to the mysql server on port"; MYSQL_PORT
      mysql_close(conn)
      end 1
   end if
   
    print "Client info: ", *mysql_get_client_info()
     print "Host info: ", *mysql_get_host_info(conn)
     print "Server info: ", *mysql_get_server_info(conn)
     
   
   if (mysql_query(conn, "show tables") = 0)  then
      print *mysql_error(conn)
      end 1
   end if
   
   res = mysql_use_result(conn)
   
   
     
   print("MySQL Tables in mysql database:")
   print "===================================================="
   
   ' IT CRASHES HERE ON NEXT LINE?????
   
   'print mysql_num_fields(res)
   
   
   'while  ((row = mysql_fetch_row(res)) )
   '   print(row[0])
   'wend
   
   mysql_free_result(res)
   mysql_close(conn)

pestery
Posts: 493
Joined: Jun 16, 2007 2:00
Location: Australia

Re: FB - MySql

Postby pestery » May 22, 2013 9:15

ike, try this. I only made a couple of changes, but it now works on my test server at least.

Code: Select all

#Include Once "mysql\mysql.bi"
#define NULL 0

Dim conn As MYSQL Ptr
Dim res As MYSQL_RES Ptr
Dim row As MYSQL_ROW

' --- Change to strings from zstring ptr's
Dim server As String
Dim user As String
Dim password As String
Dim database As String

server = "IKE.db.6891333.hostedresource.com"
user = "IKE"
database = "IKETEST"
password = "IKE@2013"

conn = mysql_init(NULL)

' --- Just use the strings directly because FB will Cast() them as
'     ZString Ptr, or if you want to be sure, use StrPtr(string)
If( mysql_real_connect( conn, server, user, password, database, MYSQL_PORT, NULL, 0 ) = 0 ) Then
   Print *mysql_error(conn)
   Print "Can't connect to the mysql server on port"; MYSQL_PORT
   mysql_close(conn)
   Sleep
   End 1
End If

Print "Client info: ", *mysql_get_client_info()
Print "Host info: ", *mysql_get_host_info(conn)
Print "Server info: ", *mysql_get_server_info(conn)

Dim As String sql = "SHOW TABLES"
If mysql_query(conn, sql)  Then
   Print "Error (line " & __LINE__ & "): " & *mysql_error(conn)
   Sleep
   End 1
End If

' --- Need to check that _use_result actually returns a result
res = mysql_use_result(conn)
If res = 0 Then
   Print "Error (line " & __LINE__ & "): " & *mysql_error(conn)
   mysql_free_result(res)
   mysql_close(conn)
   Sleep
   End 1
EndIf

Dim As Integer columns = mysql_num_fields(res)
Print
Print "MySQL Tables in mysql database:"
Print "===================================================="
Do
   row = mysql_fetch_row(res)
   If row = 0 Then Exit Do
   For i As Integer = 0 To (columns - 1)
      If row[i] Then Print *row[i];" "; Else Print "NULL ";
   Next
   Print
Loop

mysql_free_result(res)
mysql_close(conn)
Print
Print "Finished"
Sleep
Edit: Just fixed a typo
Last edited by pestery on May 23, 2013 6:30, edited 1 time in total.
ike
Posts: 383
Joined: Jan 17, 2011 18:59

Re: FB - MySql

Postby ike » May 22, 2013 16:11

FB is not best language YET, maybe?

(I said YET, and MAYBE)

But FB comunity IS THE BEST!!

Return to “Beginners”

Who is online

Users browsing this forum: No registered users and 1 guest