From mboxrd@z Thu Jan  1 00:00:00 1970
X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me
X-Spam-Level: 
X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,FREEMAIL_FROM
	autolearn=ham autolearn_force=no version=3.4.4
X-Google-Thread: 103376,9986f50b2446f516
X-Google-NewGroupId: yes
X-Google-Attributes: gida07f3367d7,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII
Path: 
 g2news1.google.com!postnews.google.com!j4g2000yqh.googlegroups.com!not-for-mail
From: tonyg <tonythegair@googlemail.com>
Newsgroups: comp.lang.ada
Subject: Re: gnade odbc and join tables
Date: Mon, 5 Jul 2010 23:30:13 -0700 (PDT)
Organization: http://groups.google.com
Message-ID: <e1bbd7c8-aeac-400b-8ba6-4c7089c1da46@j4g2000yqh.googlegroups.com>
References: 
 <1265b06d-298d-4d1d-ae74-61262605262f@x27g2000yqb.googlegroups.com>
	<87zky5r7kb.fsf@ludovic-brenta.org>
NNTP-Posting-Host: 89.240.135.62
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1278397814 24807 127.0.0.1 (6 Jul 2010 06:30:14
 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 6 Jul 2010 06:30:14 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j4g2000yqh.googlegroups.com; posting-host=89.240.135.62;
	posting-account=28F2IwkAAACL1Z5nRC-dE7zuvWdbWC7P
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.6)
	Gecko/20100628 Ubuntu/10.04 (lucid) Firefox/3.6.6,gzip(gfe)
Xref: g2news1.google.com comp.lang.ada:12213
Date: 2010-07-05T23:30:13-07:00
List-Id: <comp.lang.ada>

On Jul 5, 9:35=A0pm, Ludovic Brenta <ludo...@ludovic-brenta.org> wrote:
> tonyg writes on comp.lang.ada:
>
>
>
> > I'm adapting the odbc demo in gnade to learn more about it. I want to
> > be able to join tables and do a select according to the query string
> > included in the code fragment.
> > I 've modified the SQL prepare statement and here it is
>
> > =A0 =A0 =A0 =A0Len =A0 =A0 =A0 =A0 =A0 =A0 : aliased SQLINTEGER :=3D 0;=
 -- ignored for
> > SQL_C_SLONG, if not negative
> > =A0 =A0 Len_Macid =A0 =A0 =A0 : aliased SQLINTEGER;
> > =A0 =A0 =A0 =A0 =A0Macid =A0 =A0 =A0 =A0 =A0 : aliased String :=3D 16 *=
 '.';
> > =A0 =A0 =A0 =A0 =A0Planid =A0 =A0 =A0 =A0 =A0: aliased SQLINTEGER;
> > =A0 =A0 Len_Planid =A0 =A0 =A0: aliased SQLINTEGER;
> > =A0 =A0 =A0 =A0 =A0query_string =A0 =A0 =A0 : String :=3D "Select heate=
r.macid, room.planid
> > from heater" &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"join room on he=
ater.roomid =3D room.roomid order by
> > room.planid";
> > =A0 =A0 =A0 begin
> > =A0 =A0 =A0 =A0 =A0SQLAllocHandle (SQL_HANDLE_STMT, ConnectionHandle,
> > StatementHandle);
> > =A0 =A0 =A0 =A0 =A0SQLPrepare (StatementHandle,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "SELECT " & QuoteIdentifier ("H=
EATER.MACID") & ",
> > " &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QuoteIdentifier ("ROOM.P=
LANID") & ", " &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" FROM " & QuoteIdentifier (=
"HEATER") & " JOIN "
> > &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QuoteIdentifier("ROOM") & " =
" &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"WHERE " & QuoteIdentifier (=
"HEATER.ROOMID") & "
> > =3D " &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QuoteIdentifier ("ROOM.ROOMI=
D") &
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"ORDER BY " & QuoteIdent=
ifier
> > ("ROOM.PLANID") );
>
> > =A0 =A0 =A0 =A0 =A0This is returning the following error when running
>
> > raised GNU.DB.SQLCLI.SYNTAX_ERROR : [Proc=3DSQLBindCol][Server=3Dx.x.x.=
x]
> > [State=3D42000][MySQL][ODBC 3.51 Driver][mysqld-5.0.77]You have an erro=
r
> > in your SQL syntax; check the manual that corresponds to your MySQL
> > server version for th
>
> > (ip address is x'ed out !)
>
> > What am I doing wrong here - can anyone see?
>
> IIRC, the proper syntax is INNER JOIN ... ON, not JOIN ... WHERE, i.e
>
> SELECT heater.macid, room.planid
> FROM heater INNER JOIN room ON heater.roomid =3D room.roomid
> ORDER BY room.roomid
>
> You miss the INNER keyword in both query_string and the argument to
> SQLPrepare. =A0You use ON (correctly) in query_string but WHERE
> (incorrectly) in the argument to SQLPrepare.
>
> HTH
>
> --
> Ludovic Brenta.

Two definite show stopping bugs spotted and removed. Thanks for the
help guys. Just to note (for posterity) it still does not like the
table being specified on the column the way I did it i.e.
'heater.roomid' it insists on using the QuoteIdentifier on both heater
and roomid (with a "." &'ed in the middle) to specify that.