At 06:02 PM 6/01/2005 +0000, you wrote:

>Hi
>
>I'm stuck on some sql that I am converting from a stored procedure and
>I am getting an error
>
>List index out of bounds (2)
>
>The sql is
>
>INSERT INTO PIN_REPORT(VOUCHER_ID,PRODUCT_ID)
>SELECT DISTINCT VOUCHER_ID,PRODUCT_ID
> FROM PINS
> WHERE
> (PINS.DOWNLOAD_DATE >= :startdate and PINS.DOWNLOAD_DATE <
>:enddate + 1)
> OR
> (PINS.STATUS_CHANGED >= :startdate and PINS.STATUS_CHANGED <
>:enddate +1 AND PINS.STATUS = 5)
>
>
>If I remove the OR statement and put in just one of the statements
>either side of the or it works, so it's not a variable or expresion
>problem.
>Also the where expression works fine in a stored procedure.
>I have tried extra brackets around the whole expression after the
>WHERE statement but it then gives the List index error.
>
>Can anyone suggest anything ?.

Explanation first. The :varname syntax inside your PSQL represents a
variable. When you transplant your statement to DSQL, those :varname
elements are not variables, but replaceable parameters. Replaceable
parameters in SQL at the API are all represented by the character "?" and
are purely positional; that is, they have no identifier to distinguish one
from the other.

Still in explanation mode....Borland's interfaces provide the means for the
application to identify replaceable parameters by name. As a vehicle for
named parameters, they chose to mimic the syntax of variables in PSQL. As
a result, developers who use Borland tools and others that follow Borl
conventions can be totally bamboozled into thinking that replaceable
parameters are variables.

So - the first thing to do is to get past this belief, otherwise it will
keep biting you in unexpected places. Another good idea is to use a tool
that can show you a trace of the SQL statement that actually gets passed
across the wire.

Why you're getting the List Index out of bounds error from Delphi:

Outside the PSQL environment, your statement contains not **two
variables**, but **four parameters**. In the Borland-cognate interface,
you have defined two parameters, so the params[] list is out of bounds as
soon as you try to apply a value to the (non-existent) third parameter.

Restate your statement like this (and I recommend not complicating your
params by forcing the client to perform calculations on the parameter values)

INSERT INTO PIN_REPORT(VOUCHER_ID,PRODUCT_ID)
SELECT DISTINCT VOUCHER_ID,PRODUCT_ID
FROM PINS
WHERE
(PINS.DOWNLOAD_DATE >= :startdate1 and PINS.DOWNLOAD_DATE <
:enddate1 )
OR
(PINS.STATUS_CHANGED >= :startdate2 and PINS.STATUS_CHANGED <
:enddate2
AND PINS.STATUS = 5)

At Prepare time, the server will return the metadata attributes of the four
parameters. Once the statement is prepared, apply your host variables to
each of the four parameters. You can do this positionally (client-side,
the Params structure is managed in a zero-based array) or ByName.

....
YourStartDateVar := SomeDateTimeValue;
YourEndDate := SomeOtherDateTimeValue + 1;

with YourStatementObject do
begin
if not Prepared then Prepare;
// ----------------------------------------------------------
// Positionally:

Params[0].AsDateTime := YourStartDateVar;
Params[1].AsDateTime := YourEndDateVar;
Params[2].AsDateTime := YourStartDateVar; // same var, different param
Params[3].AsDateTime := YourEndDateVar; // ditto

// or ByName:

ParamByName('startdate1').AsDateTime := YourStartDateVar;
ParamByName('enddate1').AsDateTime := YourEndDateVar;
ParamByName('startdate2').AsDateTime := YourStartDateVar;
ParamByName('enddate2').AsDateTime := YourEndDateVar;
// ----------------------------------------------------------
Execute; // or ExecSQL, depends on which components you're using
end;

Incidentally, the client-side behaviours and rules for parameters also vary
according to which components you're using. While it's important for you
to know this, it's too far off-topic for this list - ask in the support
list of the particular components you're using.

Cheers,
./hb