Topics

Accessing MySQL databases from BBC BASIC for Windows

Jon Ripley
 

Hey all,

MySQLDemo demonstrates how to communicate with libMySQL to connect to a
real MySQL database server and gives the building blocks required to
write software that uses MySQL databases.

The MySQL demonstration is available from my web site at:
* http://jonripley.com/bb4w/win32api/examples/MySQLDemo.bbc

You will need the following files in your BB4W LIBrary drectory:
* libMySQL.DLL - http://tinyurl.com/367ox8
* mysqldef.bbc - http://tinyurl.com/29k8ho (Thanks Richard!)

The following variables at the start of the program need to be
initialised to the host name or IP address of your MySQL server, the
user name you use to connect to the server, your password and the name
of your database:

host$ = "hostname"
user$ = "username"
pass$ = "password"
database$ = "databasename"

The interface is simple: you are prompted to enter a SQL query which the
program processes then displays any results or a detailed error message
if something went wrong. Use your favourite search engine to find MySQL
tutorials. The code of MySQLDemo should be reasonably self-explanatory.
Consult the documentation for information about what the various SYS
calls do.

You might like to take a look at the source code to libmysql which I
have found to be most enlightening:
http://tinyurl.com/23an28 (koders.com)

Regards,
Jon
--

Guy Schaffner <genista@...>
 

--- In bb4w@..., Jon Ripley <jon@...> wrote:
[...]
The MySQL demonstration is available from my web site at:
* http://jonripley.com/bb4w/win32api/examples/MySQLDemo.bbc
[...]
The following variables at the start of the program need to be
initialised to the host name or IP address of your MySQL server,the
user name you use to connect to the server, your password and thename
of your database:

host$ = "hostname"
user$ = "username"
pass$ = "password"
database$ = "databasename"
Hi, Jon!

Well done! Only there is an issue with the variable
name 'database$', which comes out syntax-coloured as:

[orange]data[black]base$ ... etc.

The choice 'lowercase keywords' or not does not make any difference.

Regards,

Guy.

Jon Ripley
 

Guy Schaffner wrote:
Well done!
Thanks.

Only there is an issue with the variable
name 'database$', which comes out syntax-coloured as:
[orange]data[black]base$ ... etc.
The choice 'lowercase keywords' or not does not make any difference.
Yes it does! The entire problem is caused by lower case keywords! Note that the line didn't become retokenised (syntax coloured) until you edited it.

I've changed the program to cater for users who choose lower case keywords. Either download it again or search and replace 'database$' with 'dbase$' which I assume you've already done.

Regards,
Jon
--

Guy Schaffner <genista@...>
 

--- In bb4w@..., Jon Ripley <jon@...> wrote:

[...] Either download it again or search and replace 'database$'
with 'dbase$' which I assume you've already done.
Thank you. I had chosen 'dtabase$' to realise that I got the message:

'Unknown MySQL Server Host'http://[...]' (11004)
MyQSL server has gone away'

I suppose that '11004' is an error code, for which I have not found
any more explanations so far.

Secondly, I think I have not typed the database address in full, as
in the regular way, I normally get a 'menu page' from which I have a
link to reach the db. I will have to study what that link does.

Regards,

Guy.

Jon Ripley
 

Guy Schaffner wrote:
I suppose that '11004' is an error code, for which I have not found
any more explanations so far.
'Unknown MySQL Server Host'http://[...]' is the (hopefully explanatory) error!

I assume from your message that you've got something like the following:

host$="http://a.server.url/"
or host$="http://12.34.56.78/"

Change it to:

host$="a.server.url"
or host$="12.34.56.78"

You need to have the correct value for host$ before you worry about user$, pass$ and dbase$. You need to get in touch with your hosting provider or take a look at your hosting account information to find out the information you need.

Consider installing a MySQL solution and testing locally until you have your code up and running before you start using your hosting providers server.

Regards,
Jon
--

atomisermusic <breaks@...>
 

Hello

Fishing out this very old message...

