AltME groups: search

If some of you still have troubles connecting to a v4+ MySQL server 
with the last driver v1.0.6, please give a try to a new beta version 
(1.0.7) found here : http://softinnov.org/tmp/mysql-protocol.r

If you've set up your MySQL server using a PHP tool like phpMyAdmin, 
your users are probably created with the OLD_PASSWORD mode, even 
if you're using v5+ servers (it's a PHP issue). My driver v1.0.7 
should allow you to connect flawlessly to such configured server. 
Please try it and give me some feedback.

docKimble, I just thought of something: what if, for MySQL, a time 
out is not the same as reconnecting to a restarted server?

I don't understand your question. Could you explain a little more 
?

When a new request is sent through a timeout-ed connection, my driver 
will close the port and try to reconnect to the server. It tries 
3 time and if it fails, generates an error.

I am using mysql-wrapper.r . In function db-last-insert-id there 
should be one less "first" command to get the result. I think this 
is a bug, pls confirm

if the behavior of the mysql-protocol has changed lately, then yes. 
when i wrote that, it needed that "first". (i used that function 
a lot so I know it worked)

i used db-cached-query for vid LISTs, gives a huge speedup without 
using too much memory. (i had a custom list style though that was 
optimized for this). i don't have a simple example at hand... :(

Yes, gabriele , it's not a bug. I got confused by the three first 
, I think a clear way to code it would be "first first copy db" to 
indicate first value of the first row

the mysql program does this automatically when you give it a file 
(using ; as separator)

from here http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
I thing the adjacency list model would be a good solution, or is 
there something better withmysql 5 and stored procedures? or something 
in rebol?

I was just on the mySQL site and did a search for REBOL and was surprised 
to find 0 hits.

will, that is a great article. It explains why the adjacency list 
model is not good enough b/c it's not normalized

wops, I actually meant the" the nested set model" would be a good 
solution, had to write way too much code to retrive, cache, etc with 
the adjacency list model. Still wondering if some good rebol fellow 
has code ready, something like the article but with stored procedures? 
Anything better? mmm, and what if I'd like geolocalization, should 
I buy a book about PostGIS? please tell me there is a easier way! 
thx ;-)

The new MySQL web site is much less third-parties friendly. It looks 
like MySQL doesn't want to give much attention on alternatives drivers. 
They want ppl to use their own drivers and nothing other. The new 
MySQL protocol licence is more restrictive than before. They described 
a GPLed protocol concept (which a IMO, a very stupid concept...) 
See : http://dev.mysql.com/doc/internals/en/licensing-notice.html

Btw, I've released an alpha version of a new MySQL driver implementation 
: complete rewrite from scratch to be fully async under UniServe, 
 optimized decoding in speed (faster code, streaming decoding, etc...), 
supports only servers v5+. You'll find it in the latest UniServe 
archive (see UniServe group for link)

Here's a short example :

A query of mine loops and I can not understand the reason.

I gave a look to the protocol and found that :

* defrag-read port pl/buffer std-header-length

returns a 'packet-len of 108544 bytes

the next 

* defrag-read port pl/buffer packet-len


loops because the 'read within 'defrag-read  retrieves only 108543 
bytes.

Any idea on how I can deal with this problem?

I'm aware of only one case where my protocol implementation may fail 
with a size difference of 1 byte between expected size and received 
size: that's the compression header case. This case can only happen 
when the client send a compression flag to the server. My driver 
never send such flag, so this case should never happen.

2) Get a packet analyser tool (Ethereal for example) and try to track 
the TCP exchange on port 3306 to see what the server is really sending 
to the client.

4) If all options failed, turn trace/net on, run a test, log all 
exchanges with the server and send it to me by email for analysis.

So, it looks like a bad read buffer size setting.

Luca, let's try a more cleaner fix for this issue, replace the following 
code :

Just a moment...

I use the protocol with MySQL 4.1.5 and 5.0.22 and never noticed 
such a problem.

But I'm not storing such a big data

I'm preparing to switch my OS to a slack partition with MySQL 4.1 
(I believe)

OOPS! didn't mean to hit ENTER. sorry. Anyway, there used to be a 
test site at http://powerofq.com- it is no longer available, I believe 
that the test module was 'mysqlprot. are those resources still available?

