TUniStoredProc and Sybase: How to deal with Result params

[derekwildstar]

Hello everybody!

I have the following Sybase stored procedure. I'm using UniDAC and the TUniStoredProc component, but there are no Result parameter and the TUniStoredProc "params" tab does not allow to create a new param.

Code: Select all

CREATE PROC dbo.p_modulo_por_menu (@pNomeMenu CHAR(30) IN, @pCodModulo CHAR(10) OUT, @pNomeModulo VARCHAR(50) OUT)
AS
  DECLARE @vQuantidade INT
  
  SET @pCodModulo = NULL
  SET @pNomeModulo = NULL
  
  SELECT @vQuantidade = COUNT(*)
    FROM MenuItens A
    JOIN Modulos B ON B.codg = A.codg_modul
   WHERE A.nome = @pNomeMenu
  
  IF @vQuantidade = 1
  BEGIN
    SELECT @pCodModulo = A.codg_modul
         , @pNomeModulo = B.nome
      FROM MenuItens A
      JOIN Modulos B ON B.codg = A.codg_modul
     WHERE A.nome = @pNomeMenu
     
    RETURN 1
  END
  ELSE
    RETURN 0
GO

As you can see, I'm using the return to return a code, how to catch this result with unidac components?

[AlexP]

Hello,

Please provide the following information:
- the exact version of UniDAC;
- the exact version of your IDE;
- the exact version of your Sybase Server.

[derekwildstar]

I'm sorry for the lack of informations... Answering your questions, respectively:

1. UniDac 4.1.5
2. BDS 2006 (Win32 personality)
3. Sybase Server = 12.5

The sybase client is 15.x all other funcionalities are working properly and before, with BDE the "Result" parameter was correctly detected.

Hello,

Please provide the following information:
- the exact version of UniDAC;
- the exact version of your IDE;
- the exact version of your Sybase Server.

[AlexP]

Hello,

When setting the property

Code: Select all

UniStoredProc1.StoredProcName := 'p_modulo_por_menu'; 

the call of this stored procedure is generated automatically

Code: Select all

{call :RETURN_VALUE = p_modulo_por_menu(:pNomeMenu, :pCodModulo, :pNomeModulo)}

and the necessary parameters are created

RETURN_VALUE, pNomeMenu, pCodModulo, pNomeModulo

where
RETURN_VALUE is an OUT parameter, in which the result returned from RESULT is recorded

You can get the result of your stored procedure execution after the execution with the help of

Code: Select all

UniStoredProc1.ParamByName('RETURN_VALUE').AsInteger

[derekwildstar]

Thank you for your help!

I will test this as soon I as arrive at work, but I need to say that this call, that way with a return parameter, is no being generated. I will confirm it soon!

PS.: The assignment of the name of the procedure can be made at design time or only at runtime like you did?

[AlexP]

Hello,

We have checked parameter generation during setting the procedure name both in design-time and run-time, and in both cases parameters are generated correctly, and with the help of

UniStoredProc1.ParamByName('RETURN_VALUE').AsInteger

we get the correct result in both cases

[derekwildstar]

I'm sorry AlexP, but this is not working. Take a look:

1. Choose the procedure



2. Reviewing the specific options



3. After press the "Create SQL" button



As you can see the generated SQL does not have the result parameter

4. On Runtime (debugger)



Take a look at Watch List at left

5. Continuing the execution the error is raised



PS.: if you cannot view the screenshots, please copy and paste the image addresses on the browser: http://repository.goldeye.info/imagens/Before1.jpg, http://repository.goldeye.info/imagens/Before2.jpg, http://repository.goldeye.info/imagens/After1.jpg, http://repository.goldeye.info/imagens/Execucao.jpg, http://repository.goldeye.info/imagens/Erro.jpg

[derekwildstar]

I think I found the possible problem, but i do not know what to do. Take a look at the following "inverted call stack":

Code: Select all

function TODBCCommand.CreateProcCall(const Name: _string; NeedDescribe: boolean; IsQuery: boolean): _string;
    procedure TODBCCommand.InitProcParams(const Name: _string);
        function TASEConnection.IsReturnValueAllowed: boolean;

The last function uses the value of FDriverType field wich is defined into function TASEConnection.GetConnectionString: _string;

Code: Select all

function TASEConnection.IsReturnValueAllowed: boolean;
begin
  Result := FDriverType  ad15;
end;

As you can see, this function returns False, because form me, FDriverType = ad15 (look at TASEConnection.GetConnectionString)

Now, what can I do?

[derekwildstar]

Well... Trying to solve the problem I've done:

Code: Select all

function TASEConnection.IsReturnValueAllowed: boolean;
begin
  Result := True;//FDriverType  ad15;
end;

After compile and reinstall the full UniDAC package, when I press the Generate SQL Button at designtime, the following is generated:

Code: Select all

{call :RETURN_VALUE = dbdcr01.dbo.p_modulo_por_menu;1(:pNomeMenu, :pCodModulo, :pNomeModulo)}

And the params tab now includes the "RETURN_VALUE" parameter. I tried to execute the procedure (runtime) but now the following error is raised:

Code: Select all

---------------------------
Debugger Exception Notification
---------------------------
Project DepCrime.exe raised exception class EODBCError with message '[Sybase][ODBC Driver]Output parameters will only be returned from this stored procedure when you use only parameter markers to pass parameter values.'.
---------------------------
Break   Continue   Help   
---------------------------

Now I'm stuck again and I found another strange thing: THE PARAMETERS AND THE GENERATED COMMAND ARE NOT SAVED ON DFM. I mean, if I close the project and open it again, the generated command and the parameters are gone. This can't be correct, right??!!

What can I do now?

[derekwildstar]

I decided to reverse the change what i had done. Now the sources are as original. By doing this the error on the output parameters is no longer displayed, however I do not have the return parameter.

The "problem" of not saving the DFM command generated at design time persists

[derekwildstar]

Finally I did it! The problem is caused by a number of apparently erroneous implementations:

• function TODBCCommand.CreateProcCall - The generated call on this function is {call :RETURN_VALUE = ProcedureName(:Param1, :Param2, :Param3)} but after some research i found the ODBC specification that says this should be {:RETURN_VALUE = call ProcedureName(:Param1, :Param2, :Param3)} So the return value part must be before the call keyword. I had to fix also the generation of the command string with parameter markers (?) To the same standard: {? = call ProcedureName(?, ?, ?)}
• function TASEConnection.IsReturnValueAllowed - The above function only creates a procedure call containing a result if there are a paramenter of type result (pdResult), but this param was never included because inside the procedure TODBCCommand.InitProcParams there is a condition using this function (IsReturnValueAllowed) which returns false, so, no return parameter is included!
• function TASEConnection.GetConnectionString - The above function only returns false because inside it there is the following statement:
  Result := FDriverType ad15;ad15, in turn, is declared like the followingtype TASEDriverType = (ad12, ad15);The field FDriverType (TASEConnection class), in turn, is defined inside this function (GetConnectionString) which, says clearly that if my driver is "Adaptive Server Enterprise" the FDriverType must be filled with ad15. The strange thing is, i guess, ad15 = ASE Driver 15, which is more up to date than ad12 (ASE Driver 12), so if my driver is more up to date, why the IsReturnValueAllowed function only returns true if FDriverType ad15. This makes no sense to me.

Well, I corrected all the three points above on the following way:

• For the first poit I've done the adjust on the params positions
• For the second point I did the IsReturnValueAllowed function always return true

I know this is the worst correction that I could done, but without know all the details this is the best that I can do for now.

Please, someone say what the correct correction!

[AlexP]

Hello,

Thank you for the information. Really, the correct methods call syntax is:

Code: Select all

{? = call ProcedureName(?, ?, ?)}

We have fixed the methods call, and this fix will be included in the next version.
We have also removed the IsReturnValueAllowed check for ASE (having any driver version, you will be able to obtain the RESULT parameter), however, due to limitations of the 15th driver version, when working with a ASE version 12.X, the driver doesn't support the OUT and IN/OUT parameters.

[derekwildstar]

however, due to limitations of the 15th driver version, when working with a ASE version 12.X, the driver doesn't support the OUT and IN/OUT parameters.

Hello AlexP

You mean the "Server ASE" or 'Client ASE". Our Server is being migrated from "ASE-12" to "ASE-15". I need to expect some problem?

[AlexP]

Hello,

Sybase Client 15 doesn't support OUT and IN/OUT parameters when connecting to Sybase servers 12.X, you can find more details at the developers' site http://www.sybase.com/detail?id=1066586#CHDGHHFI

[derekwildstar]

I've done all changes on the UniDAC source code. The generated code is correct and the result parameter is correctly generated. Now i have another little issue to report. I used the Migration Wizard to convert my old BDE project to UniDAC. All conversion was successfull. After that I've saw one TUniStoredProc and the params was all automatically filled. The command in the format {? = call procedure name (?,?)} was generated automatically also.

After close the project, close the Delphi and open everything again, all parameters and the generated command has gone. Although the lack of these things the program works correctly, but i cannot set the parameters on design time.

This behaviour is correct?