World: r3wp

that makes no sense!!! I wrote all over my own message

I'm using my own server for now until bandwidth becomes an issue.

I think I will need to convert my app over to a cgi based access 
for mysql as I may want to access them from non-rebol based clients 
(smart phones).


Does anyone know what is the standard method for say iphones to access 
data from servers? CGI? or is there other prefered method?

I usually build a server script to access the database via procedures 
needed for the app. They then communicate via JSON. Works pretty 
well and eliminates the need for client side SQL, and it works with 
anything that can POST to a webserver.

Hi MySQL Group, I program in C, Fortran, Cobol, Superbase4, PHP, 
MySQL and some other languages but I'm fairly new to Rebol. I am 
attempting to connect to a remote MySQL database by following the 
intstructions here http://reboltutorial.com/blog/mysql/.I have downloaded 
the mysql-protocol.r file and run it in my Rebol program but I keep 
getting this error 'Access Error: Network timeout'. What does that 
mean? How do I fix it? Any help appreciated.

Sounds like you are not connecting to the server...try pinging it 
first. Check the port.

Shows us the code you aer using.

Is the Mysal server on your local machine? or on a hosted machine? 
Sometimes the host company will not allow you to directly acess the 
server from remote locations. You would need to access it via CGI.

I pinged the server just fine. Here is the code:

REBOL []
#include do %mysql-protocol.r

results: read rejoin [mysql://mysqluser:[mypassword-:-mysite-:-com]:22/mydatabase 
["SELECT * FROM tablename"]


The MySQL server is on a hosted machine. In cpanel I added my IP 
address to the 'Remote MySQL' Remote Database Access Hosts list. 
I think you are right that I will need to access the database from 
the cgi because the hosting company will not allow direct access, 
even though its suppossedly allowed in cpanel.

Thanks for your help.


Are there any examples of accessing a MySQL database via CGI in Rebol? 
I just googled and found nothing.

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.