Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
Bug in ADO.NET Provider with parameterized queries |
Fri, Feb 5 2010 9:25 AM | Permanent Link |
"Hedley Muscroft" | 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 |
Mon, Feb 8 2010 10:54 AM | Permanent Link |
"Hedley Muscroft" | 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! |
Tue, Feb 9 2010 11:31 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 9 2010 6:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Tue, Feb 9 2010 7:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Feb 10 2010 6:43 AM | Permanent Link |
"Hedley Muscroft" | 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 |
Wed, Feb 10 2010 3:22 PM | Permanent Link |
Chris Holland SEC Solutions Ltd. Team Elevate | 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] |
Thu, Feb 11 2010 5:31 AM | Permanent Link |
"Hedley Muscroft" | Aha! That works - thanks for the clarification Chris.
|
Thu, Feb 11 2010 8:43 AM | Permanent Link |
"Hedley Muscroft" | 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! |
Thu, Feb 11 2010 1:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |