How to use TAdoQuery to ALTER IB stored procedure

Question

We test migration of a D5 project (using IB 2009 Server and IBProvider) to XE6, keeping the same IB server an ADO provider. The project heavily uses stored procedures (SPs).

At database creation (programmatically, with IB API), after having created (with TAdoQuery) the tables, exceptions and declaring (also with TAdoQuery) several UDFs, we do the SPs creation (still with TAdoQuery) in two passes.

pass 1. Using SQL command CREATE PROCEDURE, we create all SPs with trivial body, e.g.

PROCEDURE "SP_WITH_PARAMETERS"("IN_PARAM" VARCHAR(5))
RETURNS ("OUT_PARAM" VARCHAR(5))
AS
BEGIN
  EXIT;   <-- trivial body
END;

pass 2. Using ALTER PROCEDURE, each SP is endowed with its proper body, e.g.

PROCEDURE "SP_WITH_PARAMETERS"("IN_PARAM" VARCHAR(5))
RETURNS ("OUT_PARAM" VARCHAR(5))
AS
BEGIN
  SELECT MAX(C_CODE)
  FROM CODES
  WHERE C_CODE < :IN_PARAM
  INTO :OUT_PARAM;
END;

For anybody who would like to do a test: The metadata of table CODES referred to in this particular body, is

CREATE TABLE "CODES"
("C_CODE" VARCHAR(5) NOT NULL,
PRIMARY KEY ("C_CODE"));

The two passes are to overcome the obstacle of SPs inter dependencies.

In D5 it was necessary to respect an ugly "convention" consisting in using in TAdoQuery.SQL, double-colon (instead of colon) to prefix SP parameters referred to within SQL commands occurring within SP body. Imagine the above SP - the whole body of which is a single SQL command - with its colons doubled. That was in D5 where SPs creation worked "as expected", no matter the details of the "ugly" convention.

Now we are in XE6 and we rapidly noticed that in that particular case - let us call it "SP with parameters" (SPWP) - our D5 approach fails because IB doesn't tolerate double-colon which was (probably) preprocessed in D5. Unfortunately, with simple colons in TAdoQuery.Sql we don't reach the goal. We get

EOleException 
ErrorCode=-2146824580 ($800A0E7C) 
Msg=<"Parameter object is improperly defined. Inconsistent or incomplete information was provided"

which suggests that now it's ADO's turn to be unsatisfied.

Question: How, using TAdoQuery and SQL command ALTER PROCEDURE, one can alter the first of the above SPs into the second one?

Answer 1

When assigning SQL to an TAdoQuery in code, make sure you have set ParamCheck := False before assigning the SQL statements. Otherwise, the Params list will still be populated.