World: r3wp

Qtask might do this in the future...

Never used mysql before .. and just downloaded Nenad's driver.

Getting this ...

>> insert db "select * from patientrecord"
== none
>> result: copy db
** Script Error: Invalid argument: 0000-00-00
** Where: convert-types
** Near: change at row 3 to
>>

looks like some of the patient records have 0000-00-00 as the date 
of birth in them.

ok, this works round it

 insert db "select cast( birthdate as char) from patientrecord"

ummm.. how do you do a select on a field where the field name has 
a space in it?

Why would anyone put a space in a field name??

docs say \_ is a space

>> insert db "select middle\_name from patientrecord"

** User Error: ERROR 1064 : You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server ve
rsion for th...
** Near: insert db "select middle\_name from patientrecord"

ok, worked it out

 insert db "select `middle name` from patientrecord"

MySQL driver v1.2.1 released. See more at http://softinnov.org

Thanks for fixing the date zero error.

Thank you Dock!

For a long time, I was reluctant to change the date zero behaviour 
(error throwing), because this "feature" of MySQL look, to me,  like 
very bad design. But recently, I hit the problem too when I inserted 
badly formed dates in a table and wasn't able to read the records 
anymore with my driver.

Version 1.2.1 re-released. There was a small error in the 'name-fields 
function preventing it from working. Thanks to Will for reporting 
it.

James: sorry for the delay, still not found time for docs but her 
it is "as is.."  /debug is your friend, get started, load Dock driver 
, then load  http://reboot.ch/rebol/mysql-wrapper.txtthen:
;set a list of connections:
.db/databases: [
  ;local
  mydb1.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb1
  mydb2.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb2

  ;online
  mydb1 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb1
  mydb2 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb2
]
;use a db (if not open will open it automatically):	
.db/use 'mydb1

Thanks Will.

.

I've just upload a new experimental 1.3 version of MySQL driver supporting 
mutiple queries, stored procedures and multiple result sets. Reading 
mutiple results is done by calling COPY for each result. Download 
at http://softinnov.org/tmp/mysql-protocol-41.r

Keep in mind that if you're using SEND-SQL, it already does a implicit 
COPY.

It should be considered as a early beta, so test it well before using.

good news!

Thank Gabriele for keep pushing me. ;-)

Thank you! 8-))

the above new version seems to work fine for simple tests, apart 
from the issues Nenad has already listed.

this is going to be very useful :-)

BTW, Maarten told me that he knows for sure that you can get the 
number of result sets in advance, if he doesn't show up here (he 
can't be much in front of a computer these days), maybe you could 
email him to get more info.

(he can read the mail address listed here on altme from his ipod 
touch)

I'm here. I think there is a C API call for the number of result 
sets. Primitive, but this is how to do it: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Thanks for the link, that confirms that's possible to detect the 
last result set. I'm keep getting "more results"' status even for 
the last set, but now I think that it's caused by a bug in my code. 
I'll look at it this week end (too busy until there).

Maarten, you're working too much :) Nenad: thanks! If I have some 
time, I'll have a look too, though I will just be making guesses. 
:)

New version 1.3 of new MySQL driver released as beta. (same URL).

Changes:


- Fixed the multiple result sets end-of-stream issue. Now an extra 
COPY will
  return NONE, marking the end of the data stream.

- Fixed error message parsing for protocol 4.1.


- Added SQL request delimiter property to port/locals/delimiter (default: 
#";")

It now passes all my non-regression tests, but I've done several 
deep changes, so I'll consider it beta until the end of the year 
before declaring it the new official version.

Here's an example showing the new possibilities combined (multiple 
queries support, setting custom delimiter and multiple results support) 
:

db: open mysql://[root-:-localhost]/mysql
db/locals/delimiter: "*;*"
insert db "show tables; show databases"
copy db ; <= returns tables block!
copy db ; <= returns databases block!
copy db ; <= returns none!

Enjoy ! :-)

It looks, that there is something wrong with MySQL procedures:
using mysql.r
mysql> CREATE PROCEDURE myproc() SELECT 'it works!';
mysql> call myproc();
+-----------+
| it works! |
+-----------+
| it works! |
+-----------+
1 row(s) in set
mysql> call myproc();
mysql> 

On the second run it returns nothing.

using it in cheyenne gives error:
Error Code :  	800
Description : 	user error !

ERROR 1312 : PROCEDURE webcore.myproc can't return a result set in 
the given context
Near : 	[do-sql 'webcore "call myproc();" txt 3195]
Where : 	none

the cheyenne error is probably because you need the new version of 
mysql:// that Doc posted a few days ago

Oldes: are you sure you're using the 1.3 beta mysql:// driver ? You 
can find it here : http://softinnov.org/tmp/mysql-protocol-41.r

Calling stored procedures is not supported in driver versions < 1.3.

I thought I'm, but the version was older.. now it's working fine:)

But there must be a bug anyway .... the first one shows that..  next 
sql query after the procedure call returns none.

I've send more info into private in R3-alpha... anyway.. I wonder 
where it's possible to find some mysql protocol spec. Do you just 
do reverse engineering?

found it http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

I used to reverse-engineer, but since 5.0(?) MySQL AB documents the 
protocol with sometimes (intentional?) blocking errors.

After analyzing the report you sent to me about issue with sproc. 
MySQL has a odd behaviour, it seems to always return 2 result sets 
for a sproc even when you're expecting only one (the second will 
then be empty). So, after calling a sproc you have to call COPY twice 
(or once after a SEND-SQL) to flush the remaining data. I'm looking 
in the driver to see if I can automate this flushing process.

Improved version of MySQL driver 1.3 beta released at http://softinnov.org/tmp/mysql-protocol-41.r

Changes:


- Improved automatic flushing of unread data. Now you can forgot 
the second COPY
  on sproc calls (should be robust, but needs testing).
  

- Multiple SQL requests are now sent as one packet by default. No 
more need to
  change the db/locals/delimiter value for 4.1+ servers.

I might drop out the SQL requests splitting code (and also the delimiter 
option) from the driver as now everything is sent as-is to the server. 
It might still be useful for big SQL files, not sure what the best 
strategy should be in such case (send file as one big packet, or 
split it and send each request one by one) ?

Bugfix revision for MySQL driver 1.3 beta at http://softinnov.org/tmp/mysql-protocol-41.r

Changes:


- Fixed a regression bug appearing when trying to open a connection 
without a database name.

- Fixed "port not open" error after automatic reconnection.

The change I did on [Fri 21:21] version on the way SQL requests with 
multiples statements are sent to the server, might not be a good 
idea for sending big SQL batch files to the server.  The previous 
method (slicing SQL requests and sending them one by one to the server) 
wasn't that bad (could allow streaming the reading of a big SQL file 
from disk). Maybe it should be good to let the user choose how the 
driver should send multiple SQL queries.

Thanks Dock! 8)

http://softinnov.org/tmp/mysql-protocol-41.r