BdpCommand error

Thread View: borland.public.delphi.database.bdp

6 messages

[T] Back to Threads [V] Tree View [G] Group Articles

6 total messages Started by Richard Guillen Fri, 10 Nov 2006 15:53

#142

Author: Richard Guillen
Date: Fri, 10 Nov 2006 15:53

62 lines
2829 bytes

Hi anybody,

I have a problem when I try to Execute a command with multiple select 
statement.

I have the following:

Const
  SQL = 'SELECT SUM(QUANTITY) TOTAL FROM "BARD"."B_INCOMING_DET" WHERE LOT = 
{0};'+
        'SELECT NVL(H.PO, 0) TOT_LOT, (SELECT NVL(SUM(D.QUANTITY), 0) FROM 
"BARD"."B_INCOMING_DET" D '+
        '       WHERE (H.LOT = D.LOT) AND (D.SEC >= 0)) TOT_DET '+
        'FROM "BARD"."B_INCOMING_HDR" H WHERE (H.LOT = {1});';
var
  OraSession: BdpConnection;
  cmdSelect : BdpCommand;
  drSelect  : BdpDataReader;
begin
  Result:= 0;
  OraSession:= BdpConnection.Create(ConnectionString);
  cmdSelect:= BdpCommand.Create(System.&String.Format(SQL, [QuotedStr(LOTE), 
QuotedStr(LOTE)]), OraSession);
  OraSession.Open;
  Try
    drSelect:= cmdSelect.ExecuteReader;
    While drSelect.Read do
      If drSelect['TOTAL'].ToString <> '' Then
        Result:= System.Convert.ToInt32(drSelect['TOTAL']);
        If Result = 0 Then
......

When I run this I get the following error:

Execute failed. ORA-00911: invalid character
Description: An unhandled exception occurred during the execution of the 
current web request. Please review the stack trace for more information 
about the error and where it originated in the code.

Exception Details: Borland.Data.Common.BdpException: Execute failed. 
ORA-00911: invalid character

Source Error:


Line 611:  OraSession.Open;
Line 612:  Try
Line 613:    drSelect:= cmdSelect.ExecuteReader;
Line 614:    While drSelect.Read do
Line 615:      If drSelect['TOTAL'].ToString <> '' Then


Can somone tell me what is my error?

I beleve my error is the separator between sql's but I don't know what type 
of separator should be.

Thank for your help.

I am using Delphi 2005.Net and Oracle 10g.

Richard Guillen.

Re: BdpCommand error

#143

Author: Bob Swart
Date: Fri, 10 Nov 2006 17:04

16 lines
1408 bytes

Hi Richard,

> Const
>   SQL = 'SELECT SUM(QUANTITY) TOTAL FROM 

Don't you need a comma between the SUM(QUANTITY) and the TOTAL field?

> Richard Guillen. 

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Re: BdpCommand error

#144

Author: Richard Guillen
Date: Fri, 10 Nov 2006 22:34

28 lines
1715 bytes

Hi Bob,

Thank you for your answer.

I run those two sentences in the SQL Plus of Oracle and It run very well.

I get the error only when I execute the BdpCommand.ExcecuteReader.

Reichard.

"Bob Swart" <b.swart@chello.nl> wrote in message 
news:4554B123.6050105@chello.nl...
> Hi Richard,
>
>> Const
>>   SQL = 'SELECT SUM(QUANTITY) TOTAL FROM
>
> Don't you need a comma between the SUM(QUANTITY) and the TOTAL field?
>
>> Richard Guillen.
>
> Groetjes,
>           Bob Swart
>
> -- 
> Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
> Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
> New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Re: BdpCommand error

#145

Author: Larry Siemer
Date: Sat, 11 Nov 2006 02:55

17 lines
1075 bytes

Bob Swart wrote:

> Hi Richard,
> 
> > Const
> >  SQL = 'SELECT SUM(QUANTITY) TOTAL FROM 
> 
> Don't you need a comma between the SUM(QUANTITY) and the TOTAL field?
> 
> > Richard Guillen. 
> 
> Groetjes,
>           Bob Swart

It looks like TOTAL is an Alias Name for Sum(Quantity).

--

Re: BdpCommand error

#146

Author: Bob Swart
Date: Sat, 11 Nov 2006 10:35

14 lines
1468 bytes

Hi Larry,

> It looks like TOTAL is an Alias Name for Sum(Quantity).

Ah yes. I personally would use the "AS" keyword between the 
Sum(Quantity) and TOTAL to make it clear...

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Re: BdpCommand error

#147

Author: Bob Swart
Date: Sat, 11 Nov 2006 10:37

19 lines
1687 bytes

Hi Richard,

> I run those two sentences in the SQL Plus of Oracle and It run very well.
> 
> I get the error only when I execute the BdpCommand.ExcecuteReader.

OK, what happens if you split them, and run each of them in the 
BdpCommand's ExecuteReader method? Does it work (this will verify that 
it may be the separation character that gives you the problem).

> Reichard.

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads