[patch_done] Zeos 6.6.2-rc + Oracle 10g + Stored procedures

[willianjhonnes]

Hi guys...
I'm developping a cross plataform application with Zeos 6.6.2-rc in Lazarus / Kylix / Delphi using a Oracle XE database, but I can't do it work. I created some stored procedures in database, but when I try to execute any one, the message "Unsupported operation" is returned...
Using ADO or BDE in Delphi, I can do it, but not with Zeos in Lazarus or Delphi.
The problem is: my application must to run in Linux plataform and, to make it happen, I need Zeos!

There's a way to do it work?

Or there's some package, fix or a new version in development with this feature? I didn't find anything in the web about this...

Thank you...

[gto]

Hello willianjhonnes!

Please, post a demo program with the database creation SQL, if possible (or, at least, one of the procedures you're having problems).

Stored Procedures are very DB dependent, and also is the implementation in Zeos.

Thank you!

[willianjhonnes]

Hello, gto...

Here is a sample code of my app:


*********** BEGIN OF SAMPLE CODE ***********

procedure TFFID028.proExecProcedure;
begin
with spRelatorio do
begin
Close;
if eProtocolo.Text <> '' then
ParamByName('NRPROTOCOLO').AsString := eProtocolo.Text
else
ParamByName('NRPROTOCOLO').AsString := 'N';

if meCliente.Text <> ' . . - ' then
ParamByName('NRCPF').AsString := meCliente.Text + ' '
else
ParamByName('NRCPF').AsString := 'N';

if eMatricula.Text <> '' then
ParamByName('NRMATRICULA').AsString := eMatricula.Text
else
ParamByName('NRMATRICULA').AsString := 'N';

if eDataIni.Text <> '' then
ParamByName('DATAINI').AsString := eDataIni.Text
else
ParamByName('DATAINI').AsString := '01/01/08';

if eDataFim.Text <> '' then
ParamByName('DATAFIM').AsString := eDataFim.Text
else
ParamByName('DATAFIM').AsString := '31/12/25';

if eProxContatoIni.Text <> '' then
ParamByName('PROXCONTATOINI').AsString := eProxContatoIni.Text
else
ParamByName('PROXCONTATOINI').AsString := '01/01/08';

if eProxContatoFim.Text <> '' then
ParamByName('PROXCONTATOFIM').AsString := eProxContatoFim.Text
else
ParamByName('PROXCONTATOFIM').AsString := '31/12/25';

case cbxOrdenar.ItemIndex of
0: ParamByName('ORDENACAO').AsString := 'P';
1: ParamByName('ORDENACAO').AsString := 'C';
2: ParamByName('ORDENACAO').AsString := 'M';
3: ParamByName('ORDENACAO').AsString := 'D';
4: ParamByName('ORDENACAO').AsString := 'X';
end;

case cbxSentido.ItemIndex of
0: ParamByName('CLASSIFICACAO').AsString := 'C';
1: ParamByName('CLASSIFICACAO').AsString := 'D';
end;

ParamByName('VISUALIZACAO').AsString := 'T';
{
//Debug da Stored

ShowMessage('Parâmetros para a Stored Procedure:'#13 +
Params[0].Name + ': ' + Params[0].AsString + #13 +
Params[1].Name + ': ' + Params[1].AsString + #13 +
Params[2].Name + ': ' + Params[2].AsString + #13 +
Params[3].Name + ': ' + Params[3].AsString + #13 +
Params[4].Name + ': ' + Params[4].AsString + #13 +
Params[5].Name + ': ' + Params[5].AsString + #13 +
Params[6].Name + ': ' + Params[6].AsString + #13 +
Params[7].Name + ': ' + Params[7].AsString + #13 +
Params[8].Name + ': ' + Params[8].AsString + #13 +
Params[9].Name + ': ' + Params[9].AsString);

//Fim do debug
{}
Open;
btnRelatorio.Enabled := RecordCount > 0;
end;
end;

*********** END OF SAMPLE CODE ***********

And here is the SP in the database:

*********** BEGIN OF PROCEDURE ***********

CREATE OR REPLACE PROCEDURE SYSFARMA.SEL_ACOMPANHACLIENTE (
NrPROTOCOLO IN VARCHAR2,
NrCPF IN VARCHAR2,
NrMATRICULA IN VARCHAR2,
DATAINI IN VARCHAR2,
DATAFIM IN VARCHAR2,
PROXCONTATOINI IN VARCHAR2,
PROXCONTATOFIM IN VARCHAR2,
ORDENACAO IN CHAR,
CLASSIFICACAO IN CHAR,
VISUALIZACAO IN CHAR,
IOCURSOR OUT SYS_REFCURSOR
) AS
SQL_CONSULTA VARCHAR2(1000);

-- Consulta
BEGIN

SQL_CONSULTA := 'SELECT (''NI''||LPAD(AC.PROTOCOLO,5,0) || LPAD(AC.SEQUENCIA,2,0)||AC.ANO) AS PROTOCOLO, AC.DATA, ';

-- Forma de visualização (T corresponde a visualização na TELA, caso contrário forma de relatório padrão).
IF (VISUALIZACAO = 'T')THEN
SQL_CONSULTA := SQL_CONSULTA || 'AC.CLIENTE AS CPF,C.NOME AS "NOME DO CLIENTE", SUBSTR(AC.OCORRENCIA,0,30) AS "DESCRIÇÃO DA OCORRÊNCIA", AC.NUMCAD AS MATRICULA, AC.PROXCONTATO AS "PRÓXIMO CONTATO" ';
SQL_CONSULTA := SQL_CONSULTA || 'FROM ACOMPANHACLIENTE AC, CLIENTES C WHERE ';
ELSE
SQL_CONSULTA := SQL_CONSULTA || 'AC.CLIENTE AS CPF,C.NOME AS "NOME DO CLIENTE", AC.OCORRENCIA AS "DESCRIÇÃO DA OCORRÊNCIA", AC.CONTATO, AC.PROXCONTATO AS "PRÓXIMO CONTATO", ';
SQL_CONSULTA := SQL_CONSULTA || 'CA.EMAIL, C.TELEFONE1 AS TELEFONE, C.TELEFONE2 AS CELULAR, AC.NUMCAD AS MATRICULA, AC.NUMEMP AS FILIAL ';
SQL_CONSULTA := SQL_CONSULTA || 'FROM ACOMPANHACLIENTE AC, CLIENTES C, CLIENTEAUXILIAR CA WHERE ';
END IF;
-- Fim Forma de visualização

IF (NrPROTOCOLO <> 'N') THEN
SQL_CONSULTA := SQL_CONSULTA || 'AC.PROTOCOLO = '||NrPROTOCOLO||' AND ';
END IF;

IF (NrCPF <> 'N') THEN
SQL_CONSULTA := SQL_CONSULTA || 'AC.CLIENTE = '''||NrCPF||''' AND ';
END IF;

IF (NrMATRICULA <> 'N') THEN
SQL_CONSULTA := SQL_CONSULTA || 'AC.NUMCAD = '||NrMATRICULA||' AND ';
END IF;

SQL_CONSULTA := SQL_CONSULTA || 'TO_DATE(AC.DATA,''DD/MM/YY'') >= '''||DATAINI||''' AND ';
SQL_CONSULTA := SQL_CONSULTA || 'TO_DATE(AC.DATA,''DD/MM/YY'') <= '''||DATAFIM||''' AND ';

IF (PROXCONTATOINI = '01/01/08') THEN
SQL_CONSULTA := SQL_CONSULTA || '(TO_DATE(AC.PROXCONTATO,''DD/MM/YY'') >= '''||PROXCONTATOINI||''' OR AC.PROXCONTATO IS NULL) AND ';
ELSE
SQL_CONSULTA := SQL_CONSULTA || 'TO_DATE(AC.PROXCONTATO,''DD/MM/YY'') >= '''||PROXCONTATOINI||''' AND ';
SQL_CONSULTA := SQL_CONSULTA || 'TO_DATE(AC.PROXCONTATO,''DD/MM/YY'') <= '''||PROXCONTATOFIM||''' AND ';
END IF;


-- Forma de visualização (T corresponde a visualização na TELA, caso contrário forma de relatório padrão).
IF (VISUALIZACAO = 'T')THEN
SQL_CONSULTA := SQL_CONSULTA || 'C.CODIGO = AC.CLIENTE ';
ELSE
SQL_CONSULTA := SQL_CONSULTA || 'C.CODIGO = AC.CLIENTE AND CA.CODIGO = AC.CLIENTE ';
END IF;
-- Fim Forma de visualização

-- Fim Consulta

-- Ordenação
CASE WHEN ORDENACAO = 'P' THEN
BEGIN
IF (CLASSIFICACAO = 'D') THEN
SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY PROTOCOLO DESC, SEQUENCIA DESC, ANO ';
ELSE
SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY PROTOCOLO, SEQUENCIA, ANO ';
END IF;
END;
WHEN ORDENACAO = 'C' THEN SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY NOME ';
WHEN ORDENACAO = 'M' THEN SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY NUMCAD ';
WHEN ORDENACAO = 'D' THEN SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY DATA ';
WHEN ORDENACAO = 'X' THEN SQL_CONSULTA := SQL_CONSULTA || 'ORDER BY PROXCONTATO ';
END CASE;

IF (CLASSIFICACAO = 'D') THEN
SQL_CONSULTA := SQL_CONSULTA || 'DESC';
END IF;
-- Fim Ordenação

-- Resultado
OPEN IOCURSOR FOR SQL_CONSULTA;
-- Fim Resultado

END SEL_ACOMPANHACLIENTE;

*********** END OF PROCEDURE ***********

[mdaems]

You problem is that the storedproc component isn't implemented for Oracle... We're looking for somebody who wans to implement it. Feel free to volunteer.

The more : 'IOCURSOR OUT SYS_REFCURSOR ' isn't supported for the other databases either. This trick is a little bit to database dependent, I think.

I propose an other solution : Return the SQL statement from a function and feed it to a normal ZQuery component. Lots easier, I think.
Just change 'create procedure ...(...) As' to 'create function ...(...) return varchar2' en change the last lines to

Code: Select all

-- Resultado
return SQL_CONSULTA;
-- Fim Resultado 

Mark

[willianjhonnes]

Hi Mark, thank you for the invitation... But, let me learn some more about the Zeoslib before... (hehehe)

Well, sorry about my ignorance about it (and my poor english too), but I didn't understand how I can make a stored function be called in a simple ZQuery object, because in PL/SQL we must use assign the return method to a variable, so I couldn'd make it work in the application with a ZQuery object.

Thak you once again

Willian

[mdaems]

Oracle stored functions (not procedures) returning simple scalar values (number, varchar2, ... NO boolean, refpointer, ...) can be used in a select statement. This select statement can be opened using a ZQuery. 'select functionname(params) from dual;' executes your function exactly once. In your case you can take the value of fields[0].asString and put it in the SQL property of a ZQuery. Then open this second query (eventually you could reuse the first query component).

Mark

[willianjhonnes]

Mark, thank you...

I'll try your issue, but, wouldn't it affect the response performance?
I'm affraid because I have almost 2,000,000 customers in my base...

Once again...

[mdaems]

Yes, it would. It's two queries instead of one. Other solutions are not supported by zeoslib, however. No way we can get results from a cursor reference without changing zeoslib sources. And to do that we need somebody with an Oracle database to test against.

About pure performance : compose your query using Pascal logic... There's no data dependent logic in composing the SQL statement. So there's no use in doing it on the database (I know : unless you're not the only one using the same logic). Syntax isn't much different. It could even be more performant than the original procedure as no dynamic SQL is involved.

BTW : the number of customers isn't important. (Unless these customers are using this function and the number of customers corresponds to the number of connections) Both approaches would execute exactly the same query on the customers table.

Mark (oracle developer in professional life, but only using Oracle Forms, Reports and PL/SQL)

[willianjhonnes]

Hi, Mark... It's me again.

I'd tried your issue and it works! Thank you. But I had the same problem trying to access my stored procedures in MySQL and PostgreSQL too!!! In design time, trying to get the procedures names in the object inspector, the ZStoredProc returns the "Unsupported operation" message.

I'll try your issue with this databases, but, there's a way do make it work with the ZStoredProc object?

Thank you once again...

[amarildolacerda]

I Finished first code to Oracle 10g StoredProc and Functions.
I dont known where I can upload...

- on Delphi IDE, can select StoreProcName and Params (on Inspector).

[EgonHugeist]

amarildolacerda,

Is that still actual? Can you attach a patch-file eventually?

best regards

[amarildolacerda]

Thanks for you attent... Actual the code is the same of:

link: http://zeos.firmos.at/viewtopic.php?t=3 ... dre+output

I restart my work in this code last week. I have problem to get output params from oracle procedure. If you have some sugestion I will apreciate.

[amarildolacerda]

I Have a procedure like:

procedure my_proc( v out number);

When a execute with TZStoredProc I have an exception.

I Change this code in ZDbcOracleUtils line 414 (in red);



procedure LoadOracleVars(PlainDriver: IZOraclePlainDriver;
Connection: IZConnection; ErrorHandle: POCIError; Variables: PZSQLVars;
Values: TZVariantDynArray);
var
I: Integer;
Status: Integer;
CurrentVar: PZSQLVar;
TempDate: TDateTime;
TempBlob: IZBlob;
WriteTempBlob: IZOracleBlob;
TempStream: TStream;
Year, Month, Day, Hour, Min, Sec, MSec: Word;
OracleConnection: IZOracleConnection;
begin
OracleConnection := Connection as IZOracleConnection;
for I := 0 to Variables.ActualNum - 1 do
begin
CurrentVar := @Variables.Variables[I + 1];
CurrentVar.DupData := CurrentVar.Data;
if (high(Values)<I) or DefVarManager.IsNull(Values) then




*********************************
After when call that procedure... work fine;

the same with : procedure my_proc2( p in number, ret out number);

Work fine too... with package procedure...
Work fine with functions too..

Thanks.

[EgonHugeist]

Patch done in 7 alpa testing(1138) and testing-egonhugeist(1139) branch.

Thank you..