r3wp [groups: 83 posts: 189283]
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

World: r3wp

[MySQL]

amacleod
24-May-2010
[1200]
I signed up with a web provider but they do not seem to allow remote 
direct access to the mysql db unless you specify the ip that first...which 
is no good if you have an app used by many people in any number of 
locations....


Is this standard procedure? I had the same problem with another provider.

What is the way around this? CGI?
Graham
24-May-2010
[1201x2]
Eh??
A web provider only provides access to mysql from a script ... and 
you will have a static ip for your web host.
TomBon
24-May-2010
[1203x2]
amacleod, CGI, yes or use rebservice if you allowed to start a port 
and forward the sql request to doc's mysql sheme as localhost 

or switch to a virtual server with linode or slicehost for full control.
amacleod, CGI, yes or use rebservice if you allowed to start a port 
and forward the sql request to doc's mysql sheme as localhost 

or switch to a virtual server with linode or slicehost for full control.
amacleod
24-May-2010
[1205x4]
I'm serving the db from my own p for this host as there is server 
for now as bandwidth is not an issue yet. I signed unlimited bandwidth, 
storage, email accounts, mysql db's etc. for a few bucks a month. 

I was just testing mysql for possible use down the road.


When the time comes i will probably go the "linode" route as iwould 
want to use Cheyenne too and no provider is going to let you run 
that.
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?
Henrik
25-May-2010
[1209]
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.
caelum
24-Aug-2010
[1210]
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.
amacleod
24-Aug-2010
[1211x2]
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.
caelum
24-Aug-2010
[1213]
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.
amacleod
24-Aug-2010
[1214x2]
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.
ChristianE
25-Aug-2010
[1216]
Have you checked whether MySQL is configured properly to accept tcp-requests 
instead of only socket-based connections?
caelum
25-Aug-2010
[1217]
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.
ChristianE
25-Aug-2010
[1218]
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.
amacleod
26-Aug-2010
[1219]
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.
caelum
26-Aug-2010
[1220x2]
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!
amacleod
27-Aug-2010
[1222]
great!
amacleod
20-Oct-2010
[1223x2]
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?
Andreas
20-Oct-2010
[1225]
You are using MySQL 4.1+?
amacleod
20-Oct-2010
[1226]
I believe its 5.1.30
Andreas
20-Oct-2010
[1227x3]
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.
amacleod
20-Oct-2010
[1230]
Might that be a burden on hte server for very large queries?
Andreas
20-Oct-2010
[1231x3]
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.
amacleod
20-Oct-2010
[1234x2]
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?
Andreas
20-Oct-2010
[1236]
Precisely.
amacleod
20-Oct-2010
[1237]
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?
Andreas
20-Oct-2010
[1238x2]
Yes.
Which should be easy, if you have the right functions available :)
amacleod
20-Oct-2010
[1240]
Thanks, I thought there might be some Mysql command to insert column 
names with the data...

THanks for your help.
Andreas
20-Oct-2010
[1241]
There might well be.
Dockimbel
20-Oct-2010
[1242]
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)
Gregg
20-Oct-2010
[1243]
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}]}
GrahamC
22-Sep-2011
[1244]
mysql:// question at http://synapse-ehr.com/forums/showthread.php?454-mysql-problem-what-does-this-error-mean
Dockimbel
22-Sep-2011
[1245]
The link is not working for me, I get a "Invalid Thread specified. 
If you followed a valid link, please notify the administrator".
GrahamC
22-Sep-2011
[1246x2]
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
Gabriele
22-Sep-2011
[1248]
Maybe he's using an older version of the mysql driver?
GrahamC
22-Sep-2011
[1249]
I'll ask ...