Urgent: Delphi5 can't execute sql statement with groupby_clause and parameters.

just to ask,
why you use a group by clause,
there is no aggregate-field in your select

meikl ;-)

Thanks for your concern.

Since I want to develop a Query Tool, where the user can build sql clause and execute it for displaying the query result.

May be you must use DISTINCT instead of GROUP BY.

Ivan

Hello IPCH,

The sql statement is defined according user's selection. So can't use DISTINCT instead of GROUP BY.

Would you kindly suggest?

well i am not really fit with pl sql but the only difference i was able to find is a missing blank

   Add('FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )');
   Add('from  TENDER ');
compared to
   Add('select name, getName( :Sys_User, :PASSWD) ');
   Add('from employee');
   
probably that is your problem.

a blank in the parameters doesn't matter, sancho
(pl/sql is my primary job)

atleast what i could guess is,
that a pragma in the pl/sql package is missed
(for avoiding mutating tables during the select),
but the ora-00979 does not point to this

meikl ;-)

well i am sorry i was just trying to find your problem and after this is not in the parameters but between the select list and the from statement ithought it might have an effect some servers are pretty finicking with things like this ... if oracle is not, fine!

but just another hint just as a trial to find a beginning: have you traced what does really arrives at the server?

If the statement you expect to send is correct (and i guess you know that better than i) but the server does not accept it it seems as if the statement you expect to send is not the statement you really send. I have never seen that delphi causes this, but i also can't see where this should happen in your code. So I'd check if there does arrive what you expect, if so recheck it does run in Toad if yes too i'd search in orcle doc's why ...

Hi Sfock,

The first sql statement can run in Toad. like below:
select
TEN_ID,
(FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL ))
from  TENDER
group by
TEN_ID,
FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )

As far the transfer, I think the oracle server accepted the statement expected of Delphi5 generation. You can make references about the trace log #9 of SQL Monitor:
1       09:32:16  Log started for: Delphi 5
2       09:32:22  Log started for: Project1
3       09:32:28  SQL Prepare: ORACLE - select
TEN_ID,
(FSK_PKG.DECRYPT_FIELD( :1, :2, TENDER.TEN_ID, TENDER.EST_TEN_VAL ))
from  TENDER
group by
TEN_ID,
FSK_PKG.DECRYPT_FIELD( :3, :4, TENDER.TEN_ID, TENDER.EST_TEN_VAL )

4       09:32:28  SQL Data In: ORACLE - Param = 1, Name = , Type = fldZSTRING, Precision = 7, Scale = 0, Data = SM2-CLM
5       09:32:28  SQL Data In: ORACLE - Param = 2, Name = , Type = fldZSTRING, Precision = 4, Scale = 0, Data = TEST
6       09:32:28  SQL Data In: ORACLE - Param = 3, Name = , Type = fldZSTRING, Precision = 7, Scale = 0, Data = SM2-CLM
7       09:32:28  SQL Data In: ORACLE - Param = 4, Name = , Type = fldZSTRING, Precision = 4, Scale = 0, Data = TEST
8       09:32:28  SQL Transact: ORACLE - Set autocommit on/off
9       09:32:28  SQL Execute: ORACLE - select
TEN_ID,
(FSK_PKG.DECRYPT_FIELD( ?, ?, TENDER.TEN_ID, TENDER.EST_TEN_VAL ))
from  TENDER
group by
TEN_ID,
FSK_PKG.DECRYPT_FIELD( ?, ?, TENDER.TEN_ID, TENDER.EST_TEN_VAL )

10      09:32:28  SQL Error: ORACLE - ORA-00979: not a GROUP BY expression

11      09:32:28  SQL Error: ORACLE - Unmapped SQL Error Code: 979
12      09:32:28  SQL Stmt: ORACLE - Reset
13      09:32:29  SQL Data In: ORACLE - Rows affected = 0
14      09:32:29  SQL Stmt: ORACLE - Close

So I wonder if Delphi5 can't assign valid parameter values to the parameters of groupby clause?

SQL does not accept field names as parameters, which is what you are trying to do in this group by clause.  I am not familiar with oracle, but it would be a serious safety loophole to leave.

In DB2 I would do this:

select
  TEN_ID,
  (FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )) as PKG
from  TENDER
group by
  TEN_ID,
  PKG

Hi, swift99,

Oracle can't group by field alias such as PKG. Once the alise isn't same to one field name. Then warn ORA-00904: invalid column name.

:o( Too bad.

try group by 1,2

in SQL92 parlance this means group by the 1st and 2nd columns, independent of their names.

more guessing,
if the bde is used,
the bde cannot handle packages

move your procedure/function out of the package
as standalone procedure/function
and try again

btw. a group by caluse is not needed anyway,
by this sample selects

meikl ;-)

