World: r3wp

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

here is updated wrapper, http://reboot.ch/rebol/mysql-wrapper.txt

Will, what does the wrapper do compared to the MySQL driver?

it is just a utilities wrapper that works on top of the mysql-protocol.r, 
it makes queriing mysql more rebolish

;without wrapper:
do http://softinnov.org/tmp/mysql-protocol-41.r
db: open mysql://localhost/db1
nodes: send-sql/named {SELECT `id` `name` `data` FROM `node`}

node: send-sql/named {SELECT `id` `name` `data` FROM `node` WHERE 
`id`='1'}

;with wrapper:
do http://softinnov.org/tmp/mysql-protocol-41.r
do http://reboot.ch/rebol/mysql-wrapper.txt
.db/databases: [
	db1 mysql://localhost/db1
	db2 mysql://localhost/db2
]
.db/use 'db1
nodes: .db/get/all 'node [id name data] none
node: .db/get 'node [id name data] [{`id`=?} 1]
;if you follow the rule to name your primary-key "id",
;you can use this shorter version:
node: .db/get 'node [id name data] 1

;/debug is your friend, use it to see the generated query

.db/get/debug 'node 'id 1   
;   "SELECT `id` FROM `node` WHERE id=1.0 LIMIT 0,1"

thanks - is there a page somewhere describing it in more detail?

sorry it is still rought and there is no docs, thought some comments 
in the code exists, I use it in production so, solid it must be.

and I will be glad to answer any questions and add feature requests 
8)

use /debug it will print query to the console without sending it 
to the server so you can play and understand how it works

just installed mysql on ubuntu 8.1
but can't login ...

get "Access denied for user ['root'-:-'localhost'] (using password: No 
)

I thought root was automatically created with no password??