Search code examples
ms-accessc++builderguidfiredac

FireDAC ExecSQL with Params, C++ (or escape GUID braces)

I'm using C++Builder (XE7) and FireDAC, trying to insert a row into a table with a column of GUID datatype into a Microsoft Access database with FDConnection.ExecSQL.

SQL which works in any tool which allows me to execute SQL directly to the Database:

INSERT INTO SomeTable (Column1, Column2) 
VALUES ('xyz', {guid {7F60EFE4-106D-4678-A838-8BEB541F2A74}})

(the above GUID syntax is apparently what Microsoft wants for this odd Database column type)

So, trying to execute this SQL in my program...

My first attempt:

FDConnection1->ExecSQL("INSERT INTO SomeTable (Column1, Column2) VALUES ('xyz', {guid {7F60EFE4-106D-4678-A838-8BEB541F2A74}})");

Fails because apparently curly braces '{', '}' are used in FireDAC preprocessing (which I never knew about, but looks pretty useful). FireDAC docs here say to escape special characters like '{' by doubling them up.

Docs: http://docwiki.embarcadero.com/RADStudio/XE7/en/Preprocessing_Command_Text_%28FireDAC%29#Special_Character_Processing

So, my next attempt:

FDConnection1->ExecSQL("INSERT INTO SomeTable (Column1, Column2) VALUES ('xyz', {{guid {{7F60EFE4-106D-4678-A838-8BEB541F2A74}}}})");

Still doesn't work. Fails with a preprocessor error for macro.

Finally, it looks like the ExecSQL method is overloaded with a signature that allows passing params, so I tried this:

FDConnection1->ExecSQL("INSERT INTO SomeTable (Column1, Column2) VALUES ('xyz', :guid)", ["{guid {7F60EFE4-106D-4678-A838-8BEB541F2A74}}"]);

But this simple [ param, param, ... ] syntax given in the examples is Delphi, not C++. There is no C++ example.

Doing some research, I found that there is a macro which might help with these types of method signatures in C++. Its syntax is: ARRAYOFCONST(( param, param, ... ))

So, my next attempt is:

FDConnection1->ExecSQL("INSERT INTO SomeTable (Column1, Column2) VALUES ('xyz', :guid)", ARRAYOFCONST(( "{guid {7F60EFE4-106D-4678-A838-8BEB541F2A74}}" )));

Which fails with:

[bcc32 Error] ReportHeaderFrm.cpp(487): E2285 Could not find a match for 'TFDCustomConnection::ExecSQL(UnicodeString,OpenArray,unsigned int)'

I'm out of ideas. Any help?

Solution

  • To call that kind of functions, you need the macro OPENARRAY, not ARRAYOFCONST. So, changing your last attempt for this should pass the compiler error:

    FDConnection1->ExecSQL("INSERT INTO SomeTable (Column1, Column2) VALUES (:param1, :param2)", OPENARRAY(Variant, ("xyz", "{guid {7F60EFE4-106D-4678-A838-8BEB541F2A74}}")));