Smalltalk › Squeak › Squeak - Dev

[squeak-dev] Mysql

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

8 messages Options

Bèrto ëd Sèra

[squeak-dev] Mysql

Hello! I'm new to Squeak but I have spent several years programming in SmallTalk back then... so choosing squeak for a project would be a nice way to get my mostly good memories back :)

The one and only problem I have is the same I used to have... object persistence :) I'm supposed to interface to a MySQL DB that accepts commands in XML (via a PARSER stored procedure) and answers with objects in XML format.

It's VERY nice to see in the image that the XML library is there. This should make things pretty simple. Now... HOW do I connect to Mysql?

Berto

Herbert König

Re: [squeak-dev] Mysql

Hello Bèrto,

BëS> It's VERY nice to see in the image that the XML library is
BëS> there. This should make things pretty simple. Now... HOW do I
BëS> connect to Mysql?

There's a Mysql driver on Squeakmap and some information on the swiki.
You can start here: http://wiki.squeak.org/squeak/1972

I don't know if this is Mysql or the driver but you need a new
connection for every query.

I'm using Squeak 3.8.2 and I think Mysql 4.X in an in house application
of a customer.

Herbert

Bèrto ëd Sèra

Re: [squeak-dev] Mysql

A connection for statement is not a problem, since I can pack any number of queries into a single XML statement it's not going to impact on performance ;) I'll try this later this week and let you know if it works.
Thanks
Berto

2008/6/16 Herbert König <[hidden email]>:

Hello Bèrto,

BëS> It's VERY nice to see in the image that the XML library is
BëS> there. This should make things pretty simple. Now... HOW do I
BëS> connect to Mysql?

There's a Mysql driver on Squeakmap and some information on the swiki.
You can start here: http://wiki.squeak.org/squeak/1972

I don't know if this is Mysql or the driver but you need a new
connection for every query.

I'm using Squeak 3.8.2 and I think Mysql 4.X in an in house application
of a customer.

Herbert

Rob Rothwell

Re: [squeak-dev] Mysql

There is also an ODBC package, and ODBCEnh, which allows DSN-less connections...

Rob

On Mon, Jun 16, 2008 at 4:22 AM, Bèrto ëd Sèra <[hidden email]> wrote:

A connection for statement is not a problem, since I can pack any number of queries into a single XML statement it's not going to impact on performance ;) I'll try this later this week and let you know if it works.
Thanks
Berto

2008/6/16 Herbert König <[hidden email]>:

Hello Bèrto,

BëS> It's VERY nice to see in the image that the XML library is
BëS> there. This should make things pretty simple. Now... HOW do I
BëS> connect to Mysql?

There's a Mysql driver on Squeakmap and some information on the swiki.
You can start here: http://wiki.squeak.org/squeak/1972

I don't know if this is Mysql or the driver but you need a new
connection for every query.

I'm using Squeak 3.8.2 and I think Mysql 4.X in an in house application
of a customer.

Herbert

keith1y

Re: [squeak-dev] Mysql

In reply to this post by Bèrto ëd Sèra

Bèrto ëd Sèra wrote:
> A connection for statement is not a problem, since I can pack any
> number of queries into a single XML statement it's not going to impact
> on performance ;) I'll try this later this week and let you know if it
> works.
> Thanks
> Berto
>
Try http://www.squeaksource.com/MySql for more recent version of the
driver. You don't actually need a new connection for each query, but
that is the way that I use it myself.

There is also some code for using the driver in
http://mc.lukas-renggli.ch/magritteaddons package Magritte-RDB

Keith

Bèrto ëd Sèra

Re: [squeak-dev] Mysql

All seems to work fine, I have but one problem: how do you state a "string in a string"? The query I need to execute inputs an XML command to a stored proc, so it looks like
resultSet := statement executeQuery: 'OWM2_PARSE('<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>')'.

Can't remember what's the equivalent of
resultSet := statement executeQuery: 'OWM2_PARSE(/'<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>/')'.
in smalltalk...

Very stupid question, I know :(
Bèrto

2008/6/16 Keith Hodges <[hidden email]>:

Bèrto ëd Sèra wrote:

A connection for statement is not a problem, since I can pack any number of queries into a single XML statement it's not going to impact on performance ;) I'll try this later this week and let you know if it works.
Thanks
Berto

Try http://www.squeaksource.com/MySql for more recent version of the driver. You don't actually need a new connection for each query, but that is the way that I use it myself.

There is also some code for using the driver in http://mc.lukas-renggli.ch/magritteaddons package Magritte-RDB

Keith

Bèrto ëd Sèra

Re: [squeak-dev] Mysql

I' really doing something wrong :) This is what I tried:

======================================================
| connection spec statement resultSet value OWm2command|
Socket initializeNetwork.
Transcript open.
spec :=    (JdmConnectionSpec new initialize
        user: 'root'; password: 'owm2';
        host: (NetNameResolver addressForName: 'localhost');
        database: 'owm2';
        port: 3306).

OWm2command := 'OWM2_PARSE(',$' asString,'<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>',$' asString,')'.

connection := JdmConnection on: spec.
statement := connection createStatement.
resultSet := statement executeQuery: OWm2command.
"print column names"
Transcript cr; show: (resultSet columns collect: [:col | col name]) asString.

[resultSet next]
whileTrue:
[
    value := resultSet valueNamed: 'id_object'. "get column named name"
    Transcript cr; show: value printString.
].
connection close.
============================

Obviously it could not go to the end, but I was expecting to be able to inspect the resultSet and find out how to call the "column" from there. Actually this function returns simply an XML string. Yet the point where I'm breaking is the query execution itself. It *might* be because the concation I tried looks weird. What I see inspecting the values is:
'OWM2_PARSE(''<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>'')'

Now... this will surely break because it was meant to have " only for the internal XML parameters...so this way the string cannot be passed. I'm sure there's a way, only I cannot find it.
Bèrto

2008/6/20 Bèrto ëd Sèra <[hidden email]>:

All seems to work fine, I have but one problem: how do you state a "string in a string"? The query I need to execute inputs an XML command to a stored proc, so it looks like
resultSet := statement executeQuery: 'OWM2_PARSE('<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>')'.

Can't remember what's the equivalent of
resultSet := statement executeQuery: 'OWM2_PARSE(/'<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>/')'.
in smalltalk...

Very stupid question, I know :(
Bèrto

2008/6/16 Keith Hodges <[hidden email]>:

Bèrto ëd Sèra wrote:

A connection for statement is not a problem, since I can pack any number of queries into a single XML statement it's not going to impact on performance ;) I'll try this later this week and let you know if it works.
Thanks
Berto

Try http://www.squeaksource.com/MySql for more recent version of the driver. You don't actually need a new connection for each query, but that is the way that I use it myself.

There is also some code for using the driver in http://mc.lukas-renggli.ch/magritteaddons package Magritte-RDB

Keith

Bèrto ëd Sèra

Re: [squeak-dev] Mysql

hmmmmmm... it looks like the string is okay. It's just stupid me who forgot that in SmallTalk you write '' twice to escape it. So the problem appears to be another:
==========
executeQuery: aQuery
    | queryInfo r |
    queryInfo := self class queryInfoFor: aQuery.
    queryInfo isNil
        ifTrue:
        [JdmErrorTable errorTable throwException: #invalidQuery message: aQuery].
    (connection requestPacket writeStream)
     nextPutCommand:
        (JdmMysqlCommand withCommandNamed: #query message: aQuery);
    flush.

    r := self createResult: queryInfo.
    ^r
=============
queryInfo is nil... Most probably this method wasn't written to call stored procedures. As a quick workaround I'll create a temporay table with two fields (in & out) and a Before Insert trigger that will execute the procedure on the input and place the output on the corresponding field of the same record. So I can insert the command and immediately after that I can query the last_insert_id to get my result.

But it would be much nicer if I could avoid doubling the number of db interactions, since this is where most performance gets killed. Can Anyone tell me how I can contact the author of Mysql-driver? Maybe it's possible to find something better than my poor hack.

Bèrto

2008/6/20 Bèrto ëd Sèra <[hidden email]>:

I' really doing something wrong :) This is what I tried:

======================================================
| connection spec statement resultSet value OWm2command|
Socket initializeNetwork.
Transcript open.
spec :=    (JdmConnectionSpec new initialize
        user: 'root'; password: 'owm2';
        host: (NetNameResolver addressForName: 'localhost');
        database: 'owm2';
        port: 3306).

OWm2command := 'OWM2_PARSE(',$' asString,'<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>',$' asString,')'.

connection := JdmConnection on: spec.
statement := connection createStatement.
resultSet := statement executeQuery: OWm2command.
"print column names"
Transcript cr; show: (resultSet columns collect: [:col | col name]) asString.

[resultSet next]
whileTrue:
[
    value := resultSet valueNamed: 'id_object'. "get column named name"
    Transcript cr; show: value printString.
].
connection close.
============================

Obviously it could not go to the end, but I was expecting to be able to inspect the resultSet and find out how to call the "column" from there. Actually this function returns simply an XML string. Yet the point where I'm breaking is the query execution itself. It *might* be because the concation I tried looks weird. What I see inspecting the values is:
'OWM2_PARSE(''<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>'')'

Now... this will surely break because it was meant to have " only for the internal XML parameters...so this way the string cannot be passed. I'm sure there's a way, only I cannot find it.
Bèrto

2008/6/20 Bèrto ëd Sèra <[hidden email]>:

All seems to work fine, I have but one problem: how do you state a "string in a string"? The query I need to execute inputs an XML command to a stored proc, so it looks like
resultSet := statement executeQuery: 'OWM2_PARSE('<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>')'.

Can't remember what's the equivalent of
resultSet := statement executeQuery: 'OWM2_PARSE(/'<?xml version="1.0" encoding="UTF-8" ?><OWM2><Query command="list" type="class" id="12" ><Output /></Query></OWM2>/')'.
in smalltalk...

Very stupid question, I know :(
Bèrto

2008/6/16 Keith Hodges <[hidden email]>:

Bèrto ëd Sèra wrote:

A connection for statement is not a problem, since I can pack any number of queries into a single XML statement it's not going to impact on performance ;) I'll try this later this week and let you know if it works.
Thanks
Berto

Try http://www.squeaksource.com/MySql for more recent version of the driver. You don't actually need a new connection for each query, but that is the way that I use it myself.

There is also some code for using the driver in http://mc.lukas-renggli.ch/magritteaddons package Magritte-RDB

Keith