World: r3wp

Here is a simple example but basically you just create a script that 
reads from the DB locally and prints. Your remote app reads the printout 
when it reads from the page:

#!/user/cgi-bin/REBOL -cs
REBOL [Title: "Get Roster"]

do %mysql-protocol.r

db_ip: mysql://user:[password-:-localhost]:3306/bighouse

db: open db_ip
insert db "SELECT * FROM roster"
		dat: copy db
close db

print dat

Place this file in your cgi-bin and have your remote client 'read' 
the url to that file.  


Don't forget to place mysql-protocol.r somewhere on the server an 
'do' it.


you could 'encript' the output with encloak using a simple key if 
some security of the data is needed. Just decloak at the remote client 
with same key.

Have you checked whether MySQL is configured properly to accept tcp-requests 
instead of only socket-based connections?

Thanks amacleod I'll give that a try. ChristianE, I don't see any 
MySQL settings like that on my cpanel, which I am familiar with. 
I don't know if my ISP allows me to access such things. I've never 
seen that kind of information about MySQL in cpanel. How would I 
check? I am also looking for a virtual server where I can set everything 
myself in order to use Rebol to access databases and create HTML. 
If possible I want to eliminate PHP and use Rebol as my standard 
web interface program, but until I can get Rebol reading/writing 
to my databases I am stuck.

If the SKIP-NETWORKING config entry is enabled, MySQL won’t listen 
for TCP/IP connections at all. That's a security setting. All interaction 
with MySQL must then be made via Unix sockets. Whereas the mysql-driver 
only supports TCP connections. I don't no about CPanel, but look 
out for some CPanel setting like "skip networking" or alike.

Caelum, instead of 'read' use 'load' when your remote client accesses 
teh cgi script. That will give you your data back in rebol blocks 
intead of text.

ChristianE, I could not find the SKIP-NETWORKING config entry anywhere 
on cpanel, but after reading some tech docs from my ISP I conclude 
they have it set to only allow database access via Unix sockets, 
so I guess I will have to live with that.

amacleod, Progress: After a lot of messing around I got that script 
working. So now I can access my databases using Rebol. NICE!

great!

What is the SOP for building json objects from MySQL queries? 

I saw this but I could not get it to work:

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

Anyone know if  that is supported with mysql-protocol?

You are using MySQL 4.1+?

I believe its 5.1.30

Ok.

In any case, I'd probably just do plain queries and construct the 
JSON from the resultset.

http://www.json.org/json.rwill help you with the encoding.

Might that be a burden on hte server for very large queries?

Makes no difference.

Either you transfer a very large string which you build up within 
MySQL. Or you transfer a very large result set and build up the string 
in REBOL.

Also note that the above approach with CONCAT/GROUP_CONCAT does not 
help you with JSON-specific encoding. So changes to you'll in fact 
create wrong JSON are rather high.

I used json.r.
New to json....
	I'm going to want an json object right?

If a query gives me a rebol block of blocks and I send it through 
json.r I don't get a json object.

Should I work on converting the query to a rebol object first?

Precisely.

ok, so there is no easy way to do it...I justs need to loop through 
each rebol block and insert the field name before the data and then 
make objeet?

Yes.

Which should be easy, if you have the right functions available :)

Thanks, I thought there might be some Mysql command to insert column 
names with the data...

THanks for your help.

There might well be.

amacleod: NAME-FIELDS function might help : http://softinnov.org/rebol/mysql-usage.html#sect11.
(it works on a single record only, so you'll need to loop through 
the recordset anyway)

You should get back a top-level JSON object, but nested blocks will 
be arrays, not objects. e.g.


>> rebol-to-json [a: 1 b: 2 c: [d: 4 e: 5] f: #[object! [g: 6 h: 
7]]]

== {["a", 1, "b", 2, "c", ["d", 4, "e", 5], "f", {"g": 6, "h": 7}]}

mysql:// question at http://synapse-ehr.com/forums/showthread.php?454-mysql-problem-what-does-this-error-mean

The link is not working for me, I get a "Invalid Thread specified. 
If you followed a valid link, please notify the administrator".

mysql problem what does this error mean?

    Hello,
    I am just looking at REBOL and trying to access mysql
    I got this error??
    ===================================
    connecting to: 127.0.0.1

    ** User Error: ERROR 1251 : Client does not support authentication 
    pro
    tocol requested by server; consider upgrading MySQL client
    ** Near: db: open mysql://[rootass-:-127-:-0-:-0-:-1]:3306/mysql
    ==============================================
    This the code I am using from rebol document
    Rebol[
    title: "Rebol Mini-Text Database with Visual GUI"

    author: "http://reboltutorial.com/blog/rebol-mini-text-database/"
    version: 1.0.0
    ]
    do %mysql-protocol.r
    probe first system/schemes
    db: open mysql://[rootass-:-127-:-0-:-0-:-1]:3306/mysql
    insert db {
    DROP TABLE IF EXISTS products;
    CREATE TABLE products (
    name VARCHAR(100),
    version DECIMAL(2, 2),
    released DATE
    );

    INSERT INTO products VALUES ('cheyenne', '1.0', '2007-05-31');
    INSERT INTO products VALUES ('mysql', '1.1', '2007-05-01');
    }
    insert db read %setup.sql ;-- execute a big SQL file

link above works for me

Maybe he's using an older version of the mysql driver?

I'll ask ...

It looks like the MySQL driver he's using is too old for his MySQL 
server. He should try with the latest version for newer servers: 
http://softinnov.org/tmp/mysql-protocol-41.r


I haven't made it official as it does not work for older servers 
IIRC, and I also never found the time to fully test it with more 
recent ones.  But it should work correctly.

Ok, posted

The problem could be this as well: http://dev.mysql.com/doc/refman/5.5/en/old-client.html

when you upgrade your mysql server from 4.x to 5.x the passwords 
in your "mysql" table for users stays old, which hashed a different 
algorithm.. so you cannot connect it using username/password anymore.

I think the link didn't work because I discovered it was a moderated 
thread.

Doc, what a lifesaver! I just spent the last 7 hours trying to figure 
out what was wrong with my remote rebol-based mysql app. It stopped 
working after I upgraded mysql. I spent most of my time messing around 
with mysql until I narrowed it down to the protocol.
 
sql-protocol-41.r Addresses: 

User Error: ERROR 1251 : Client does not support authentication protocol 
requested by server; consider upgrading MySQL client  (When using 
newer long hash passwords) 
and

User Error: ERROR 1043 : Bad handshake  (when using older short hash 
passwords) 

Thank you for posting this. I was getting to the point of panic.

Glad to hear that my driver helped you solve your issue. :-)