[jadmin] RE: [JDEV] Complex DTD=2 SQL

[Bernino Lind]

Very true - implicit variables are enemies of the state...

However, it did not solve the problem at hand...

Best regards,
Bernino

-----Original Message-----
From: jadmin-admin at jabber.org [mailto:jadmin-admin at jabber.org] On Behalf
Of Tim Klem
Sent: Wednesday, June 25, 2003 4:47 PM
To: jdev at jabber.org; jadmin at jabber.org
Subject: [jadmin] RE: [JDEV] Complex DTD=2 SQL


Hi Bernino,

I think the SELECT data fields should be stated explicitly. The bindvar
may be failing on the "SELECT *".

Specifically:

<querydef name="score-get" dtd="2" type="get" namespace="lind:score">
    <text>SELECT sid, username, gameid, score, date
           FROM score WHERE gameid = '$$gameid$$' AND username =
'$$user$$'</text>

Plus this way, if changes are ever made to the DB table structure, you
won't break this.

Regards,

Tim


-----Original Message-----
From: jdev-admin at jabber.org [mailto:jdev-admin at jabber.org]On Behalf Of
Bernino Lind
Sent: Wednesday, June 25, 2003 10:29 PM
To: jdev at jabber.org; jadmin at jabber.org
Subject: [JDEV] Complex DTD=2 SQL


Dear all,

I need a bit of adwise.

I have a working simple dtd="2" sql set/get pair with my own namespace.

I need to make these more sql queries more complex by adding more
variables in my select statements.

I can do this no problem with my update/insert with bindvar tag=... but
with select I hit problems, which I cant resolve...:

My select in xdb_sql.xml / queredef:

     <querydef name="score-get" dtd="2" type="get"
namespace="lind:score">
        <text>SELECT * FROM score WHERE gameid = '$$gameid$$' AND
username = '$$user$$'</text>
        <user>$$user$$</user>
        <bindvar tag="gameid">$$gameid$$</bindvar>
        <top-result>
          <x xmlns='lind:score'/>
        </top-result>
          <tuple-node><item/></tuple-node>
            <bindcol tag="sid" offset="0"/>
            <bindcol tag="username" offset="1"/>
            <bindcol tag="gameid" offset="2"/>
            <bindcol tag="score" offset="3"/>
            <bindcol tag="date" offset="4"/>
        </querydef>
    </queries>


What happens when I fire off my xml is that the type="set" works just
fine, but firing off a gives a warning:

WARNING:  pg_atoi: zero-length string

Which I guess means that gameid is null, even though my iq packet is
good:

<iq type="get">
  <query xmlns='lind:score'>
    <gameid>123</gameid>
  </query>
</iq>

And theres something in it:
jdb=# SELECT * FROM score WHERE gameid = '123' AND username =
'psi at 192.168.0.166';
 sid |     username      | gameid | score  |            date
-----+-------------------+--------+--------+----------------------------
  10 | psi at 192.168.0.166 |    123 | 234234 | 2003-06-17 15:42:41.611469
  12 | psi at 192.168.0.166 |    123 | 234234 | 2003-06-17 16:12:12.182216
  13 | psi at 192.168.0.166 |    123 | 234234 | 2003-06-25 15:31:02.446713
  14 | psi at 192.168.0.166 |    123 | 234234 | 2003-06-25 15:31:11.871944
  15 | psi at 192.168.0.166 |    123 | 234234 | 2003-06-25 16:02:51.309132
(5 rows)

When I change the querydef to have just say username - no problem, it
selects and outputs the way I want it...

Help,
Best Regards Bernino Lind


_______________________________________________
jdev mailing list
jdev at jabber.org
http://mailman.jabber.org/listinfo/jdev


_______________________________________________
jadmin mailing list
jadmin at jabber.org
http://mailman.jabber.org/listinfo/jadmin