Hi,

Please find a test C# project here which demonstrates the problem :-
http://www.pioneersoftware.co.uk/files/temp/ElevateDB_Test2.7z

And here's the database to go with the sample app :-
http://www.pioneersoftware.co.uk/files/temp/Sample_Database.7z

There are two buttons :-

[1] "Run SELECT" runs a simple query and provides the results
[2] "Run PARAM QUERY" runs the EXACT SAME query, but using "EDBParameters"
for parameters instead

The results are different.

Our application has been live with EDB for well over a year error and this
problem has only just come to our attention - probably because we only use a
parameterized query in one place in our application for performance
purposes. I've tested it with the latest build of EDB and also an older
build too and the problem exists in both.

Hope you can help!

Thanks,

Hedley Muscroft
Pioneer Software Ltd

Hi Tim - do you think you'll have a chance to look at this anytime soon?
It's causing problems for one client in particular - thanks!

Hedley,

<< [1] "Run SELECT" runs a simple query and provides the results
[2] "Run PARAM QUERY" runs the EXACT SAME query, but using "EDBParameters"
for parameters instead

The results are different. >>

I'm still looking into another issue, and should have an answer on this for
you shortly.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

<< The results are different. >>

Okay, there are two issues here:

1) You're adding new parameters when EDB already auto-creates parameters
from the SQL statement.  See here for information on the
EDBCommand.ParamCheck property:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2dac&category=3&topic=23

2) I need to update the provider to handle duplicate parameter name
assignments for the next build.  However, for now, if you just assign the
parameters by position, you will get the correct results.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hedley,

<< 2) I need to update the provider to handle duplicate parameter name
assignments for the next build.  However, for now, if you just assign the
parameters by position, you will get the correct results. >>

Scratch that - the base parameter implementation really doesn't allow any
way of doing this, at least as far as I can see.  You'll have to use unique
parameter names, or use the positions in order to do this.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for looking into this, however unless I'm being stupid (always a
possibility), this doesn't solve the problem.

>> 1) You're adding new parameters when EDB already auto-creates parameters

OK, so in the sample application, I can simply remove the "Parameters.Add"
lines like this...

=====================================
   private void btRunParamQuery_Click(object sender, EventArgs e)
   {
     string sql = "select * from app " +
                  "WHERE (((start >= :From) AND (start <= :To)) OR " +
                  "((finish >= :From) AND (finish <= :To)) OR " +
                  "((start < :From) AND (finish > :To)) OR " +
                  "(x_type = 1))";

     using (EDBDataAdapter adap = new EDBDataAdapter(sql, con))
     {
       EDBCommandBuilder cb = new EDBCommandBuilder();
       cb.DataAdapter = adap;

//        adap.SelectCommand.Parameters.Add(new EDBParameter("From", new
DateTime()));
//        adap.SelectCommand.Parameters.Add(new EDBParameter("To", new
DateTime()));

       adap.SelectCommand.Parameters["From"].Value = new DateTime(2010, 3,
1);
       adap.SelectCommand.Parameters["To"].Value = new DateTime(2010, 4,
1);

       DataTable tbl = new DataTable();
       adap.Fill(tbl);
       dataGridView1.DataSource = tbl;
     }
   }
=====================================

Unfortunately, this doesn't make any difference - the results are still the
same and the parameterized query still returns an incorrect result.

Look forward to your help!

Regards,

Hedley

Hi Hedley,

I think what Tim is saying is that you will need to change the Sql and
parameters to be something like this:

