World: r3wp

i think you should declare the return value as a struct. how is MYSQL_RES 
defined?

anyway, have a look at http://www.compkarori.com/vanilla/display/peek_and_poke.r

maybe it can be useful to you.

Dummy question: is it possible to insert more than one SQL statement 
in one time ?
ie:

insert mysql-db {INSERT INTO table VALUES (1, "USA"); INSERT INTO 
table VALUES (2, "FRANCE"); INSERT INTO table VALUES (3, "ITALY")}

This works with some SQLs -- not trie dit with mySQL
 INSERT INTO table
   select (1, "USA")
   union all  select (2, "FRANCE")
   union all  select (3, "ITALY")

According MySQL doc, I can use this syntax for INSERT :

INSERT INTO table VALUES (1, "USA"), VALUES (2, "FRANCE"), VALUES 
(3, "ITALY")

But I can't do that for UPDATE !!

So I need to know it the Rebol MySql protocol allow to pass several 
SQL statements in one time.

I tend to just loop it, but that may not be very efficient.

OK, this is the full problem :

Apologies on Insert -- I'd assumed from the fact you'd asked the 
question that MySQL did not support the Values syntax.


Update is trickier as you are potentially changing a whole load of 
values.

Best way is to have the update data in a 2nd table, and then select 
as appropriate.

I have a table with some Products.

I need to copy each line from the Products table in a Command table 
(with a different structure, but same key) IF THEY ARE NOT ALREADY 
THERE, and add a quantity value.

Actually, I :
- select the lines form Products table

- Loop on the result to INSERT each line in the Commands table, with 
IGNORE option to not replacing already there line.
- Loop again on the Commands line to UPDATE the quantity

I could use the REPLACE statement, but doc said that it's an heavy 
instruction.
Is there an "UPDATE-IF-THERE-ELSE-INSERT" statement somewhere ?

This is the way I'd do it -- don't know if it'd work for mySQL (assumes 
product is identified by 'prod-code)
insert  ignore into Commands
             select * from products;

update commands 
    where not prod-code in 

         select prod-code  from product  where command.prod-code = products.prod-code
          set quantity = ????;

Huu, I need to learn SQL more deeply !!

I think I mean 
   where prod-code in
not NOT in
(the code is not tested, and may not be possible in mySQL)

Joe Celko's SQL for Smarties is probably the best book for getting 
up to speed.

dunno if newer mysql supports subselects, but it didn't in the past

4.1 seems to support them. Dunno if they work though :-)
http://dev.mysql.com/doc/mysql/en/subqueries.html

I need to learn SQL more deeply

 A broad introduction can be found here: http://riki-lb1.vet.ohio-state.edu/mqlin/computec/tutorials/SQLTutorial.htm

I'm sure one of you guys knows how to check so that I can avoid:
** Access Error: Port rhm not open
** Where: read-rows
** Near: a: copy db

I think this happens after I have inserted a query and there are 
no results, but in any case I'd like to able to check a port to see 
it is open.

I should add that the code looks like this ...and it works when a 
record is found:
    insert db reduce [s]
	a: copy db

while copy db

foreach record copy db [ probe record ]

Graham, thanks. So this would check if there was something returned 
by the query. I'll check it out.

I have a problem when the connection times out and mysql-protocol.r 
wants to reconnect. It looks like it hangs, but on closer inspection 
with trace/net on just gives an infinite amount of low-level reads 
of length 0 bytes.

I can close and reopen the connection, and it works again, but I 
can only do this 5-6 times before it refuses to connect to the mysql 
server. Is this a known problem?

You can try to disengage the auto-reconnect feature and test if the 
connection is still active by yourself (send a ping command for example).

db-port/locals/auto-ping?: off (stop pinging the server and don't 
reconnect automatically).

Henrik: I hit the same problem just at this moment:( unfortunatelly 
I don't know how to fix it

But I'm not sure if it's not problem of the MySQL itself (for example 
some way how to awoid attacks

I found a different solution: manually check if the connection has 
timed out 30 seconds after a query and then reconnect. it's not perfect 
since I have to check at every query, but it works to the extent, 
I've tested it

how you do the timeout test?

after the query I store 'now. the next time I do a new query I check 
'now against the stored one. if the difference is more than 30 seconds, 
I close the db connection and open it again immediately

that would not solve my problem

my problem was that after about 30 queries it got to infinitive loop 
(low-level reads of length 0 bytes)

What's your server version ?

MySQL server version: 3.23.37

an atomic way of returning the value of an AUTO_INCREMENT column 
on insert  would be very nice.

i.e. 

insert db "insert  into tab(tab_id,tab_name) values(NULL,'alt_key')"
tab-id: copy db


would gaurentee to give you the correct tab_id   as it looks now 
you have to:


insert db "insert  into tab(tab_id,tab_name) values(NULL,'alt_key')"
;;; hope no one else is insertintginto the table right now
insert db  "select LAST_INSERT_ID()"
tab-id: copy db

even being able to issue more than one sql statement would help some

insert db {insert  into tab(tab_id,tab_name) values(NULL,'alt_key'); 
select LAST_INSERT_ID()}
tab-id: copy db

Tom: the select above is actually safe. MySQL keeps the last insert 
id on a per-connection basis

this means, that even if someone else inserts a record between your 
two queries, you'll get the correct result.

each connection gets its own last insert id.

thanks Gab, that makes me feel better

Hello, is there anyone working with Rebol Command and Mysql 4.1.x 
? It appears that the authentication protocol has changed and Rebol/Command 
can not connect to MySQL 4.1.x. But it works fine with MySql 4.0.x.

have you tried free mySQL driver from DocKimbel?

I have heard that the driver from DocKimbel has the seem issue... 
But my point is that Rebol/Command should be updated to work with 
version 4.1 which is currently the recommended version for peoduction 
environment!

Most probably driver support for db will be drop in future versions. 
(I recommend working with DocKimbel of helping improve his code).

(Disclaimer: This is my opiong not RT official position)