Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 16 of 16 total |
Interesting problem |
Wed, Jul 16 2014 12:48 PM | Permanent Link |
Ben Sprei CustomEDP | It’s the quotes in the data field data that cause the problem. A Param use
will not help since you cant set the param "Adam Brett" wrote in message news:E6112874-8626-4E6E-B2DC-539C7887BC58@news.elevatesoft.com... Ben I usually use EDB's PARAMs in this situation as it cleanly copes with all issues such as single & double quotes which might confuse the compiler: EDBQuery.SQL.Text:= 'SELECT * FROM sometable WHERE somefield = :Str'; EDBQuery.Prepare; EDBQuery.ParamByName('Str').asString:= 'Someone''s '; The point is that if you do it like this the compiler will spot the single quote when you set the Param, so you can correct it. If it works as a string in code, EDB will happily run it. |
Wed, Jul 16 2014 12:50 PM | Permanent Link |
Ben Sprei CustomEDP | The "Engine.QuotedSQLStr" is producing a double quote. The data in the data
field has a single quote "Raul" wrote in message news:1B71002E-C8B9-46BA-BEE0-BBA0CD47F98D@news.elevatesoft.com... On 4/9/2014 10:55 PM, Ben wrote: > I did some further test utilizing this function and these were the > results: > > vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value); > > Value Input > A456 23' 24 > > Value Output > 'A456 23'' 24' > > This function is not working. Is there any other way around this. What do you mwan it's not working ? This is exactly what it's supposed to do. Here's an example as requested. - for this sample i assume the SKU input is from edit field named "Edit1" but of course it can come from any where (file, another table, etc). - q1 is a TEDBQuery - code sample code that queries the table for specific SKU and displays it : q1.SQL.Clear; q1.SQL.Add('SELECT * FROM Inv WHERE SKU=' + Engine.QuotedSQLStr(Edit1.Text)); q1.Open; if not q1.Eof then ShowMessage( Format('Sku=%s. Count=%d',[q1.FieldByName('SKU').AsString,q1.FieldByName('Num').AsInteger])); q1.Close; Raul |
Thu, Jul 17 2014 3:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
I'm getting baffled by what your problem actually is. Can you post a small demo project to the binaries so that we can see what's going on. Roy Lambert |
Sun, Jul 20 2014 4:48 PM | Permanent Link |
Ben Sprei CustomEDP | The question is very simple. I have a file named "InvMas". One field in
that file is "SKU". The value of that field is "A456 23' 24". The double quotes are NOT part of the field value. The single quote IS part of the field value. If I write a query (exactly as I am writing it) Select * from Invmas where SKU = 'A456 23' 24', it will return an error because of the single quote in the middle of the data string (field Value). If I use the following code in my program: vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value); Q1.SQL.Add(format(Select * from Invmas where SKU = ''%s''',[vSKU]; the query will not return any records because the value of vSKU is 'A456 23'' 24' (note the 2 single quotes in middle of the data). How do I get around this. Ben Sprei "Roy Lambert" wrote in message news:02A020CE-4947-4A9C-BE85-CFEC98F2D77F@news.elevatesoft.com... Ben I'm getting baffled by what your problem actually is. Can you post a small demo project to the binaries so that we can see what's going on. Roy Lambert |
Mon, Jul 21 2014 4:29 AM | Permanent Link |
Fernando Dias Team Elevate | Ben,
If the following doest work, then just like Roy, I can't understand what your problem actually is: Q1.SQL.Add(format('Select * from Invmas where SKU = %s',[vSKU])); -- Fernando Dias [Team Elevate] |
Mon, Jul 21 2014 4:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>The question is very simple. I have a file named "InvMas". One field in >that file is "SKU". The value of that field is "A456 23' 24". >The double quotes are NOT part of the field value. The single quote IS part >of the field value. >If I write a query (exactly as I am writing it) Select * from Invmas where >SKU = 'A456 23' 24', it will return an error because >of the single quote in the middle of the data string (field Value). >If I use the following code in my program: >vSKU := Engine.QuotedSQLStr(QInvMasSKU.Value); >Q1.SQL.Add(format(Select * from Invmas where SKU = ''%s''',[vSKU]; >the query will not return any records because the value of vSKU is 'A456 >23'' 24' (note the 2 single quotes in middle of the data). >How do I get around this. My first suggestion is to shoot whoever came up with that approach. However, I appreciate that that is not very helpful. What you have to do is double the quotes. QUOTEDSTR is not going to work because it will see one and a half quoted strings and bomb out. The best approach would be to develop your own function (as I've shown you on the other thread) to walk through the SKU and double any internal quotes. You would then alter your sql to something along the lines of SELECT * FROM table WHERE SKU = ExtraQuotes(:SKUValue) And simply supply the value of the SKU field as a parameter Roy Lambert |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |