Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC against SQLite with Single Quote and Backslash in Insert Query



Permlink Replies: 6 - Last Post: Jan 5, 2016 5:21 PM Last Post By: Wanli Ma
Wanli Ma

Posts: 21
Registered: 12/6/05
FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 2, 2016 12:35 PM
I am evaluating FireDAC (XE10 Pro) for SQLite. With TFDQuery, there seems an issue if values contain both backslash and single quote. Following sample query demonstrates the problem.

insert into Table1 (Field1) values ('\fs22\''c4\''fa\''ba\''c3\lan')

Field1 is TEXT type. The single quote is escaped by doubling it per SQL standard.

The expected inserted result: -- It is the standard behavior of SQL 92. I have also tested with SQLite Explorer. -- So it can be achieved.

\fs22\'c4\'fa\'ba\'c3\lan

The actual inserted result:

\fs22\'c4'fa\'ba'c3\lan

Notice that some backslashes are missing. I know a few threads advise using parameters. But please help to get this simple query working. Standard query should still be an option, right? :)

I suspect it is a FireDAC escaping issue. It might be doing some internal work via c code, thus backslash now becomes an escape character in some cases. As confirmation, before INSERT, instead of doubling the single quote, I replaced it with something like ~~ and then replace it back at SELECT. Then everything is fine.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2016 2:50 AM   in response to: Wanli Ma in response to: Wanli Ma
Set ResourceOptions.MacroCreate, MacroExpand, EscapeExpand to False.

--
With best regards,
Dmitry
Wanli Ma

Posts: 21
Registered: 12/6/05
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2016 8:13 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Hi, Dmitry:

Great Thanks! It worked like a charm. It reminds me of good old days when TeamB Gurus always got the magic. It has been since long I moved to C#/Java camps. This time upgraded to XE10 just to keep Delphi knowledge alive for another cycle.

One more question on the side, for TFDConnection is there a way one can provide a physical path or a different name for sqlite dll? So as to be sure a specific version of dll is loaded. I cannot find a property like this. The Pro version does not seem to come with FireDAC source code, I cannot check how it does internally for LoadLibrary.

Thanks again!

Wanli


Dmitry Arefiev wrote:
Set ResourceOptions.MacroCreate, MacroExpand, EscapeExpand to False.

--
With best regards,
Dmitry
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 3, 2016 11:22 PM   in response to: Wanli Ma in response to: Wanli Ma
The Pro version does not seem to come with FireDAC source code, I cannot check how it does internally for LoadLibrary.

On Windows (I expect it is your platform) FireDAC SQLite driver
uses static linking of SQLite engine. To switch to dynamic linking
you must have the FireDAC sources. Read about linking mode at:
http://docwiki.embarcadero.com/RADStudio/Seattle/en/Connect_to_SQLite_database_%28FireDAC%29#Client_Software

--
With best regards,
Dmitry
Wanli Ma

Posts: 21
Registered: 12/6/05
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2016 6:25 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Thanks for the clarification (Yes, as of now I use Delphi for Windows only). Static linking is better than DLL hell but there might be some issue if SQLite releases a critical patch.

Back to the original question. If Embarcadero Dev would be collecting feedback from forum, your 3 settings should be the correct defaults for TFDQuery. Or else it might unknowingly cause headaches.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 4, 2016 10:28 PM   in response to: Wanli Ma in response to: Wanli Ma
If Embarcadero Dev would be collecting feedback from forum, your 3 settings should be the correct defaults for TFDQuery. Or else it might unknowingly cause headaches.

I do. But the problem is backward compatibility ...

--
With best regards,
Dmitry
Wanli Ma

Posts: 21
Registered: 12/6/05
Re: FireDAC against SQLite with Single Quote and Backslash in Insert Query
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 5, 2016 4:50 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Embacardero takes big credit for reviving Delphi and for fulfilling many old Kylix dreams Borland had. But I don't think they have good record on backward compatibility. i.e. TDataset, they changed signature for a few functions, there is Pointer, TRecordBuffer, TRecBuf ...

Anyway, hope more people will read your answer thus avoid releasing a program sometimes unknowingly missing data when inserting records. Following Anders to C#, then swinging back and forth between C# and Java, I know many people expect the default behavior will be the standard behavior, then try additional properties for extension.

Thanks again!

Wanli
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02