AltME groups: search

I'm here. I think there is a C API call for the number of result 
sets. Primitive, but this is how to do it: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Thanks for the link, that confirms that's possible to detect the 
last result set. I'm keep getting "more results"' status even for 
the last set, but now I think that it's caused by a bug in my code. 
I'll look at it this week end (too busy until there).

Maarten, you're working too much :) Nenad: thanks! If I have some 
time, I'll have a look too, though I will just be making guesses. 
:)

It looks, that there is something wrong with MySQL procedures:
using mysql.r
mysql> CREATE PROCEDURE myproc() SELECT 'it works!';
mysql> call myproc();
+-----------+
| it works! |
+-----------+
| it works! |
+-----------+
1 row(s) in set
mysql> call myproc();
mysql> 

On the second run it returns nothing.

using it in cheyenne gives error:
Error Code :  	800
Description : 	user error !

ERROR 1312 : PROCEDURE webcore.myproc can't return a result set in 
the given context
Near : 	[do-sql 'webcore "call myproc();" txt 3195]
Where : 	none

the cheyenne error is probably because you need the new version of 
mysql:// that Doc posted a few days ago

But there must be a bug anyway .... the first one shows that..  next 
sql query after the procedure call returns none.

After analyzing the report you sent to me about issue with sproc. 
MySQL has a odd behaviour, it seems to always return 2 result sets 
for a sproc even when you're expecting only one (the second will 
then be empty). So, after calling a sproc you have to call COPY twice 
(or once after a SEND-SQL) to flush the remaining data. I'm looking 
in the driver to see if I can automate this flushing process.

Bugfix revision for MySQL driver 1.3 beta at http://softinnov.org/tmp/mysql-protocol-41.r

Changes:


- Fixed a regression bug appearing when trying to open a connection 
without a database name.

- Fixed "port not open" error after automatic reconnection.

The change I did on [Fri 21:21] version on the way SQL requests with 
multiples statements are sent to the server, might not be a good 
idea for sending big SQL batch files to the server.  The previous 
method (slicing SQL requests and sending them one by one to the server) 
wasn't that bad (could allow streaming the reading of a big SQL file 
from disk). Maybe it should be good to let the user choose how the 
driver should send multiple SQL queries.

it is just a utilities wrapper that works on top of the mysql-protocol.r, 
it makes queriing mysql more rebolish

thanks - is there a page somewhere describing it in more detail?

What does read dns://127.0.0.1 give you? It may be related to a mismatch 
in the localhost name mapping, so you have to add access rights for 
root@... (or [root-:-127-:-0-:-0-:-1]) in MySQL to workaround that issue.

I think that someone had setup a password ... so I purged mysql, 
deleted my.cnf and reinstalled.  That worked.

I'm trying to upload a binary (image) file to a mysql DB. When I 
retrieve it teh binary data seems to have changed and I can not display 
the image. I was able to do it with sqlite with no problem.
I'm using the mediumblob field type for the image data... 
Any ideas what I might be doing wrong?

btw, I suggest not to store images in the db, store it somewhere 
on your hardrive and put in the db a pointer to the file, if you 
really need it and do not find ehat's wrong, ping me in the weekend 
and I can test that here, althougt I use other binary data with mysql-protocol 
with no problem, not sure if it apply but check also encodings for 
mysql storage, etc

I got it....
I have to convert it back to binary. ("to-binary") 

I assumed that if I was giving it a binary file it would remain in 
binary just as sqlite treated it....The field attributes state "binary".

Thanks for the help, Will!

Is there a MySQL guru around ? I need to optimize this query:


SELECT A.cod, A.date , SUM(B.amount) AS amount, COUNT( * ) AS numrow 
FROM A INNER JOIN A AS B ON A.cod=B.cod AND B.date <= A.date WHERE 
A.cod IS NOT NULL GROUP BY A.cod, A.date


This return "running sums" (partial sum for every date and every 
cod). 
Both cod and date are indexed.


In a table of about 100'000 records it takes 104 sec to complete 
while in SQL server 2005 (the same query, on the same data, on the 
same index ) it takes 3 sec !