Hello swift99,
Do you try "group by 1,2" in Oracle? I can't run it. Oracle will warn ORA-00979: not a GROUP BY expression. For SQL-92 parlance, can you provide url to introduce the details?

In the group by clause, add tender.ten_id and tender.est_ten_val in the front as below:

 Add('TENDER."TEN_ID", ');
 Add('TENDER."EST_TEN_VAL",');
 Add('FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )');

The SQL statement can work.

I'm not an Oracle person, and don't have oracle available.  SQL-92 is the "standard" or vanilla SQL that most servers extend, so it should always be more limited than whatever vendor-specific implementations are there.

I may be surprised, the "group by 1,2" may be a DB2ism.  DB2 is as vanilla as it gets syntax wise, but this maybe a syntax that is unique to IBM.  I'll look around for some URL's.

Hello swift99,

Thanks for your kindness. Waiting for yours.

Hello swift99,

Thanks for your kindness. Waiting for yours.

Start at http://www.netaktive.com/biblio/sql/SQL98/standards.html

Hello everybody,

I understand the clause of SQL error. If I want to group by the expression(including params) by Delphi5 like:
(FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )
The expression related to two practical fields. ie. TENDER.TEN_ID and TENDER.EST_TEN_VAL. So I must group by them at the same time. The SQL statement can work by Delphi5. But I didn't know why Toad can run original statement smoothly. If BDE of Delphi5 can't support the groupby statement(need to assign params)?

Please note if use param value insteal of param name, the original statement can run by Delphi like:
 with Query1, SQL do begin
   close;
   clear;
   Add('select ');
   Add('TEN_ID, ');
   Add('FSK_PKG.DECRYPT_FIELD( 'SM2-CLM', 'TEST' TENDER.TEN_ID, TENDER.EST_TEN_VAL )');
   Add('from  TENDER ');
   Add('group by ');
   Add('TEN_ID, ');
   Add('FSK_PKG.DECRYPT_FIELD( 'SM2-CLM', 'TEST' TENDER.TEN_ID, TENDER.EST_TEN_VAL )');
   Open;
 end; {with}

In order by invoke the error. We can use subqueries in from clause.
 with Query1, SQL do begin
   close;
   clear;
   Add('select TEN_ID, EST_TEN_VAL ');
   Add('from (select TENDER."TEN_ID", ');
   Add('FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD TENDER.TEN_ID, TENDER.EST_TEN_VAL ) "EST_TEN_VAL"');
   Add('from  TENDER) TENDER ');
   Add('group by ');
   Add('TEN_ID, ');
   Add('EST_TEN_VAL');
   ParamByName('PASSWD').AsString := 'TEST';
   ParamByName('SYS_USER').AsString := 'SM2-CLM';
   Open;
 end; {with}

Please advise!

BDE implements a subset of SQL.  Unless you make it otherwise, it takes your SQL, interprets it, and builds new SQL to pass on to the back end.

Options:
1. Set the BDE components to "pass through" the SQL.  I believe that it is a setting on the TDatabase component.

2. Switch to CLX components which do not have the overheads (or interference) of the intermediate interpreter.  There is a CLX DLL for Oracle, so that should work okay.

Hello swift99,

Can you introduce some CLX components and show some examples?

For #1 of Options:
If I can deploy TDatabase.TransIsolation property? Please advise.

Pass-through SQL for BDE components - I see it exists in various Delphi online help, but no examples.  I am using DB2 API directly most of the time so I don't have these middle pieces except for simple applications.

CLX components - Use SQLConnection instead of TDatabase, and SQLQuery instead of TQuery.

Usage is remarkably similar to BDE components.  You have one or two less layers of abstraction between you and the DBMS, so you will need to be aware of the limits and characteristics of the DBMS.  At this level you don't have the bidirectional buffering that the BDE builds for you so if you need interactive scrolling you will want to use a client dataset.

About the Oracle trace you posted earlier:

select
TEN_ID,
(FSK_PKG.DECRYPT_FIELD( :1, :2, TENDER.TEN_ID, TENDER.EST_TEN_VAL ))
from  TENDER
group by
TEN_ID,
FSK_PKG.DECRYPT_FIELD( :3, :4, TENDER.TEN_ID, TENDER.EST_TEN_VAL )

Note that Oracle is expecting a third and fourth parameter - I bet BDE is only providing two.

Also, from the online Delphi manual:

Note:     Some SQL database systems require strict case-sensitivity for names of metadata objects in SQL statements. These SQL databases typically have problems with the way metadata names are passed by the BDE in live queries and an exception is raised. A workaround that helps in most cases is to enclose the metadata object references (like table and column names) in quotation marks.

Dear swift99,

If it's convenient for you, can you show a sample for using DB2 API?

If you know any URL resources to introduce CLX, please list. Thanks.

Thank all for the helps.