gnade odbc and join tables

gnade odbc and join tables

[tonyg]

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?

Re: gnade odbc and join tables

[Georg Bauhaus]

On 7/5/10 7:59 PM, tonyg wrote:

>           SQLPrepare (StatementHandle,
>                      "SELECT "&  QuoteIdentifier ("HEATER.MACID")&  ",
> "&
>                         QuoteIdentifier ("ROOM.PLANID")&  ", "&
>                       " FROM "&  QuoteIdentifier ("HEATER")&  " JOIN"
>

I believe you have an extra comma after ROOM.PLANID, before FROM.

Re: gnade odbc and join tables

[Ludovic Brenta]

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.

Re: gnade odbc and join tables

[Stephen Leake]

tonyg <tonythegair@googlemail.com> writes:

> 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.

Others have commented on your SQL syntax.

One way I find useful to debug things like this is to run the query in
an interactive front end for the database involved; the sql error
messages tend to be better, and it's easier to modify the syntax and try
again.

MySQL on Windows has a nice GUI for this, including some help on SQL
syntax (although it tends to leave out the basic stuff and focus on the
advanced stuff).

-- 
-- Stephe

Re: gnade odbc and join tables

[tonyg]

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.