I've tried to use MyIsam, InnoDB, MEMORY storage, and used BOTH btree 
and hash index.
I've tried to FORCE Index  for Join and Group too.

Any suggest ?

is there any reason why you join A on A? also.. have you tried to 
EXPLAIN the query? http://dev.mysql.com/doc/refman/5.0/en/explain.html

> is there any reason why you join A on A?

If I have these records:

cod 	date		amount
A	2009/03/01	10	
A	2009/03/03	30
A	2009/03/04	20
A	2009/03/07	5
B	2009/03/02	17
B	2009/03/10	5


That query give me the sum of previous amounts for every date/cod: 

cod 	date		amount	NumRow
A	2009/03/01	10		1	
A	2009/03/03	40		2
A	2009/03/04	60		3
A	2009/03/07	65		4
B	2009/03/02	17		1
B	2009/03/10	22		2		

I don't know if there's a better method without  using join

> also.. have you tried to EXPLAIN the query?


Yes, explain returns that  the correct indices are used. Not very 
informative.

Davide, first: NULL value are evil (as are duplicate rows).My guess 
the cost is the fact that your query probably runs a fulll table 
scan as it needs to sum all of the rows... So table partitioning 
will help a lot. My guess is also that Oracl, SQL Server and perhaps 
PostGreSQL are smarter with their table optimziers and query rewriters. 
HTH

> My guess is also that Oracl, SQL Server and perhaps PostGreSQL 
are smarter with their table optimziers and query rewriters


Postgres is about 2 times faster than MySQL in this query, but the 
execution time grow alot as num of records increase. 
So I really don't know how good is compared to MySQL.
Oracle, I would try, but I have no time :-P

> Also, change the count(*) to count(1)

Thanks, good one.


The real tables will be more large (5 M record or more), so small 
optimizations would be not sufficient
I'm tryng a different approach, using one simple stored function:

create function running_total (cod VARCHAR(50), adder DECIMAL)
RETURNS DECIMAL
	BEGIN
		IF @last_cod <> cod THEN
			BEGIN
				SET @running_total = 0;
				SET @last_cod = cod;
				SET @num_row = 0;
			END;
		END IF;
		SET @running_total = @running_total + adder;
		SET @num_row = @num_row + 1;
		RETURN @running_total;
	END

and using as select:

SET @last_cod = ''; 
SET @running_total = 0; 
SET @num_row = 0; 

SELECT cod , date, running_total(cod , amount), @num_row FROM a ORDER 
BY cod, date;


This approach seems really fast : processing and inserting 100'000 
records  took less than 1 sec. instead of  281,73 sec. with SQL join

I have a new mysql server but I can't connect to it with %mysql-protocol 
v1.2.1 :

>> open mysql://user:[traiteur-:-localhost]/testjmg
connecting to: localhost

** User Error: ERROR 1251 : Client does not support authentication 
protocol requested by server; consider upgrading MySQL client
** Near: open mysql://user:[traiteur-:-localhost]/testjmg


I know I have to change something in the mysql server configuration, 
but does anyone can point me to what it is ?

It's MySQL - 5.0.51a
Do you know how to do that ?

I'm using 5.0.18 and don't have such issues. Anyway to find a fix, 
have a look here : http://dev.mysql.com/doc/refman/5.1/en/old-client.html

Ok, it works now. My bad, I put the last protocol in a folder but 
I where still loading the old one from another folder !

Can you create a database remotely?

Anyone know the syntax?

I tried:
insert db [create-db "test2"]

with db=port but does not seem to work

Is there a limit to the number of tables in a database?

Anyone know SOP for this situation:

 I have hundreds of users (not yet really) )and I need to store several 
 tables of user data for each. Should I create a seperate database 
 for each user or is it better to use a naming scheme for the tables 
 and store all of them in one database? or is it just personal preference?

I think there is no limit on number of tables. A database in MySQL 
is implemented as a directory containing files that correspond to 
tables in the database.

