?
Solved

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

Posted on 2003-03-11
26
Medium Priority
?
544 Views
Last Modified: 2010-04-03
I try to execute an Oracle sql statement by Delphi5, oracle warns general SQL error. Ora-00979: not a Group By expression. But I execute same sql statement by Toad. Then can get query result.
  with Query1, SQL do begin
    close;
    clear;
    Add('select ');
    Add('TEN_ID, ');
    Add('FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )');
    Add('from  TENDER ');
    Add('group by ');
    Add('TEN_ID, ');
    Add('FSK_PKG.DECRYPT_FIELD( :SYS_USER, :PASSWD, TENDER.TEN_ID, TENDER.EST_TEN_VAL )');
    ParamByName('PASSWD').AsString := 'TEST';
    ParamByName('SYS_USER').AsString := 'SM2-CLM';
    Open;
  end;

Moreover, I try to imitate a similar SQL statement. It can work normally.
  with Query2, SQL do begin
    Close;
    Clear;
    Add('select name, getName( :Sys_User, :PASSWD) ');
    Add('from employee');
    Add('group by name, getName( :Sys_User, :PASSWD) ');
    ParamByName('Sys_User').AsString := 'Tom';
    ParamByName('PASSWD').AsString := 'dd';
    Open
  end;

So I wonder if the mistake is caused by using package method of Oracle. There is a package method in groupby clause of first sql statement. So that Delphi5 can't execute it. How to solve it? If I can't assign parameter by ParamByName method, and have to assign parameter value manually?

Please advise. Thanks a lot.
0
Comment
Question by:merry_prince
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 3
  • +2
26 Comments
 
LVL 27

Expert Comment

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

meikl ;-)
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8110102
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.
0
 
LVL 2

Expert Comment

by:IPCH
ID: 8110554
May be you must use DISTINCT instead of GROUP BY.

Ivan
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:merry_prince
ID: 8110645
Hello IPCH,

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

Would you kindly suggest?
0
 
LVL 3

Expert Comment

by:sfock
ID: 8114541
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8114708
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 ;-)
0
 
LVL 3

Expert Comment

by:sfock
ID: 8114843
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 ...
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8116075
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?
0
 
LVL 6

Expert Comment

by:swift99
ID: 8116563
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
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8116664
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.
0
 
LVL 6

Expert Comment

by:swift99
ID: 8119447
:o( Too bad.

try group by 1,2

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

Expert Comment

by:kretzschmar
ID: 8119598
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 ;-)
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8124462
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?
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8124591
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.
0
 
LVL 6

Expert Comment

by:swift99
ID: 8127880
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.
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8132977
Hello swift99,

Thanks for your kindness. Waiting for yours.
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8133150
Hello swift99,

Thanks for your kindness. Waiting for yours.
0
 
LVL 6

Expert Comment

by:swift99
ID: 8136314
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8140757
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!
0
 
LVL 6

Expert Comment

by:swift99
ID: 8151586
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.
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8178248
Hello swift99,

Can you introduce some CLX components and show some examples?
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8178496
For #1 of Options:
If I can deploy TDatabase.TransIsolation property? Please advise.
0
 
LVL 6

Expert Comment

by:swift99
ID: 8179032
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.

0
 
LVL 1

Author Comment

by:merry_prince
ID: 8192320
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.
0
 
LVL 6

Accepted Solution

by:
swift99 earned 60 total points
ID: 8195084
The Delphi online help is a wonderful source - it's all I have needed to use.

I would also check out www.borland.com and www.torry.net.

Here's a partial example of using the DB2 API directly.  you can see why BDE and CLX are used more frequently.

procedure GetTEN_ID (acds: TClientDataset);
var
  dataset: Variant;
  aCursor: handle;
  aBuffer: PCHar;
begin
  try
    sqlString := 'select TEN_ID, (FSK_PKG.DECRYPT_FIELD( :1, :2, TENDER.TEN_ID, TENDER.EST_TEN_VAL ))'+
      'from  TENDER group by 1, 2';

    DB2AllocDataArea( aBuffer, self.ComponentIndex, DataArea.Count );
    CheckCA( CommArea );
    DB2SetDataArea( aBuffer, 0, DataArea^ );

    // populate parameters in here ... Not shown due to complexity
    // ...
    DB2OpenCursor( aCursor, AllocDataArea(ptInput) );
    while result = 0 do
    begin
      DB2Fetch( aCursor, AllocDataArea(ptOutput) );
      if result = 0 then
          MoveBufferToClientDataset (aCursor, aCds);
    end;
    DB2CloseCursor (aCursor);
  finally
    DB2FreeDataArea (aBuffer);
  end;
end;
0
 
LVL 1

Author Comment

by:merry_prince
ID: 8199499
Thank all for the helps.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question