RE: [odb-users] ODB compiler for oracle, double qoutes SQL statements

[Magnus Granqvist]

Hi,

In my tests I run the following query:
SELECT "dagsmproduct"."oidval","dagsmproduct"."msisdn" FROM "dagsmproduct"

The table exists in my database. But I get "ORA-00942: table or view does not exist".
If I run the same query in sqlplus command line tool I also get ORA-00942.

If I manually edit the generate file and remove the double quotes the query works from the test application:
const char access::object_traits< ::dagsmproduct >::query_statement[] =
  "SELECT "
  "\"dagsmproduct\".\"oidval\","
  "\"dagsmproduct\".\"msisdn\""
  " FROM \"dagsmproduct\""
  " ";

To

const char access::object_traits< ::dagsmproduct >::query_statement[] =
  "SELECT "
  "dagsmproduct.oidval,"
  "dagsmproduct.msisdn"
  " FROM dagsmproduct"
  " ";

But I think I see why this is happening now, the table must be created with quotes to support the SQL.
This could be a problem developing for existing production databases that is not created with qoutes

CREATE TABLE "dagsmproduct" (
  "oidval" RAW(16),
  "msisdn" VARCHAR2(512) NOT NULL PRIMARY KEY);


/Magnus

-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: den 20 november 2012 14:25
To: Magnus Granqvist
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] ODB compiler for oracle, double qoutes SQL statements

Hi Magnus,

Magnus Granqvist <magnus.granqvist at tailormade.se> writes:

> The odb compiler generates oracle SQL code like this:
>
> "SELECT "boost_employee"."id" FROM "boost_employee" WHERE 
> "boost_employee"."employer"=:1"
> 
> This is not valid to run towards a oracle database as far as I know 
> and when exeuting this SQL I  get an  ora error.

As far as I know, identifier quoting is legal in Oracle. In fact, it is required if you use a keyword as a table or column name or if you want to preserve case (Oracle upper-cases all the unquoted identifiers by default). So ODB-generated queries are and were always quoted and we never had any issues with that. What exactly is the error code and message that you are getting?

One common situation where you would get an error trying to execute an ODB-generated query is if the corresponding tables haven't been created.

Boris