Re: gnade odbc and join tables

[Ludovic Brenta <ludovic@ludovic-brenta.org>]

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.