Problem with ORDER BY in Dynamic SQL

Problem with ORDER BY in Dynamic SQL

Post by Patrick Goeb » Tue, 05 Dec 1995 04:00:00



I am attempting to write a simple Dynamic SQL program using Pro*C 1.3
and RDBMS 6.0.33.  While I can substitute a place holder in a WHERE
clause fine, it seems to be ignored when used in an ORDER BY clause.

For example:

char sql_text[50];
char var_text[6];

strcpy(sql_text, "SELECT name_last FROM profile WHERE name_last = :x");
strcpy(var_text, "Goebel");
EXEC SQL PREPARE sql_stmt FROM :sql_text;
EXEC SQL DECLARE users CURSOR FOR sql_stmt;
EXEC SQL OPEN users USING :var_text;

works fine.  But

char sql_text[41];
char var_text[9];

strcpy(sql_text, "SELECT name_last FROM profile ORDER BY :x");
strcpy(var_text, "name_last");
EXEC SQL PREPARE sql_stmt FROM :sql_text;
EXEC SQL DECLARE users CURSOR FOR sql_stmt;
EXEC SQL OPEN users USING :var_text;

fails; i.e., the output is *not* ordered by last name but by row id.
It's as if the value of the host variable var_text is not being passed
when the OPEN ... USING statement is executed.

Any suggestions would be greatly appreciated.

Cheers,
------------------------------------------------------------------->
 Patrick Goebel           | voice: (415) 321-2052     ------  __o
 CASBS/GCN/VeloNet        |   fax: (415) 321-1192  -------  _`\<,_
 202 Junipero Serra Blvd. |                           ---- (*)/ (*)
 Stanford, CA 94305       | High Speed Digital Commuter
 http://cycling.org       | Gweebe in '95 -> Ironman in 2000
------------------------------------------------------------------->

 
 
 

Problem with ORDER BY in Dynamic SQL

Post by Bill Manry - Oracle Cor » Wed, 06 Dec 1995 04:00:00


Patrick,

You can't use a bind variable to specify a column name (or a SELECT
list position) in an ORDER clause.  The value you are binding for ":x"
is being treated as an expression (value), equivalent to issuing the
the SQL statement

 SELECT name_last FROM profile ORDER BY 'name_last'

which isn't meaningful.  To get what you want you will have to copy
the column name into the SQL statement text prior to the EXEC SQL
PREPARE.

Hope this helps.

/b
--
Bill Manry  -  Mainframe and Integration Technologies  -  Oracle Corporation
Standard disclaimer applies.

 
 
 

Problem with ORDER BY in Dynamic SQL

Post by a.. » Wed, 06 Dec 1995 04:00:00


|> I am attempting to write a simple Dynamic SQL program using Pro*C 1.3
|> and RDBMS 6.0.33.  While I can substitute a place holder in a WHERE
|> clause fine, it seems to be ignored when used in an ORDER BY clause.

You are comparing two different class of dynamic sql statements.

|> char sql_text[50];
|> char var_text[6];
|>
|> strcpy(sql_text, "SELECT name_last FROM profile WHERE name_last = :x");
|> strcpy(var_text, "Goebel");
|> EXEC SQL PREPARE sql_stmt FROM :sql_text;
|> EXEC SQL DECLARE users CURSOR FOR sql_stmt;
|> EXEC SQL OPEN users USING :var_text;

In this case, you are passing value of a column. This may not be a dynamic
statement. You can even write it as follows. Note, what you are doing is also one
of the method (Dynamic sql method 3) and it has advantages too.

EXEC SQL DECLARE users CURSOR FOR
     SELECT name_last FROM profile
      WHERE name_last=:var_text;
EXEC SQL OPEN users;

Note that with this you do not have to prepare a statement. This will give same
results.

|> char sql_text[41];
|> char var_text[9];
|>
|> strcpy(sql_text, "SELECT name_last FROM profile ORDER BY :x");
|> strcpy(var_text, "name_last");
|> EXEC SQL PREPARE sql_stmt FROM :sql_text;
|> EXEC SQL DECLARE users CURSOR FOR sql_stmt;
|> EXEC SQL OPEN users USING :var_text;

In this case, your are not passing value to a column but the name of a column.
This is a dynamic sql in true sense. In this case, you will have to form complete
statement before you prepare it. Hence, you should try as follows.

strcpy (sql_text, "SELECT name_last FROM profile ORDER BY ");
strcpy (var_text, "name_last");

strcat (sql_text, var_text);
EXEC SQL PREPARE sql_stmt FROM :sql_text;
EXEC SQL DECLARE users CURSOR FOR sql_stmt;
EXEC SQL OPEN users;

|> fails; i.e., the output is *not* ordered by last name but by row id.

 
 
 

Problem with ORDER BY in Dynamic SQL

Post by Patrick Goeb » Thu, 07 Dec 1995 04:00:00


Thanks!  Now I understand.

Cheers,
------------------------------------------------------------------->
 Patrick Goebel           | voice: (415) 321-2052     ------  __o
 CASBS/GCN/VeloNet        |   fax: (415) 321-1192  -------  _`\<,_
 202 Junipero Serra Blvd. |                           ---- (*)/ (*)
 Stanford, CA 94305       | High Speed Digital Commuter
 http://cycling.org       | Gweebe in '95 -> Ironman in 2000
------------------------------------------------------------------->

 
 
 

1. Order by Variable in Embedded Dynamic SQL in Pro*C

Regarding someone's query on using a variable in an Order By clause in Dynamic
SQL in Pro*C.
 I think the problem is that rather than ordering by the column, it's ordering
by a constant, similar to a SELECT * FROM EMP ORDER BY 'DEPT'. As this is
constant in all rows, it has no effect on the ordering.
 Possible solutions are - can you include the entire 'ORDER BY col' in the
variable ? Alternatively, if the column is one of the selection can you do a
hardcoded ORDER BY 1 rather than explicitly identify the column/expression ?
Would a decode help (similar to an ORDER BY DECODE(variable, 'DEPT', DEPT, 1) ?

  Hope one of those pans out.
  Gary
--
Gary Myers, Analyst/Programmer, Kaisha Technology, West of England

2. typeface conversions

3. High cardinality tables and multiple row ins => ROLLBACK SEG PROBLEM!

4. Reading an XML DataSet without Sequence Numbers

5. Dynamic Table Names in Dynamic SQL

6. iPaq, Cell Phone & Bluetooth Questions

7. Pro*C/Dynamic SQL, can the WHERE clause be dynamic?

8. Wiring cisco DB60 to AT&T Systimax

9. How to call dynamic remote stored procedure in the dynamic sql

10. dynamic sql problem

11. Problems with dynamic SQL in PRO*C

12. Problem with embedded dynamic pl/sql in/out parameters