Using ADO code to add a UDF to Pervasive database

Question

I have a CREATE FUNCTION statement in a text file. I need to add this function to a Pervasive database (v13 minimum) at runtime using ADO and the OLEDB provider. I could use ODBC if that is what it takes.

I have set up an ADOCommand object and an ADOQuery object. I drop the function if it exists like so:

cmd.CommandText := format('DROP FUNCTION IF EXISTS %s', [GetFunctionName(sl.Text)]);  // sl is a StringList containing the content of the file
cmd.Execute;

Then I try to execute the CREATE FUNCTION text like so:

qry.SQL.Text := sl.Text;  // sl is a StringList containing the content of the file 
qry.Prepared := true;
qry.Parameters.Clear;
qry.ExecSQL;
{  or this way
cmd.CommandText := sl.Text;  // sl is a StringList containing the content of the file
cmd.Parameters.Clear;
cmd.Execute;
}

I get this error using either a Command or Query to execute the text:

I think this is because variables in PSQL are identified by a leading ":" and ADO thinks they are parameter placeholders. Here is a snippet from my CREATE FUNCTION text as an example:

CREATE FUNCTION FormatDate(:sDate char(8))
--Version 2
--Comment Convert Sympac date string to DD/MM/YYYY
--Group Conversion
RETURNS char(10)
AS
BEGIN
DECLARE :dd char(2)
DECLARE :mm char(2)
DECLARE :yyyy char(4)

How can I run my CREATE FUNCTION text using ADO? Thanks in advance :)

Brian · Answer 1 · 2023-06-13T12:22:48.137

You were close. It is Delphi itself that in supporting named parameters in SQL using the :NAME syntax that does the conversion to ? syntax. You need to turn this off when it gets in the way.

Turn off ParamCheck.

cmd.Parameters.Clear;
cmd.ParamCheck := false;
cmd.CommandText := sl.Text;  
cmd.Execute;

The Delphi help for ParamCheck mentions this:

This property is useful for data definition language (DDL) statements that contain parameters as part of the DDL statement and that are not parameters for the ADO command component. For example, the DDL statement to create a stored procedure may contain parameter statements that are part of the stored procedure. Set ParamCheck to false to prevent these parameters from being mistaken for parameters of the ADO command component executing the DDL statement.

Using ADO code to add a UDF to Pervasive database

1 Answers1