Problem parsing SQL Statement with backslash and parameter

Hi 

We are migration from Ado to Firedac (for MSSQL) and noticed that not all existing SQL starements work as before.

As an example such a statement won't work anymore (resulting in "Incorret syntax near ':'"): insert into table (field1, field2, field3) values ('test', '\\Server\Share\', :paField3Parameter).

Is there a fix or another way how to change the behaviour of the component without the need to change the SQL statements?

Thanks
0
Cornelius
7/30/2015 6:26:50 AM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

2 Replies
978 Views

Similar Articles

[PageSpeed] 48

FireDAC by default considers '\' as an escape character. Because you are migrating
from ADO and do not use FireDAC SQL preprocessor features, then set ResourceOptions
MacroCreate, MacroExpand, EscapeExpand to False. More about that:
http://docwiki.embarcadero.com/RADStudio/XE8/en/Preprocessing_Command_Text_%28FireDAC%29#Special_Character_Processing


-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
0
Dmitry
7/31/2015 5:38:38 AM
> {quote:title=Dmitry Arefiev wrote:}{quote}
> FireDAC by default considers '\' as an escape character. Because you are migrating
> from ADO and do not use FireDAC SQL preprocessor features, then set ResourceOptions
> MacroCreate, MacroExpand, EscapeExpand to False. More about that:
> http://docwiki.embarcadero.com/RADStudio/XE8/en/Preprocessing_Command_Text_%28FireDAC%29#Special_Character_Processing
> 
> 
> -- 
> With best regards,
> Dmitry Arefiev / FireDAC Architect

Hi Dmitry

Thank you for the hint. I have seen this optione before and try to ouse it - but it had no effect on the problem. See example code below - this still leads to the error.

procedure TTestForm.BtnQueryClick(Sender: TObject);
var lFDConnection: TFDConnection;
    lFDCommand: TFDCommand;
begin
  lFDConnection := TFDConnection.Create(nil);
  lFDConnection.LoginPrompt := False;
  lFDConnection.ResourceOptions.ParamCreate := True;
  lFDConnection.ResourceOptions.ParamExpand := True;
  lFDConnection.ResourceOptions.MacroCreate := False;
  lFDConnection.ResourceOptions.MacroExpand := False;
  lFDConnection.ResourceOptions.EscapeExpand := False;

  lFDCommand := TFDCommand.Create(nil);
  lFDCommand.Connection := lFDConnection;
  lFDCommand.ResourceOptions.MacroCreate := False;
  lFDCommand.ResourceOptions.MacroExpand := False;
  lFDCommand.ResourceOptions.EscapeExpand := False;

  lFDConnection.ConnectionString := edtDatabase.Text;
  lFDConnection.Params.Text := edtParams.Text;

  lFDConnection.Connected := True;
  lFDCommand.CommandText.Text := 'insert into COSETS (OIDCOMPANY, SITEM, SNAME) values (''1'', ''SWDepot\'', :paName)';
  try
    lFDCommand.Execute;
    ShowMessage('Success!');
  except
    on E:Exception do Showmessage('Ooops: ' + E.Message);
  end;
end;
0
Cornelius
8/4/2015 4:38:41 PM
Reply:

Similar Artilces:

Parsing SQL statements in Delphi with Oracle [Edit]
Hi, I have a requirement for a DBA to compile once and run many times sql sentences. I tried with BDE, ADO and DBExpress but in all cases the number of parsing is equals executions Example in delphi: procedure TForm1.Button1Click(Sender: TObject); var i : integer; begin Query1.Close; for I := 1 to 20 do begin Query1.SQL.Clear; Query1.SQL.Add(' SELECT COUNT(8) FROM CONTAB WHERE CONTAB_FEHOULMO__B = :fecha'); Query1.ParamByName('fecha').value := edit1.text ; Query1.Prepare; Query1.OPEN; end; end; the trace in oracle show...