insert db [create-db "test2"] works flawlessly here....You've probably 
connected to the server with a user that didn't had enough rights 
for creating databases, but it's hard to figure out from a "does 
not seem to work" issue description.


I start to better understand Carl's willings for filtered communication 
channels that improve the signal to noise ratio in order to save 
some valuable working time...

The DDL in question is create table statements where some obscure 
semantic rule is violated. By obscure, I mean it took me a day to 
track down the error in the MySQL manuals.

You should try with the official command line mysql client. If you 
notice a different behaviour than the mysql:// driver, please report 
it here.

Btw, I've never tested my driver with a 5.1.x server.

Yeah, but you're driver has the source. Also, in the ancient times 
before COmmand 2.x and your mysql:// I did a library interface, it's 
still on rebol.org

for large query results is there a way to show a progress bar as 
an indicator of the download progress?

Is there some type of callback method like "read-net"?

No, there's no such callback. But you can easily add one in my driver 
by inserting a call to "read-net" in the FOREVER loop inside READ-ROWS 
function. Can you describe briefly the situation where you need to 
get large query results and display a progress bar (just curious)?

I'm hosting a large number of text docs broken up by sections in 
a mysql db. Once all the material is on line the changes (and thus 
downloads) wil be smal for the most part. But in the mean time large 
amounts of material are still to be uploaded to the db and when a 
client finds this new material it will download it and any changes 
to previous material. The material contains a lot of image data too 
so it could add up. Right now I have about 40 megs of data and I 
have about 10X more to upload.


If its too tuff for me I will just display some animated gif but 
I would prefer something more tangible..

I'll look into your suggestion, Thanks Doc...


BTW I tried the latest mysql-protocol and it broke my app. I have 
not had a chance to look into it but I think you changed some syntax 
for selects??
I'm using Version: 1.1.2 at the moment.

Doc, I played around with read-net but no luck.

Would not read-net need to know total size of query result to work?
Does the server send this info first?


also, read-net wants a URL. Where am I pointing it to? Is this the 
same as the db port i'm using?

I'm just understanding now what you're trying to do. READ-NET is 
not what you want to use, it's for downloading files! You have to 
write your own progress bar function. About the total size of result 
set, AFAIK, MySQL server doesn't send that information, it just marks 
the last record of a result set.

Has anyone released a sphinx Rebol client api implementation?

I have made a primitive client libs to the SOLR search engine, if 
that helps anyone

That was a year ago ...

Interesting indeed. I'll pick this up with Reichart; we are all in 
favour, it merely is a matter of time (pressure). The libs are nice, 
but I am sure others can help improving them.

There's also an alternative approach, the SQL-ESCAPE encoding function 
of MySQL driver is exported in the global context, so you can use 
it when forming SQL queries directly :

send-sql db join "UPDATE table SET field=" sql-escape string


The prepared statement approach is recommended, because the driver 
will care about that for you automatically (so less risks that you 
forget to encode one value, opening a hole for SQL injection attacks).

yes, will use the prepared statement. it is also more elegant. with 
escaping I need to handle all fields to be save.

btw. many thx to provide such a cool, free and very important driver!

Need some advice on db structure.
I'm not sure which would be better for this senario:


I want to store user generated data which could consist of thousands 
of rows. But I also will want to be able to search across each users 
data.


I could create a seperate table for each user and join tables when 
searching through all user data or I could make one large database 
with a user field to seperate out that user's data when needed.

There could be many hundred users.

What is SOP in such a case?

Is there any reason mysql will not accept a nested block in a text 
field?
I have no problem with sqlite storing the same data...

SQL command is a string so IMHO you have to mold it and enquote it 
as normal text and then there should be no problems .. what kind 
of errors does it throw?

I'm using 'mold/only' saving to mysql and
'to-block'  to re-block it...
is there a cleaner way like an 'unmold'?

Also, i have some datestamp issues.
rebol attaches the zone code to now when getting the date/time

but when using mysql timestamp I do not get a time zone attached 
and its screwing me up.
Is there a way to add time zone to datestamp in mysql?

