MySQL driver issue?
[1/4] from: kpeters::otaksoft::com at: 25-Aug-2007 13:57
The query below fails:
"SELECT * FROM donations where DonationAmount > 10 order by DonationID"
** User Error: URL error: mysql://theuser:thepassword-192.168.1.14:3306/thedatabase?SELECT
* FROM donations where DonationAmount > 10 orde...
** Near: cursor: read rejoin [mysql-url "?" querystr]
while this one works:
"SELECT * FROM donations where DonationAmount = 10 order by DonationID"
DonationAmount happens to be a decimal 8.2 in this case
These queries work just fine from any MySQL client on all platforms.
It seems that including an operator like '>' or '<' is causing the problems. Escaping
issue? Re-read the docs but
am unsure what this might be....
Thanks,
Kai
[2/4] from: tim-johnsons:web at: 25-Aug-2007 16:54
On Saturday 25 August 2007, Kai Peters wrote:
> The query below fails:
> "SELECT * FROM donations where DonationAmount > 10 order by DonationID"
<<quoted lines omitted: 9>>
> problems.= Escaping issue? Re-read the docs but
> am unsure what this might be....
Hi Kai:
I don't use that method. I know that you will be hearing from DocKimbel or
someone else soon with insights. In the meantime, if you just need to get
up and running I could suggest another method:
;; factor out a port and open it
db: open mysql://theuser:thepassword-192.168.1.14:3306/thedatabase
insert db "SELECT * FROM donations where DonationAmount > 10 order by
DonationID" ;; string wrapped by my mailer
print db
;; try that and see if you get an error
Tim
[3/4] from: kpeters::otaksoft::com at: 25-Aug-2007 19:50
Tim ~
tested a bit more based on your suggestion and here's what I get:
print send-sql db "select * from donations where donationamount > 50" works just fine
print read join mysql://theuser:thepassword-192.168.1.14:3306/thedatabase? "SELECT *
from donations where donationamount > 50" fails
** User Error: URL error: mysql://theuser:thepassword-192.168.1.14:3306/thedatabase?
"SELECT * from donations where donationamount > 50
** Near: print read join mysql://theuser:thepassword-192.168.1.14:3306/thedatabase? "SELECT
* from donations > ...
I had emailed DocKimbel about another error I get when using the shortcut command from
above: On one of my tables a simple query for
... LastName = '%ee%' ... returns a bunch of unrelated records - all other queries for
other character combinations seem to work just fine.
Kai
On Sat, 25 Aug 2007 16:54:11 -0800, Tim Johnson wrote:
[4/4] from: dockimbel::free::fr at: 26-Aug-2007 13:38
Hi Kai,
Now I understand the cause of the issue you've sent me by email.
The issue is in the URL encoding/decoding process when you use 'read.
'dehex is internally called by the MySQL driver, so if you use the %
character in a 'read query, you'll get wrong results.
>> dehex to-url "like %ee%"
== "like '=EE%'"
Maybe I should add support for 'read/custom to avoid the need to encode
the SQL query.
--
DocKimbel
Kai Peters wrote:
Notes
- Quoted lines have been omitted from some messages.
View the message alone to see the lines that have been omitted