Zeoslib Portal

I am using Zeos v. 6.6.4 with Delphi 5 Enterprise to copy data from MSSql Server 2005 Enterprise to Firebird 1.5 .

Most of code works ok, but now I am stuck with the following problem:

In MSSQL I have the SP below (GetPK).

The SP takes the tables name and returns its primary key.

I call the SP from Delphi with the snippet below and get the error message 'row data is not available'.

Can somebody help ?

Should I get it that I cannot use TZQuery with selectable queries in MSSQL ?

Is there a workaround ?

Thank you

Peppe



Create function [dbo].[GetPK]
(
@Atable char(30)
)
returns @table table
(
result char(30)

)
as
begin
declare @vR char(30)



/* Seed the table with the initial value */
select @vR=c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @ATable
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
insert into @table (result) values (@Vr)


return
end


and I read it from Delphi with the following code:

function TZeosReplication.GetPK(atable:string):string;
begin
with TZQuery.Create(self) do
try
Connection := SourceDB;
sql.Text:=Format(GetText('select result from GETPK (%s)'),[InsideApex(atable)]); // function GetText returns the input string here
// InsideApex function is defined below
Log(sql.Text);
Open;
result:=trim(Fields[0].AsString); // exception SRowDataIsNotAvailable is raised here
finally
Free;
end;
end;

function InsideApex(Text:string):string;
const
apex='''';
begin
result:=Apex+Text+Apex;
end;

I solved the problem myself and will post the solution here as a help to all Zeos users:

Zeos does not handle selectable procedures like

select ... from ProcedureName(...)

for MSSQL.

Use TStoredProc and old fashioned non-selectable procedure

with TZStoredProc.Create(self) do
try
Connection := SourceDB;
StoredProcName := 'GETPK');
ParamByName('@ATABLE').AsString:= atable; // note the @ !
ExecProc;
result:=ParamByName('RESULT').AsString;
finally
Free;
end;

Hi All !

SAME HERE !

SELECT * FROM MyUDFName(1); causes EZSQLException 'row data is not available' in a TZQuery.

Can I use the above solution for UDFs that return resultsets ?

Thanks, Peter