- There's now a new preferred way to query data, the SEND-SQL global 
function. The INSERT/COPY methods are still available for cases where 
finer-grained control or ressources optimization is required. Example:


send-sql [ /flat ] [ /named ] [ /raw ] db "SQL query or server command"

rebol.org is RT's property. My driver is a free concurrent to RT's 
commercial one.

don't you think it would be a problem for RT ?

but in any case, rebol.org is a community space.  although the site 
H/W and domain is "owned" by RT ,  the actual service has no useage 
limits AFAIK.

henrik, maybe not... who says RT won't offer plugins for a fee?

If you think it's ok with RT, you can put a copy of the new driver 
in rebol.org once I release it (should be tonight).

I think that's a great idea, Doc!
***

RT own the domain for rebol.org (and pay all the hosting charges).

They have never vetoed any scripts .... the topic has never come 
up.


Publishing to REBOL.org leaves you with all your ownership rights 
(unlike some libraries that claim ownership on any contributions). 
All REBOL.org requires is the non-exclusive right to publish the 
script and its documentation:

http://www.rebol.org/cgi-bin/cgiwrap/rebol/boiler.r?display=script-owner-faq

As a script owner on REBOL.org you have access to up-to-the-minute 
access and download stats.

I see that rebol.org has improved a lot. Good work guys!

Is there a top 10 most downloaded scripts ? (could be usefull to 
newcomers)

There's a top 250 most popular scripts -- available if you are logged 
on:

http://www.rebol.org/cgi-bin/cgiwrap/rebol/script-popularity.r?top=250

The popularity rating is an imperfect attempt to factor out downloads 
by bots (and many other factors) and arrive at something meaningful.

Can I upload a ZIP archive or does it require to upload only a single 
REBOL script ?

even if 'argument is a SQL query in its own?

You remind me that I should add a few lines about secure programming 
practices in the driver documentation.

will send a query like:

Interesting case, you're right, it may confuse the multistatement 
processing, I'll make a fix for that asap.

Am I missing something wihen querying multiple columns... seems I 
get the results as a single string.. 
ie: send-sql "select a, b from table";

returns something like     "aresultbresult" ?

Beta release v1.1.1 with a fix for the semicolon issue in SQL strings 
: http://softinnov.org/tmp/mysql-protocol.r

MySQL driver release v.1.1.2 Download at : http://rebol.softinnov.org/mysql

- Fix for an infinite loop issue when the server times out the connection 
(unix platforms).

- TCP keepalive option activated by default (for longstanding idle 
connections).

- Send-cmd function optimized to be a little bit faster and use less 
memory.
- Added new-lines markers to resulting recorsets.

- Added option to switch on/off new-lines marker through port/locals/newlines? 
flag.

- Recycle call removed from read-rows function. Should speed up the 
results a little bit.
- Minor source code cleanup.

GREAT NEW, a basis benchmark with about 50'000 queries runs in 2:52.26 
minutes and..

does this line return a none! value too : send-sql db "SELECT * FROM 
jobs" ?

That did not work either but I seem to have got it. I had previously 
copyed the database to a word like:  data_info: copy db. But that 
gave me an error saying data_info is a set word.  I changed it to 
data_info: send-sql/flat db "select * from jobs" THis seems to work. 
Thanks

I just found that there is a serious bug in sql-escape functions 
which is part of mysql-protocol...  new versions of MySQL for example 
autoconvert "'" to ' which MUST be escaped or you will get error 
or your query may be injected! Current sql-escape function do not 
escape such a cases.

insert db "create table esctest (text TINYTEXT)"
insert db "insert into esctest values ('a')" ;correct

insert db "insert into esctest values ('a'')" ;=== User Error: 
ERROR 1064

hmm.. so it's not so bad.. the problem is just with semicolon:

insert db {insert into esctest values ("a;")} ;=== User Error: ERROR 
1064

and now I have a prove:

not-squote: complement charset "'"
not-dquote: complement charset {"}
ws: charset " ^-^M^/"

data: {INSERT INTO hmm_kalendar_akce VALUES (NULL,9,'posledni-skotsky-kral',NULL,'(Last 
King of Scotland, The)','Poslední skotský král',NULL,NULL,'','http://www.kinoartbrno.cz/?stranka=film&film=posledni-skotsky-kral',0,NULL)}

;the parse part from insert-all-queries function:
	parse/all s: data [
		any [
			#"'" some ["\'" | "''" | not-squote] #"'"
			|{"} some [{\"} | {""} | not-dquote] {"}
			| #"`" thru #"`"
			| e: #";" (
				probe "next query?"
				probe e
			  ) any [ws] s:
			| skip
		]
	]

New MySQL release 1.1.3 beta : http://softinnov.org/tmp/mysql-protocol.r

	o Fix for parsing correctly quoted empty strings. (Fix by Oldes)
		

 o Fixed an issue in case of automatic reconnection, the driver looses 
 local
	  flags like 'flat, auto-ping?, etc...(thanks to Will Arp)


 o Fix a remanence issue on /flat and /raw flags in 'send-sql after
	  automatic reconnection. (thanks to Will Arp)


 o Improved port recovery support after a failed request upon a shutdown 
 server.
	  Now the port will resume working once the server is up again.


 o An init SQL string can now be specified for an opened port. That 
 init string

   will be executed after automatic reconnections. (Useful to set, e.g., 
   a
	  specific charset value). Example : 

		  db-port/locals/init: "SET NAMES 'latin2'"

MySQL driver release v.1.2.0 - Download at : http://rebol.softinnov.org/mysql
- Changed behaviour and syntax of the READ function.
- Fix for parsing correctly quoted empty strings. (Fix by Oldes)

- Fix for the driver loosing local flags, like 'flat, 'auto-ping?,... 
when reconnecting (thanks to Will Arp)

- Fix a remanence issue on /flat and /raw flags in 'send-sql after 
automatic reconnection. (thanks to Will Arp)

- Improved port recovery support after a failed request upon a shutdown 
server.
- An init SQL string can now be specified for an opened port.

Doc, first of all, thanks for the driver. It is amazing. Secondly, 
I've tried a few ways to be able to detect for an open port without 
the program erroring out. I've just settled on a "error? attempt 
[db/locals]" Do you have any recommendations?

Here's a handy function to test if a MySQL server is up :

you can also use a simple : port: attempt [ open mysql://... ]

(i.e. the search client protocol that can talk to Sphinx). You can 
also compile a client in mysql and then use sql for your queries.

here is a short and concise presentation about sphinx http://www.scribd.com/doc/2670976/Sphinx-High-Performance-Full-Text-Search-for-MySQL-Presentation

Guys, what do you think about Tim Johnson's ml remark? Could we help 
him somehow?

-----------------------------------

I've been using mysql-protocol (Mysql Scheme) and cgi for many years 
now. I 

really like it, but I think that /command would bring some advantages, 
if
it can directly access the "C" API for MySQL.


Example: Using the mysql scheme on a tcp/ip connection, I have to 
code

the escaping of special characters for insert and updates and code 
the
unescaping of special characters for retrieval. 


Using the C API from other scripting languages, the mysql_real_escape_string()
function handles the escaping _appropriate_ to the version, and
queries are similarly unescaped.

about escaping, Nenad's driver has such functions already, so i don't 
see that as an advantage for the native driver. Qtask has a native 
driver too (for speed reason with large record sets), but remember 
that when you use the mysql c library either your app has to be GPL 
or you need a license from MySQL (Qtask has a license).

I have a mysql wrapper that I use in production but haven't released 
because it need cleaning and docs, but if there is interest I could 
release for testing as is. id does things like:

print .db/get/all/sort/debug [mailing/m mailingLog/ml] [ml.dc m.email 
ml.url] [{ml._mailing=m.id}] [m.email asc ml.dc desc]

SELECT `ml`.`dc`,`m`.`email`,`ml`.`url` FROM `mailing` `m`,`mailingLog` 
`ml` WHERE ml._mailing=m.id ORDER BY `m`.`email`,`ml`.`dc` DESC
print .db/set/debug 'mailing 12 reduce['active false] 
UPDATE `mailing` SET `active`=0 WHERE id=12.0

print .db/get/all/flat/debug '_node_tree [_node _media] [{_tree=? 
AND _issue=?} 5443 22]

SELECT `_node`,`_media` FROM `_node_tree` WHERE _tree='5443' AND 
_issue='22'
..

