I am trying to figure out how to get my query to run with partially filled parameters.
When I run a parameterized query in DBSys, then I get results as if there were no parameters.
I do the same in my code (turning of paramcheck), and I don't get errors, but I don't get any data, either.
How can I get the same results as in DBSys.  What am I missing?

Connie;
What does your SQL statement look like?

Allan
"Connie McBride" <conniem@axcis.net> wrote in message
news:829A5642-4FA4-4469-994F-BE992A666B3E@news.elevatesoft.com...
> I am trying to figure out how to get my query to run with partially filled
parameters.
> When I run a parameterized query in DBSys, then I get results as if there
were no parameters.
> I do the same in my code (turning of paramcheck), and I don't get errors,
but I don't get any data, either.
> How can I get the same results as in DBSys.  What am I missing?
>

Connie


As far as I know you can't run a parameterised query in DBSys. You have to actually type in the parameters.

eg if I run

select * from companies where upper(_name) like upper(:startofname)

I get nothing

If I run

select * from companies where upper(_name) like upper('B%')

I get 883 companies.


Roy Lambert

"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote:

Connie;
What does your SQL statement look like?

Allan
"Connie McBride" <conniem@axcis.net> wrote in message
news:829A5642-4FA4-4469-994F-BE992A666B3E@news.elevatesoft.com...
> I am trying to figure out how to get my query to run with partially filled
parameters.
> When I run a parameterized query in DBSys, then I get results as if there
were no parameters.
> I do the same in my code (turning of paramcheck), and I don't get errors,
but I don't get any data, either.
> How can I get the same results as in DBSys.  What am I missing?
>

It looks like this:
Select I.InvoiceDate, I.InvoiceNumber, I.CustName, I.CustUID, I.TaxSubTotal, I.NonTaxSubTotal,I.SalesTax, I.PrepaidUsed, I.Total,
S.InvoiceUID as InvoiceUID, S.UID as OrderUID, I.Total - (I.Deposit + I.PrepaidUsed) as Beginning, (I.Payments + I.Deposit + I.PrepaidUsed)
as TotalPayments
from Invoice I, Install S
where
  I.IsFromOrder = true and
  I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate and
  S.InvoiceUID = I.UID


However, move the parameterized section to the first line of the where clause, I get 0 records.
How funny is that?

Connie McBride <conniem@axcis.net> wrote in news:1CDA28CF-88FF-4088-942E-
869AA3FF109D@news.elevatesoft.com:

> where
>    I.IsFromOrder = true and
>    I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate and
>    S.InvoiceUID = I.UID
>

What happens if you alter it to:

where
  I.IsFromOrder = true and
  (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and
  (:EndDate IS NULL OR I.InvoiceDate <= :EndDate) and
  S.InvoiceUID = I.UID

?
--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote:

Connie McBride <conniem@axcis.net> wrote in news:1CDA28CF-88FF-4088-942E-
869AA3FF109D@news.elevatesoft.com:

> where
>    I.IsFromOrder = true and
>    I.InvoiceDate >= :StartDate and I.InvoiceDate <= :EndDate and
>    S.InvoiceUID = I.UID
>

What happens if you alter it to:

where
  I.IsFromOrder = true and
  (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and
  (:EndDate IS NULL OR I.InvoiceDate <= :EndDate) and
  S.InvoiceUID = I.UID

?
--
Chris
If    (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and
  (:EndDate IS NULL OR I.InvoiceDate <= :EndDate) and
is the first line, I get 0 records.

where
  I.IsFromOrder = true and
  (:StartDate IS NULL OR I.InvoiceDate >= :StartDate) and
  (:EndDate IS NULL OR I.InvoiceDate <= :EndDate) and
  S.InvoiceUID = I.UID

in fact, if I put it ANYWHERE in the sql statement, I get 0 records.

Connie,

<<I am trying to figure out how to get my query to run with partially filled
parameters. When I run a parameterized query in DBSys, then I get results as
if there were no parameters.  I do the same in my code (turning of
paramcheck), and I don't get errors, but I don't get any data, either.  How
can I get the same results as in DBSys.  What am I missing? >>

DBSYS is simply using ParamCheck=False, so if you're getting different
results then it must be because you're using a different version of DBISAM
in your application from that which DBSYS is using.  Did you check the
version numbers/build numbers to make sure that you're using the same
version ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

<<I am trying to figure out how to get my query to run with partially filled
parameters. When I run a parameterized query in DBSys, then I get results as
if there were no parameters.  I do the same in my code (turning of
paramcheck), and I don't get errors, but I don't get any data, either.  How
can I get the same results as in DBSys.  What am I missing? >>

DBSYS is simply using ParamCheck=False, so if you're getting different
results then it must be because you're using a different version of DBISAM
in your application from that which DBSYS is using.  Did you check the
version numbers/build numbers to make sure that you're using the same
version ?

--
Tim Young
Elevate Software
www.elevatesoft.com

I am using the correct versions.
I figured out the 'problem'.
If I have the parameterized statement of the where clause as the first line, then I get 0 records.
If it is the last line, I get the data.

Connie,

<< I am using the correct versions.
I figured out the 'problem'.
If I have the parameterized statement of the where clause as the first
line, then I get 0 records.
If it is the last line, I get the data. >>

That shouldn't make any difference at all.  Are you sure that moving the
expression isn't altering the meaning of the WHERE clause ?

--
Tim Young
Elevate Software
www.elevatesoft.com