World: r3wp

(change the user/pass/db etc)

Oh boy! Now we're getting into some good stuff! Sorry, I have to 
leave for an hour or so. I will try that as soon as I get back. Thanks....

Here are the results: >> do load http://powerofq.com/start
connecting to: powerofq.com
>> load "mysqlprot"
== mysqlprot
a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"
>> a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"

** Access Error: Invalid port spec: mysql://tim:[password-:-localhost]/hs4ushow 
tables

** Near: a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"

Tim: MySQL 4.1 uses a new encryption method for user passwords, try 
add a new user assigning the password with the function OLD_PASSWORD('pass') 
rather than PASSWORD('pass'), and attempt   open mysql:// ... etc. 
 with the new user. Hope this helps.

Do you have a syntax example of that? I added users with the GRANT 
...... IDENTIFIED BY ..... approach

I don't remeber the syntax exactly, wait me a minute...

I googled it and added the user as per the OLD-Password syntax. Now 
I tried a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"
and got the same error message

Sorry,I did not read all messages... Why are you using 'read rather 
than 'open ?

:-) following instructions trying: open mysql://tim:[pwd-:-localhost]/hs4u 
getting ** Access Error: Invalid port spec: mysql://tim:[pwd-:-localhost]/hs4u
** Near: open mysql://tim:[pwd-:-localhost]/hs4u

I'm going to try it with the mysql-protocol module that I am using 
on my desktop. Just a minute...

worked from the console, but I'm not getting in with the cgi program. 
hmmm ....

Bolixed my code that's why. I am in! Alberto, thank you very much. 
I really appreciate the tip. You made it work for me.... cheers tj

You are wellcome :-)

any idea how to get(read) a string from a integer pointer, delivered 
by mysql.dll ? is there something like  - get-string  :integerpointer 
?

declare it as string!, not integer!.

yes gabriele, it works fine for this ->  char *mysql_info(MYSQL *mysql) 
 points to a char, but this -> MYSQL_RES *mysql_list_tables(MYSQL 
*mysql, const char *wild) delivers the integer pointer again as string. 
I know I have to provid a struct! which will be filled by passing 
the pointer to the routine but can I pass just a empty block instead 
a struct! ?, or better pass the recieved integer pointer later to 
a block ?

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