Board index » delphi » D3 SQL problem

D3 SQL problem

I have a major problem converting a live D1 App to D3,
involving SQL. The SQL statement:

SELECT  JobDets.JobCardNo,  <rest of fields list>
FROM ConDets INNER JOIN
(JobDets LEFT JOIN CertDets
ON JobDets.CertNo = CertDets.CertNo)
ON ConDets.ContractNo = JobDets.ContractNo

fails on the TQuery Prepare and/or Open statement
with the error 'Invalid parameter' !!
Note - there is no parameter in sight !

However, it also fails with the same message when
there are legit parameters.

This, and more complex queries work happily in D1
(and VB4 and Acc97), while they all fail in the same way
in D3.

One JOIN (ie 2 tables) is OK, but more than one, of any type,
brings on the problem.

Where tables are joined using the x.x = y.y format all works
well, but this of course only provides Equijoins, thus the LEFT and
RIGHT joins are often required.

Platform is Win95, Delphi is 3.0, Tables are DBase.

I can't believe anything as simple as this has slipped thru Borland's
validation net, so I guess there may be a (non-published of course)
change in the syntax rules. Or is it a genuine bug ?

Any help much appreciated.

Please remove "notospam." from e-mail address for replies.

 

Re:D3 SQL problem


Quote
Peter Houchin wrote in message <01bce520$7f8ea420$LocalHost@mc3>...
>I have a major problem converting a live D1 App to D3,
>involving SQL. The SQL statement:

>SELECT  JobDets.JobCardNo,  <rest of fields list>
>FROM ConDets INNER JOIN
>(JobDets LEFT JOIN CertDets
>ON JobDets.CertNo = CertDets.CertNo)
>ON ConDets.ContractNo = JobDets.ContractNo

>fails on the TQuery Prepare and/or Open statement
>with the error 'Invalid parameter' !!
>Note - there is no parameter in sight !

>However, it also fails with the same message when
>there are legit parameters.

>This, and more complex queries work happily in D1
>(and VB4 and Acc97), while they all fail in the same way
>in D3.

>One JOIN (ie 2 tables) is OK, but more than one, of any type,
>brings on the problem.

>Where tables are joined using the x.x = y.y format all works
>well, but this of course only provides Equijoins, thus the LEFT and
>RIGHT joins are often required.

>Platform is Win95, Delphi is 3.0, Tables are DBase.

>I can't believe anything as simple as this has slipped thru Borland's
>validation net, so I guess there may be a (non-published of course)
>change in the syntax rules. Or is it a genuine bug ?

>Any help much appreciated.

>Please remove "notospam." from e-mail address for replies.

Hi Peter,

Here is an SQL that I am using in D3, win95 that works:

SELECT Products.ProductID,Products.SortName, <etc.>
FROM ((Products LEFT JOIN ChemicalState ON Products.ProductID
    = ChemicalState.ProductID) LEFT JOIN Units ON ChemicalState.ProdStateID
    = Units.ProdStateID) LEFT JOIN InventoryTransactions ON
Units.ProdStateUnitID
    = InventoryTransactions.ProdStateUnitID
WHERE InventoryTransactions.UnitsReceived > 0
ORDER BY Products.SortName, InventoryTransactions.TransactionDate;'

HTH
John

Other Threads