In my previous location I had MySQL access working fine, and my code was happily working away with host$="xxserver" (where 'xxserver' was the local server name).

Now I'm just setting up with a new location, new network, new server. When it gets to this line of code:
SYS mysql_connect%, MYSQL{}, mysql_host$, mysql_user$, mysql_pass$ TO ret%
...it is always stopping with an "address out of range" error.

It doesn't seem to matter what mysql_host$ is set to. I've tried 'yyserver' (where 'yyserver' is the new local server name), I've tried 'localhost' (Wampserver is running on this PC).

Any advice about what might be causing an 'address out of range' error would be appreciated. If I was just getting the hostname wrong, then if I read this old forum thread properly I should get an 'Unknown MySQL Server Host' error instead?

I've downloaded Jon's MySQLDemo.bbc and it has the same error, on the same line (SYS mysql_connect%, MYSQL{}, host$, user$, pass$ TO ret%) yet all I have done is change the host$ value, so I don't think the error lies elsewhere in my code.

Stuart

--- In bb4w@..., Jon Ripley <jon@...> wrote:

Guy Schaffner wrote:
I suppose that '11004' is an error code, for which I have not found
any more explanations so far.
'Unknown MySQL Server Host'http://[...]' is the (hopefully explanatory)
error!

I assume from your message that you've got something like the following:

host$="http://a.server.url/"
or host$="http://12.34.56.78/"

Change it to:

host$="a.server.url"
or host$="12.34.56.78"

You need to have the correct value for host$ before you worry about
user$, pass$ and dbase$. You need to get in touch with your hosting
provider or take a look at your hosting account information to find out
the information you need.

Consider installing a MySQL solution and testing locally until you have
your code up and running before you start using your hosting providers
server.

Regards,
Jon
--

atomisermusic <breaks@...>
 

Further to my earlier e-mail, I've made a half-step forward by seeing that mysql_connect% is deprecated and mysql_real_connect% should be used instead.

It now doesn't create the error message as before, though it still says 'Can't connect to MySQL server on...'

I'm wondering if it's related to something Guy Quinlan-Brown said a long time ago:
"I rectified this by changing mysql_connect% to
mysql_real_connect% and passing the extra parameters required (port
etc)."
http://tech.groups.yahoo.com/group/bb4w/message/7958

Now I'm struggling on the next step... having got as far as
SYS mysql_real_connect%, MYSQL{}, host$, user$, pass$, dbase$ , 0, 0, 0 TO ret%
I'm now getting 'access denied' despite the correct username and password, possibly because one of those last noughts shouldn't be a nought?

Also, several previous forum posts refer to files in the MySQL folder of the Files section of this group. There's no MySQL folder there now- is there a reason for this, and is there a new area for MySQL-related programs?

Thanks

Stuart

atomisermusic <breaks@...>
 

Apologies for the multiple e-mails but I'm making very small progress every few hours. Again, I've taken one small step forward before hitting a wall...

I have been using Jon Ripley's MySQLDemo as a starting point, getting the code from http://jonripley.com/bb4w/win32api/examples/MySQLDemo.bbc

All I've been altering in the code is:
1. changing the values of host$, user$, pass$ and dbase$ (to refer to my localhost from Wampserver)
2. changing the line:
SYS mysql_connect%, MYSQL{}, host$, user$, pass$ TO ret%
to:
SYS mysql_real_connect%, MYSQL{}, host$, user$, pass$, dbase$, 0, 0, 0 TO ret%

I don't know that that second line is right but it does at least appear to make a connection to the database when run.

The program reaches the QUERY > prompt, and if you type 'QUIT' it exists.

However the moment you type anything resembling a MySQL query, the program crashes, and takes BB4W down with it, with that unhelpful "bbcwin.exe has encounted a problem and needs to close." Every single time, without fail.

By process of elimination I've managed to work out that the fatally crashing line is this one:
!(^MYSQL_RES{}+4) = ret%

Now I've got to be honest, I don't understand how that line works or exactly what it's doing, which doesn't help when it comes to fixing it.

Please can somebody suggest what could be causing this crash at this point?

Stuart

Richard Russell
 

--- In bb4w@..., "atomisermusic" <breaks@...> wrote:
However the moment you type anything resembling a MySQL query, the
program crashes, and takes BB4W down with it, with that unhelpful
"bbcwin.exe has encounted a problem and needs to close."
Unhelpful? If you're getting that message it probably means a Doctor Watson error log has been generated. Looking at that might provide a useful insight into what's going on. I usually find the contents of DRWTSN32.LOG to be very helpful!

I've managed to work out that the fatally crashing line is this one:
!(^MYSQL_RES{}+4) = ret%
Now I've got to be honest, I don't understand how that line works
I admire your honesty, but wouldn't it be better if you *did* understand how your program works? Presumably you've checked the obvious - that ret% contains a sensible value?

Please can somebody suggest what could be causing this crash at
this point?
It would be so much easier to help if you could upload some code for us to run. I appreciate that this might be difficult - you'd presumably need to include username, password and server information you'd rather not reveal (or aren't at liberty to) - but it would probably get the problem solved much more quickly.

Richard.

Jon Ripley
 

Hi Stuart,

atomisermusic wrote:
Now I'm just setting up with a new location, new network, new server. When it gets to this line of code:
SYS mysql_connect%, MYSQL{}, mysql_host$, mysql_user$, mysql_pass$ TO ret%
...it is always stopping with an "address out of range" error.
An address out of range error usually in this context usually means that the function address you are calling is zero, typing PRINT mysql_connect% when you get this error will confirm it. In this case wouldn't matter what parameters you passed. This function should still work even though it is depreciated.

SYS mysql_real_connect%, MYSQL{}, host$, user$, pass$, dbase$ , 0, 0, 0 TO ret%
I'm now getting 'access denied' despite the correct username and
password, possibly because one of those last noughts shouldn't be a
nought?
That call to mysql_real_connect should work. The only zero you would normally change is the first signifying the server port number if it is not the default of 3306. You've not specified the full error message which would have helped track down the issue.

If the error is along the lines of "Access denied for user 'user'@'host' (using password: YES)" it means that you need to grant permission for that user to connect from the host.

If the error is along the lines of "Access denied for user 'user'@'host' to database 'dbase'" it means that you need to grant permission for that user to access that database from that host.

See the MySQL online manual for information about user permissions, choose your MySQL server version from the left hand menu if it is not 5.1:
http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html

I've downloaded Jon's MySQLDemo.bbc and it has the same error
It could be that your copy of the MySQL library is out of date. Download the full MySQL example from my site at:
http://jonripley.com/volatile/MySQL.zip

Make a backup of your BB4W library directory and then copy the libmySQL.dll and mysqldef.bbc files from the file you downloaded into the BB4W library directory.

Load the MySQLDemo.bbc program and enter the correct host, database, username and password and see if it works. You can try CreateTable.bbc which just demonstrates creating a table.

By process of elimination I've managed to work out that the fatally
crashing line is this one:
!(^MYSQL_RES{}+4) = ret%
That line points the MYSQL_RES structure to the results structure returned by mysql_use_result so we can use the data returned. What's the value of ret% at this point?

What query are you running?

Can you provide a minimum program that displays the fault?

Regards,
Jon
--

atomisermusic <breaks@...>
 

Thanks Jon and Richard for the suggestions about the MySQL connection.

This is just a short note to let you know that something else has come up work-wise and I won't be back at the location I was working at the MySQL connectivity from until Monday. So I'll have another crack at the MySQL connection next week and let you know how it goes.

Stuart

atomisermusic <breaks@...>
 

I sent a post on Monday about this but it doesn't seem to have shown up- apologies for any duplication.

I am a lot further on with MySQL than I was last week, mainly thanks to downloading Jon's version of libmysql.dll and using it to overwrite my previous copy. Once I'd done that, the crashing pretty much stopped, and once I got told the correct password for the MySQL server (human error, and not mine...) I was able to connect and start querying to my heart's content.

Thanks again to Richard and to Jon for the suggestions. I did respond to them more thoroughly before, and may have to again if this message doesn't show up...

Stuart

--- In bb4w@..., Jon Ripley <jon@...> wrote:
It could be that your copy of the MySQL library is out of date.
Download the full MySQL example from my site at:
http://jonripley.com/volatile/MySQL.zip

georg_wissinger
 

Hi,

i got an error trying to open a connection to mysql with bbc basic for windows.
"Client does not support authentication protocol requested by server; consider upgrading MySql client."

is there any newer client?
thanks for your help

georg

--- In bb4w@..., "atomisermusic" <breaks@...> wrote:

I sent a post on Monday about this but it doesn't seem to have shown up- apologies for any duplication.

I am a lot further on with MySQL than I was last week, mainly thanks to downloading Jon's version of libmysql.dll and using it to overwrite my previous copy. Once I'd done that, the crashing pretty much stopped, and once I got told the correct password for the MySQL server (human error, and not mine...) I was able to connect and start querying to my heart's content.

Thanks again to Richard and to Jon for the suggestions. I did respond to them more thoroughly before, and may have to again if this message doesn't show up...

Stuart

--- In bb4w@..., Jon Ripley <jon@> wrote:
It could be that your copy of the MySQL library is out of date.
Download the full MySQL example from my site at:
http://jonripley.com/volatile/MySQL.zip

Richard Russell
 

--- In bb4w@..., "georg_wissinger" wrote:
i got an error trying to open a connection to mysql with bbc basic
for windows.
"Client does not support authentication protocol requested by
server; consider upgrading MySql client."
The likelihood is that you're using an old version of 'libmysql.dll'. What I presume to be the latest version can be found here:

http://www.mysql.com/downloads/mirror.php?id=377978#mirrors

Click on 'No thanks, just take me to the downloads!' and download mysql-connector-c-noinstall-6.0.2-win32.zip

Richard.

georg_wissinger
 

Hi,

new error-message: "Address out of range at line 240"
can you help me?

thanks
georg

--- In bb4w@..., "Richard" <yahoo@...> wrote:

--- In bb4w@..., "georg_wissinger" wrote:
i got an error trying to open a connection to mysql with bbc basic
for windows.
"Client does not support authentication protocol requested by
server; consider upgrading MySql client."
The likelihood is that you're using an old version of 'libmysql.dll'. What I presume to be the latest version can be found here:

http://www.mysql.com/downloads/mirror.php?id=377978#mirrors

Click on 'No thanks, just take me to the downloads!' and download mysql-connector-c-noinstall-6.0.2-win32.zip

Richard.

Richard Russell
 

--- In bb4w@..., "georg_wissinger" wrote:
new error-message: "Address out of range at line 240"
can you help me?
Seeing the code would help!

If that error message has arisen as a result of updating to the latest 'libmysql.dll' it's suggestive that the API has changed so that a function you were previously calling no longer exists.

The MySQL 5.5 'C' Function Reference is here:

http://dev.mysql.com/doc/refman/5.5/en/dynindex-cfunction.html

Is the function you are calling in 'line 240' listed there?

For a more in-depth diagnosis you may need to ask Jon Ripley.

Richard.

Edwin Mendey
 

One question, where have all the files gone.  I am trying to download the files without success.

Richard Russell
 

On Mon, Aug 28, 2017 at 04:02 am, Edwin Mendey wrote:
One question, where have all the files gone.  I am trying to download the files without success.
If you are referring to files at Jon Ripley's personal web site, very sadly he took that down several years ago.  He (understandably) was never happy about them being mirrored elsewhere, so once his site disappeared so did the files.  I had thought that Jon himself had also vanished, at least from BB4W groups, but not so long ago he posted a message here, so if by chance he reads this perhaps he can update us.

On the other hand if you're referring to files that were stored at the old Yahoo! group, I can still access those and transfer them to the Files section here, if you tell me what you want.

Richard.

Previous Topic Next Topic