TZSQLProcessor, parameters

[nostradumbass]

I need to have a replaceable parameter in a script that I need to run multiple times in a form.

In this case, I need to run the SQL with different file-names, every time -

Is this possible?

TZSQLProcessor component, Script property :

Code: Select all

 CREATE TABLE IMPORT_TABLE EXTERNAL :IMPORT_FILENAME (
    EMAIL_ADDRESS      CHAR(60) CHARACTER SET ANSI,
    FIRST_NAME         CHAR(50) CHARACTER SET ANSI,
    LAST_NAME          CHAR(50) CHARACTER SET ANSI,
    CRLF               CHAR(1) CHARACTER SET ANSI
);

in the code:

Code: Select all

with TZSQLProcessor1 do 
begin 
  ParamByName('IMPORT_FILENAME').Value = QuotedStr('c:\tmp.txt');
  Execute;
end;

the above does not work. What are my options other than using :
ReplaceStr( Script.Text, ':IMPORT_FILENAME', s_filename )

Thanks in advance,
ND

[mdaems]

What is the problem here?
Depending onthe database I believe the parameters are substituted (eg. mysql implementation) or really bound to a prepared statement when sending to the server. (eg. firebird)
I suppose your database is in the second situation. I'm afraid - but check your database docs - it's not possible to send this part of create statement as a variable of a prepared statement.
Do you get a real zeoslib error or is it a propagated database error you see?

Mark

[nostradumbass]

I get a runtime error because the parameter is not substituted in the SQL statement.

[nostradumbass]

So, to replace parameters in the statement before it is sent to the server is :

Code: Select all

  with  ZSQLProcessor1 do
  begin
    Script.Clear;
    Script.Add( replaceStr(sScriptText, ':IMPORT_FILENAME',  quotedStr(txtImportFile.Text)) );
   // sScriptText is set to the Script property in the OnCreate procedure of the form.
    Parse;
    Execute;
  end;