I know..a little off topic..moving to core

Any reason why my data in a 'text' column is getting truncated at 
9999 characters? I tried 'longtext' but there was no change..

I thought 'text' holds up to 4 gigs from what I read...


I saw something about longtext being limited by the way a client 
handles packets..could this be a problem with MySQL protocol?

I tried to insert a longer string using phpMyAdmin and it inserted 
fine...no truncation.
I guess its mysqlprotocol.r problem.

Anyone else encounter this? Any work arounds?

try wireshark to do a tcp trace to make sure all the packets are 
getting thru

Amacleod: There's no internal limit at 10000 bytes in mysql driver 
(single packet default limit is 1MB). Are you using prepared statements? 
If not, are you sure that your data is correctly encoded? Anyway, 
try using TRACE mode : trace/net on. Look at the sent packets size 
to see if there's a truncation in the mysql driver.

are you using latest 1.3beta versionof the driver? previus version 
may have a problem and truncate..

IIRC previous version had a problem with escaping correctly {'} in 
some circumstances, that may be a cause of truncation.

here is the latest http://softinnov.org/tmp/mysql-protocol-41.rgive 
it a try

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?

A web provider only provides access to mysql from a script ... and 
you will have a static ip for your web host.

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.

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.

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.

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.

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.

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

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

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.

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?

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}]}

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

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.

You put the "your browser does not support" message in the noframes 
section.

Many elderly browsers don't support them. All modern ones do -- though 
support can be turned off as a user-selected option.

Search engines are still very patchy at indexing frames -- and that 
is unlikley to get better given the inherent problems with frames

anyway - from your server you could create a reverse proxy. this 
is not very efficient (browser > your server > shop server   instead 
of browser > shop server   for all requests), but to the user it 
will really look like the shop is on your server (noone would be 
able to tell the difference)

alternatively, use frames as you do; though, this way users have 
no way to link to a specific page in your shop, because all urls 
are hidden.

petr: it's possible to crate a reverse proxy even without using mod_rewrite. 
you'll need mod_rewrite only if you want to somewhat change the urls 
to make them look different

Thanks Gabriele. I should mention why I do need it. The problem is, 
that some big distributors cheat on their sub-distributors. Together 
with few shops we found out, that although they allow to use their 
part of portal, some customers are clever, find out what shop it 
is in reality and they go directly to the parent shop and get the 
same price as from us. That is indeed bad behavior on their part 
as their partly ruin our business, but we can't do nothing about 
it. I will do my own shop, but as a whole system. I will describe 
architecture later ...

are there ANY decent web browsers that can print a page on a sheet 
without cropping long lines?  What is the best way to insure this? 
(IE, FF, Opera....all rubbish!!!)

ah, but none of it works...FF, IE, NS, Opera...all rubbish.  All 
I want is to print a proof.  At least they could get the text right.

Could you post a link to site you want to get printed?

it is a document I did in Make-doc2.r not for the public yet for 
another month...

Hi Steve, I usually just scale it down a bit in the print dialog 
box.

It's usually due to the size of the banner graphic on the top -- 
which is just a bit too wide. I need to fix it one of these days 
or change the way the page is built to not include the graphic in 
the outermost table.

Need a print CSS

yeah...I have done the scaling, sort of a fishy thing to have to 
do.  More of a rhetorical question I was asking...because if Web 
browsers and Adobe Acrobat were well designed, electronic documentation 
would be a joy to use...just lots of room for improvement to REBOLutionize 
the industry.

I will shut up...was just venting...a big pain in computing to solve...perfect 
literate computing.  Before Dynabooks are good, this problem has 
to be solved.

I did have code in a box that was rather long. so that will help 
printing until I design a new Web displayer...

I would like to fix path-thru so it can handle query strings, as 
links to rebol.org scripts have. eg. http://www.rebol.org/cgi-bin/cgiwrap/rebol/download-a-script.r?script-name=slim.r

This type of mapping could cause collisions (there might be a url 
with a %3F already in place of the ?) but I think this imperfect 
system is better than not being able to map at all.