Gabriele, I thought you only needed a license if you are releasing 
your software ....

graham, i'm not entirely sure, in principle the GPL2 is as you say, 
however i'm not sure if MySQL has a different interpretation here. 
iirc the GPL3 states that being a server for users is the same as 
releasing the software.

So, looks like you didn't need a license

because of a limited viewpoint

With two open source databases to choose from of approximately similar 
functionality and stability, it is hard to understand why one would 
choose the one that has the restrictive license ... I can only imagine 
it is a mind share thing.

James, thanks for your interest, I' ll upload it in a couple days 
8)

ummm.. how do you do a select on a field where the field name has 
a space in it?

Why would anyone put a space in a field name??

docs say \_ is a space

>> insert db "select middle\_name from patientrecord"

** User Error: ERROR 1064 : You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server ve
rsion for th...
** Near: insert db "select middle\_name from patientrecord"

For a long time, I was reluctant to change the date zero behaviour 
(error throwing), because this "feature" of MySQL look, to me,  like 
very bad design. But recently, I hit the problem too when I inserted 
badly formed dates in a table and wasn't able to read the records 
anymore with my driver.

Version 1.2.1 re-released. There was a small error in the 'name-fields 
function preventing it from working. Thanks to Will for reporting 
it.

James: sorry for the delay, still not found time for docs but her 
it is "as is.."  /debug is your friend, get started, load Dock driver 
, then load  http://reboot.ch/rebol/mysql-wrapper.txtthen:
;set a list of connections:
.db/databases: [
  ;local
  mydb1.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb1
  mydb2.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb2

  ;online
  mydb1 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb1
  mydb2 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb2
]
;use a db (if not open will open it automatically):	
.db/use 'mydb1

I've just upload a new experimental 1.3 version of MySQL driver supporting 
mutiple queries, stored procedures and multiple result sets. Reading 
mutiple results is done by calling COPY for each result. Download 
at http://softinnov.org/tmp/mysql-protocol-41.r

Keep in mind that if you're using SEND-SQL, it already does a implicit 
COPY.

It should be considered as a early beta, so test it well before using.

BTW, Maarten told me that he knows for sure that you can get the 
number of result sets in advance, if he doesn't show up here (he 
can't be much in front of a computer these days), maybe you could 
email him to get more info.

could make a spellcheck-demo with rebgui.

I don't think i have a good diff tool on hand.

I use sourceforge - winmerge for windows.  It is a gui diff tool 
that works great and is free.

Area field editing is still quite problematic :(  Put some text in, 
change it by putting a lot more, and then try it with a lot less. 
 The area widget gets confused and the text starts showing halfway 
down the widget.  Resetting lines-no doesn't help.

This very odd .. I have a drop-list .. and the contents of the data 
change each time I drop the list down.  It doesn't happen in Ashley's 
example tour.r, and I don't have an action associated with the drop-list.

Ahh... sussed it.  I was composing the layout block inside a function, 
and forgot to copy before passing it to the display function.

I'd say your boss was a bit suss, if he hadn't sussed out the meaning.

hey, come on, there is no proper english exists >( its never been 
defined, its only referenced all the time. infact it cant really 
be defined. not even without serious accuracy. the language has too 
many dimensions (time, instance, coordinates, target, etc) and the 
values in this space show a great variation, just we dont wanna notice 
it. infact its not very useful to average clusters of this space. 
specifying these cluster boundaries is a constant source of problems.. 
i live in the middle of europe, at a language-wise very sepecial 
place, but i still can experience this problem easily -- if i want 
to..

Sorry, OneTom, it was meant as a joke.

Anyone know if rebgui has a dirty? flag for area/text ?

is there anybody here at the moment who can help me develop a csv 
editor interface(, so i can easily translate the qtask interface 
to hungarian)?

Will a REBOL spreadsheet script help you? http://www.devx.com/opensource/Article/27454/0

edit-list does not display past the border of a drop-list.

would be nice to have in the options list, a way to set the background 
colour for the slider.

Got some bug when I create a form with a radio button where the pf 
in the engage function is none.

okay, i was lost in the code, i suppose. but how can i select a row 
or rows programmatically?

you could try using the engage function .. ie. to simulate a mouse 
click.

but there is already a smart engage function crafted for widgets 
having textediting capabilities