Problem with Delphi XE2 using parameters in SQL query [Edit]
Hello, I have problem with using SQL with parameters. This part of code works fine on Delphi 2007, but have Debugger Exception Notification in Delphi XE2 with ADOTmp do *//works fine on Delphi2007 and Delphi XE2, date value is inserted* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = ''2012-04-03 13:13:13'' ');* ExecSQL; end; with ADOTmp do *//works fine on Delphi2007* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = :D1 ');* Parameters.ParamByName('D1').DataType := ftDateTime; Parame...

RE: SQL::Statement cannot parse valid Postgresql statement
> From: Terrence Brannon [mailto:metaperl@mac.com] > > On Monday, December 10, 2001, at 09:16 AM, Jeff Zucker wrote: > > > Terrence Brannon wrote: > >> > >> The following SQL is not parseable by SQL::Statement: > >> > >> INSERT > >> INTO thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date) > >> VALUES (?,?,?,CURRENT_TIMESTAMP) > > What I have is a program which reads a SQL statement from a file. > It parses it with SQL::Statement, then it prepares this > statement, then does an ...

Some parser to SQL and parameters for a proper FireDAC SQL Monitor
Hi, I see my questions are not answered. This is my last shot. As I can not compile the demos project about the "Moni" thing, I Will change my question. I am making a tiny SQL Monitor to avoid all problems that FDMonitor.exe produces. So, how is the best way to make a parser to get the SQL statement and the parameters and display all that information on a memo? I was watching carefully uADStanIntf.pas but I can not find any documentation for using it. Regards, with hope... Pablo Romero Cordoba, Argentina > I see my questions are not answered. ...

Problem with analytic sql function (The OVER SQL construct or statement is not supported)
Hi All!  Could You comment the next situation:I'm configuring my TableAdapter just like Scott Mitchell does in his tutorialhttp://www.asp.net/learn/data-access/tutorial-70-vb.aspxThe only principal difference is that I need Insert/update and deletemethods to be generated (His aim is only SELECT).I'm also using analytic function (ROW_NUMBER) and I'm also gettinwarning “The OVER SQL construct or statement is not supported.” Yousay then that it could be ignored. But, in this case statements tomodify data (insert/update and delete) aren't being generated, thoughafter w...

A problem about SQL Statement
Hello, I've got a problem about a Select statement with the following criteria: Data Table Key Num1 Num2 A 1 2 A 1 2 A 2 3 B 0 0 B 1 1 C 2 1 C 1 2 I would like to select all row of a key if the (sum of num1 - sum of num2) with that key != 0 In the above case, the outcome would be: A 1 2 A 1 2 A 2 3 Could any body advise what the SQL should be? Many thanks!   Hi,newbie Try Like This I have shown the select query in bold   CReate table Test_Sql ([Key] varchar(20),[Num1] int, [Num2] i...

SQL Statement problem
I am trying to pull certain information from a database by using the ISQL window, but Powerbuilder keeps locking up when I type the following code, could someone please show me what I'm doing wrong (assuming that all of the spelling is correct)? Thanks!!! SELECT t0.code, t0.billcode FROM billcode t0 WHERE t0.code in (SELECT distinct t1.code FROM app_charges_old t1 WHERE t1.invoice_number IN (SELECT t2.invoice_number FROM invoice_summary_old t2 WHER...

SQL statement problems
Having problems with our SELECT statements in both Webforms and client/server application that I wasn't having in the various demo versions. I am using "SNC" (SQL Native Client installed) connection. Connected to DB with transaction object called "SQLCA2". This code for debugging purposes works: select count(*) into :ll from users using SQLCA2; This actual code does NOT work: SELECT count (*) INTO :l_i_count FROM users WHERE user_id = :a_s_user_id using sqlca2; From debugger: sqlcode=0 sqldbcode=0 sqlerrtext="" Any ideas would b...

Problem with IF statement in SQL
 Can someone please tell me what I'm doing wrong here:  CREATE PROCEDURE dbo.AddProductToEnquiry @SessionID int, @ProductID int AS DECLARE @CountProductInEnquiry int SET @CountProductInEnquiry = (SELECT Count(*) FROM Enquiry_Items WHERE SessionID=@SessionID AND ProductID=@ProductID) IF @CountProductInEnquiry = 0 THEN INSERT INTO Enquiry_Items ([SessionID], [ProductID], [Date]) VALUES (@SessionID, @ProductID, GETDATE()) END IF   Cheers,Curt Regards, Curt  Actually I think Ive fixed it. Can someone please conf...

