Re: gnade odbc and join tables

[tonyg <tonythegair@googlemail.com>]

On Jul 5, 9:35 pm, 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
>
> >        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.

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.