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 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-7-bit
Path: 
 g2news1.google.com!news2.google.com!news4.google.com!feeder.news-service.com!85.214.198.2.MISMATCH!eternal-september.org!.POSTED!not-for-mail
From: Ludovic Brenta <ludovic@ludovic-brenta.org>
Newsgroups: comp.lang.ada
Subject: Re: gnade odbc and join tables
Date: Mon, 05 Jul 2010 22:35:00 +0200
Organization: A noiseless patient Spider
Message-ID: <87zky5r7kb.fsf@ludovic-brenta.org>
References: 
 <1265b06d-298d-4d1d-ae74-61262605262f@x27g2000yqb.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Injection-Date: Mon, 5 Jul 2010 20:35:00 +0000 (UTC)
Injection-Info: mx02.eternal-september.org;
 posting-host="xBF6WdS4QkBenpfTcNvFew";
	logging-data="20810"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19mu7ahFdkCbyi+jrgxHm3J"
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:rZooca9RMZFX/bFutvTjFYGdj0Q=
	sha1:dJuRZVh81FSRFwjc/khjw3/8iOI=
Xref: g2news1.google.com comp.lang.ada:12200
Date: 2010-07-05T22:35:00+02:00
List-Id: <comp.lang.ada>

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
>
>        Len             : aliased SQLINTEGER := 0; -- ignored for
> SQL_C_SLONG, if not negative
>  	 Len_Macid	 : aliased SQLINTEGER;
>          Macid           : aliased String := 16 * '.';
>          Planid          : aliased SQLINTEGER;
> 	 Len_Planid	 : aliased SQLINTEGER;
>          query_string 	 : String := "Select heater.macid, room.planid
> from heater" &
>          			"join room on heater.roomid = room.roomid order by
> room.planid";
>       begin
>          SQLAllocHandle (SQL_HANDLE_STMT, ConnectionHandle,
> StatementHandle);
>          SQLPrepare (StatementHandle,
>                     "SELECT " & QuoteIdentifier ("HEATER.MACID") & ",
> " &
>                        QuoteIdentifier ("ROOM.PLANID") & ", " &
>                      " FROM " & QuoteIdentifier ("HEATER") & " JOIN "
> &
>                      QuoteIdentifier("ROOM") & " " &
>                      "WHERE " & QuoteIdentifier ("HEATER.ROOMID") & "
> = " &
>                      QuoteIdentifier ("ROOM.ROOMID") &
>                        "ORDER BY " & QuoteIdentifier
> ("ROOM.PLANID") );
>
>          This is returning the following error when running
>
> raised GNU.DB.SQLCLI.SYNTAX_ERROR : [Proc=SQLBindCol][Server=x.x.x.x]
> [State=42000][MySQL][ODBC 3.51 Driver][mysqld-5.0.77]You have an error
> 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 = room.roomid
ORDER BY room.roomid

You miss the INNER keyword in both query_string and the argument to
SQLPrepare.  You use ON (correctly) in query_string but WHERE
(incorrectly) in the argument to SQLPrepare.

HTH

-- 
Ludovic Brenta.