SQL Parameters Problem
I have a sqldataadapter, and its selectcommand is:   "select [PageID] from [tblPages] where [SearchKeywords] like '%@Keyword%'"Then I add a parameter to it:   myadap.parameters.add("@Keyword", sqldbtype.text)   myadap.parameters("@Keyword").value=myvarBut the problem is that this adapter doesn't properly but when I change it to:   "select [PageID] from [tblPages] where [SearchKeywords] like '%" & myvar & "%'"it will work properly.Is there anyone who could solve this problem?Regards you could add the parameter as in :"selec...

SQL Statement Problem
Hello all, Let's see if we have some pro's out here or not. This could be simple actually, but I don't know how... I have two tables, "Projects", and "Tasks." Now what I am trying to do is list the projects on a page. And I have managed to do that nicely. And, while it's looking, I actually have it open up another recordset inside of that one (which is looping) and have it lookup the tasks status percentage. So, when looking at one record, it looks like this: Projects - ID Title Status 1 Sam...

parsing SQL statement
Anyone having suggestion parsing SQL statement? It should able to parse: BLA BALA BALA... VALUES( 'abcd efg',,999, 'some \"STRING\" and \'STR2\' STR3',,,, 'abcd, def, fghi' ) I'm using tr and then split by "," but it will fall when seeing comma inside the single quote. Thanks. --budhi On Fri, 2008-05-30 at 19:24 +0700, beast wrote: > Anyone having suggestion parsing SQL statement? > > It should able to parse: > > BLA BALA BALA... > > VALUES( > 'abcd efg',,999, 'so...

SQL Statement problem
Can someone please tell me what is wrong with my SQL below..I think I am missing a quote somewhere... "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & " and highkv ='" & VoltageString & "' " StationNameString single quotes are not endedSushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb Thank you..that was the problem. Final statement = "select distinct itype from TR_data_and_history where stationname ='" & StationNameString & "' and highkv...

SQL::Statement problem
I posted a similar message to Jeff directly but I never got a confirmation that he actually received. So let's try here... Firstly, I've found out that RLIKE is broken: sub is_matched { ... if ($op =~ /LIKE|CLIKE/i) { $val2 = quotemeta($val2); $val2 =~ s/\\%/.*/g; $val2 =~ s/_/./g; } If $op i s'RLIKE', and since 'RLIKE' contains the string 'LIKE', the pattern is run through quotemeta(). Also, the SQL operators IS and the LIKE family are case-sensitive, which is contrary to SQL specs if I'm not mista...

Web resources about - Problem parsing SQL Statement with backslash and parameter - embarcadero.delphi.firedac

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Innovation statement: what you need to know about Malcolm Turnbull's plan
What you need to know about the newly released innovation statement.

The innovation nation: Elitist statement which won't generate change
Readers discuss the Turnbull government's innovation statement.

The statements murdered grandmother Helen Dawson Key's family will never get to read
If the family members of Helen Dawson Key had their chance, they would have faced her killer and told of their pain. 

Innovation statement: At a glance
Find out the main points from Malcolm Turnbull's innovation statement.

Dallas Stars: Cold facts: Loss to Blues 'clearly a statement' that Stars' upcoming divisional games will ...
Blues 3, Stars 0Click here for the full box score.Three Stars1. David Backes, Blues Goal, assist and fight give St. Louis captain the Gordie... ...

Comic-Con Organizers Issue Statement After Fan Confusion
... desire to expand their annual convention in San Diego. Comic-Con International made the unusual move today of putting out a statement regarding ...


The Latest: Islamic State group airs statement praising California mass shootings
The latest on the mass shooting in San Bernardino, California (all times local): 2:40 a.m.

Resources last updated: 12/14/2015 10:43:46 AM