private void btRunParamQuery_Click(object sender, EventArgs e)
{
 string sql = "select * from app " +
 "WHERE (((start >= :StartFrom) AND (start <= :StartTo)) OR " +
 "((finish >= :FinishFrom) AND (finish <= :FinishTo)) OR " +
 "((start < :StartFrom2) AND (finish > :FinishTo2)) OR " +
 "(x_type = 1))";

 using (EDBDataAdapter adap = new EDBDataAdapter(sql, con))
 {
 EDBCommandBuilder cb = new EDBCommandBuilder();
 cb.DataAdapter = adap;

 adap.SelectCommand.Parameters["StartFrom"].Value = new DateTime(2010,
3, 1);
 adap.SelectCommand.Parameters["StartTo"].Value = new DateTime(2010, 4, 1);
 adap.SelectCommand.Parameters["FinishFrom"].Value = new DateTime(2010,
3, 1);
 adap.SelectCommand.Parameters["FinishTo"].Value = new DateTime(2010,
4, 1);
 adap.SelectCommand.Parameters["StartFrom2"].Value = new DateTime(2010,
3, 1);
 adap.SelectCommand.Parameters["FinishTo2"].Value = new DateTime(2010,
4, 1);

 DataTable tbl = new DataTable();
 adap.Fill(tbl);
 dataGridView1.DataSource = tbl;
 }

Chris Holland


On 10/02/2010 11:43, Hedley Muscroft wrote:
> Hi Tim,
>
> Thanks for looking into this, however unless I'm being stupid (always a
> possibility), this doesn't solve the problem.
>
>>> 1) You're adding new parameters when EDB already auto-creates parameters
>
> OK, so in the sample application, I can simply remove the
> "Parameters.Add" lines like this...
>
> =====================================
> private void btRunParamQuery_Click(object sender, EventArgs e)
> {
> string sql = "select * from app " +
> "WHERE (((start >= :From) AND (start <= :To)) OR " +
> "((finish >= :From) AND (finish <= :To)) OR " +
> "((start < :From) AND (finish > :To)) OR " +
> "(x_type = 1))";
>
> using (EDBDataAdapter adap = new EDBDataAdapter(sql, con))
> {
> EDBCommandBuilder cb = new EDBCommandBuilder();
> cb.DataAdapter = adap;
>
> // adap.SelectCommand.Parameters.Add(new EDBParameter("From", new
> DateTime()));
> // adap.SelectCommand.Parameters.Add(new EDBParameter("To", new
> DateTime()));
>
> adap.SelectCommand.Parameters["From"].Value = new DateTime(2010, 3, 1);
> adap.SelectCommand.Parameters["To"].Value = new DateTime(2010, 4, 1);
>
> DataTable tbl = new DataTable();
> adap.Fill(tbl);
> dataGridView1.DataSource = tbl;
> }
> }
> =====================================
>
> Unfortunately, this doesn't make any difference - the results are still
> the same and the parameterized query still returns an incorrect result.
>
> Look forward to your help!
>
> Regards,
>
> Hedley

--
Chris Holland
[Team Elevate]

Aha! That works - thanks for the clarification Chris.

Hi Tim,

Can I just clarify something : in my app I use both PGSQL (via DevArt's
provider) and EDB.

With PGSQL I can use the following query with 're-used' parameters :-

select * from app
WHERE (((start >= :From) AND (start <= :To)) OR
((finish >= :From) AND (finish <= :To)) OR
((start < :From) AND (finish > :To)) OR
(x_type = 1))

....and then in code I can just set two parameters :-

adap.SelectCommand.Parameters["From"].Value = new DateTime(2010, 3, 1);
adap.SelectCommand.Parameters["To"].Value = new DateTime(2010, 4, 1);

In EDB this doesn't work - I have to rename each parameter to give it a
unique name (e.g. From1, From2 etc.) and then set each value.

Which behaviour is correct (DevArt's PGSQL or the EDB provider)? Should I be
able to re-use parameters?

Many thanks!

Hedley,

<< Which behaviour is correct (DevArt's PGSQL or the EDB provider)? Should I
be able to re-use parameters? >>

Ideally, EDB should work like it does with Delphi and DevArt's provider,
however I haven't figured out a way to make it happen yet.  On the EDB
engine side of things, the engine only receives an SQL statement with ?
markers for parameters, so it has no knowledge of the marker names used on
the provider side of things (think C/S).  And, the problem is that any
reference to a particular parameter by name results in a single parameter
object reference being returned (property getter).  IOW, the base parameter
collection does not support referencing same-named parameter objects and
iterating over them.

--
Tim Young
Elevate Software
www.elevatesoft.com