Board index » delphi » DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures

DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures

Configuration :

WinNT4, Service Pack 4, Option Pack 4, SQL7 client utility, BDE5.02 (the
BDE5.01 with the

gamma drivers)

Problem Stored Procedures

Since we applied the SQL7 patch, we can see , in design mode, the stored
procedures that are available in the SQL Server 7 Database.

The Tstoredproc in design mode seems to find the parameters  that are
required for the

stored procedure. The only thing we have to do is indicate if the parameters
are input,

output or result.

The problem comes when we try to call them after having set the parameters
(with

TstoredProc.ExecProc) .
The code we use looks like:

        With StoredProc1 Do Begin
          ParamByName('@THISTRANSACCPID').AsInteger:=976;
          ParamByName('@CODEREPSA').AsString := 'T';
          ParamByName('@THISDATE').AsString :='981212';
          ParamByName('@THISTIME').AsString := '103000';
          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
          Prepare;
          ExecProc;
          GetResults;
          ShowMessage(paramByName('Result').asString);
          UnPrepare;
        End;

On The ExecProc statement, we have an exception saying: General SQL Error,
Could not find

Stored procedure dbo.SPOT8COF;1.

Workaround:

We have found that we can call a stored procedure with the following
(boring) method:

        With StoredProc1 Do Begin
          StoredProcName:='SPOT8COF';
  // Or StoredProcName:='dbo.SPOT8COF'; or StoredProcName:='dbo.SPOT8COF;1';
          Params.Clear;
          Params.CreateParam(ftInteger,'@THISTRANSACCPID',ptInput);
          Params.CreateParam(ftString,'@CODEREPSA',ptInput);
          Params.CreateParam(ftString,'@THISDATE',ptInput);
          Params.CreateParam(ftString,'@THISTIME',ptInput);
          Params.CreateParam(ftString,'@THISNUMAUTORISATION',ptInput);
          Params.CreateParam(ftInteger,'Result',ptResult);
          ParamByName('@THISTRANSACCPID').AsInteger:=976;
          ParamByName('@CODEREPSA').AsString := 'T';
          ParamByName('@THISDATE').AsString :='981212';
          ParamByName('@THISTIME').AsString := '103000';
          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
          Prepare;
          ExecProc;
          GetResults;
          ShowMessage(paramByName('Result').asString);
          ShowMessage(SpotCof.ParamByName('Result').AsString);
          UnPrepare;
        End;

You can understand that this is very boring if you have to update manually
all your
applications.

 

Re:DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures


Your must apply Delphi Patch 2, I Don't need to change a single code from
Delphi 3 to 4 and MsSQL 6.5 to &7

Quote
>WinNT4, Service Pack 4, Option Pack 4, SQL7 client utility, BDE5.02 (the
>BDE5.01 with the

>gamma drivers)

>Problem Stored Procedures

>Since we applied the SQL7 patch, we can see , in design mode, the stored
>procedures that are available in the SQL Server 7 Database.

>The Tstoredproc in design mode seems to find the parameters  that are
>required for the

>stored procedure. The only thing we have to do is indicate if the
parameters
>are input,

>output or result.

>The problem comes when we try to call them after having set the parameters
>(with

>TstoredProc.ExecProc) .
>The code we use looks like:

>        With StoredProc1 Do Begin
>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>          ParamByName('@CODEREPSA').AsString := 'T';
>          ParamByName('@THISDATE').AsString :='981212';
>          ParamByName('@THISTIME').AsString := '103000';
>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>          Prepare;
>          ExecProc;
>          GetResults;
>          ShowMessage(paramByName('Result').asString);
>          UnPrepare;
>        End;

>On The ExecProc statement, we have an exception saying: General SQL Error,
>Could not find

>Stored procedure dbo.SPOT8COF;1.

>Workaround:

>We have found that we can call a stored procedure with the following
>(boring) method:

>        With StoredProc1 Do Begin
>          StoredProcName:='SPOT8COF';
>  // Or StoredProcName:='dbo.SPOT8COF'; or

StoredProcName:='dbo.SPOT8COF;1';

- Show quoted text -

Quote
>          Params.Clear;
>          Params.CreateParam(ftInteger,'@THISTRANSACCPID',ptInput);
>          Params.CreateParam(ftString,'@CODEREPSA',ptInput);
>          Params.CreateParam(ftString,'@THISDATE',ptInput);
>          Params.CreateParam(ftString,'@THISTIME',ptInput);
>          Params.CreateParam(ftString,'@THISNUMAUTORISATION',ptInput);
>          Params.CreateParam(ftInteger,'Result',ptResult);
>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>          ParamByName('@CODEREPSA').AsString := 'T';
>          ParamByName('@THISDATE').AsString :='981212';
>          ParamByName('@THISTIME').AsString := '103000';
>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>          Prepare;
>          ExecProc;
>          GetResults;
>          ShowMessage(paramByName('Result').asString);
>          ShowMessage(SpotCof.ParamByName('Result').AsString);
>          UnPrepare;
>        End;

>You can understand that this is very boring if you have to update manually
>all your
>applications.

Re:DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures


You may want to download the BDE SQL Links field test from our web site and try
it.  You can find it off the Delphi and BDE Support pages.

John Kaster, Enterprise Product Manager
Please post MIDAS-related messages in inprise.public.midas

Re:DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures


Thank you for your answer.
Actually, this is what we have done: we have the Delphi Patch 2 for delphi4
(downloaded from their Web site).
I may have missed something. Can you confirm that you can call stored
procedures from your application with an MSSQL alias to the SQL7 Server. We
found that with an ODBC alias, the problem doesn't occur for stored proc
(but we have some troubles with Cached updates and other few features..)

Thank you in advance.

Quote
AhPaul wrote in message <75uopn$f...@forums.borland.com>...
>Your must apply Delphi Patch 2, I Don't need to change a single code from
>Delphi 3 to 4 and MsSQL 6.5 to &7

>>WinNT4, Service Pack 4, Option Pack 4, SQL7 client utility, BDE5.02 (the
>>BDE5.01 with the

>>gamma drivers)

>>Problem Stored Procedures

>>Since we applied the SQL7 patch, we can see , in design mode, the stored
>>procedures that are available in the SQL Server 7 Database.

>>The Tstoredproc in design mode seems to find the parameters  that are
>>required for the

>>stored procedure. The only thing we have to do is indicate if the
>parameters
>>are input,

>>output or result.

>>The problem comes when we try to call them after having set the parameters
>>(with

>>TstoredProc.ExecProc) .
>>The code we use looks like:

>>        With StoredProc1 Do Begin
>>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>>          ParamByName('@CODEREPSA').AsString := 'T';
>>          ParamByName('@THISDATE').AsString :='981212';
>>          ParamByName('@THISTIME').AsString := '103000';
>>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>>          Prepare;
>>          ExecProc;
>>          GetResults;
>>          ShowMessage(paramByName('Result').asString);
>>          UnPrepare;
>>        End;

>>On The ExecProc statement, we have an exception saying: General SQL Error,
>>Could not find

>>Stored procedure dbo.SPOT8COF;1.

>>Workaround:

>>We have found that we can call a stored procedure with the following
>>(boring) method:

>>        With StoredProc1 Do Begin
>>          StoredProcName:='SPOT8COF';
>>  // Or StoredProcName:='dbo.SPOT8COF'; or
>StoredProcName:='dbo.SPOT8COF;1';
>>          Params.Clear;
>>          Params.CreateParam(ftInteger,'@THISTRANSACCPID',ptInput);
>>          Params.CreateParam(ftString,'@CODEREPSA',ptInput);
>>          Params.CreateParam(ftString,'@THISDATE',ptInput);
>>          Params.CreateParam(ftString,'@THISTIME',ptInput);
>>          Params.CreateParam(ftString,'@THISNUMAUTORISATION',ptInput);
>>          Params.CreateParam(ftInteger,'Result',ptResult);
>>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>>          ParamByName('@CODEREPSA').AsString := 'T';
>>          ParamByName('@THISDATE').AsString :='981212';
>>          ParamByName('@THISTIME').AsString := '103000';
>>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>>          Prepare;
>>          ExecProc;
>>          GetResults;
>>          ShowMessage(paramByName('Result').asString);
>>          ShowMessage(SpotCof.ParamByName('Result').AsString);
>>          UnPrepare;
>>        End;

>>You can understand that this is very boring if you have to update manually
>>all your
>>applications.

Re:DELPHI 4 C/S, MS SQL 7.0 and Stored Procedures


I think will remove enough  ";1"

Quote
Defrance frdric wrote in message <75tp9s$ek...@forums.borland.com>...
>Configuration :

>WinNT4, Service Pack 4, Option Pack 4, SQL7 client utility, BDE5.02 (the
>BDE5.01 with the

>gamma drivers)

>Problem Stored Procedures

>Since we applied the SQL7 patch, we can see , in design mode, the stored
>procedures that are available in the SQL Server 7 Database.

>The Tstoredproc in design mode seems to find the parameters  that are
>required for the

>stored procedure. The only thing we have to do is indicate if the
parameters
>are input,

>output or result.

>The problem comes when we try to call them after having set the parameters
>(with

>TstoredProc.ExecProc) .
>The code we use looks like:

>        With StoredProc1 Do Begin
>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>          ParamByName('@CODEREPSA').AsString := 'T';
>          ParamByName('@THISDATE').AsString :='981212';
>          ParamByName('@THISTIME').AsString := '103000';
>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>          Prepare;
>          ExecProc;
>          GetResults;
>          ShowMessage(paramByName('Result').asString);
>          UnPrepare;
>        End;

>On The ExecProc statement, we have an exception saying: General SQL Error,
>Could not find

>Stored procedure dbo.SPOT8COF;1.

>Workaround:

>We have found that we can call a stored procedure with the following
>(boring) method:

>        With StoredProc1 Do Begin
>          StoredProcName:='SPOT8COF';
>  // Or StoredProcName:='dbo.SPOT8COF'; or

StoredProcName:='dbo.SPOT8COF;1';

- Show quoted text -

Quote
>          Params.Clear;
>          Params.CreateParam(ftInteger,'@THISTRANSACCPID',ptInput);
>          Params.CreateParam(ftString,'@CODEREPSA',ptInput);
>          Params.CreateParam(ftString,'@THISDATE',ptInput);
>          Params.CreateParam(ftString,'@THISTIME',ptInput);
>          Params.CreateParam(ftString,'@THISNUMAUTORISATION',ptInput);
>          Params.CreateParam(ftInteger,'Result',ptResult);
>          ParamByName('@THISTRANSACCPID').AsInteger:=976;
>          ParamByName('@CODEREPSA').AsString := 'T';
>          ParamByName('@THISDATE').AsString :='981212';
>          ParamByName('@THISTIME').AsString := '103000';
>          ParamByName('@THISNUMAUTORISATION').AsString :='HELLO';
>          Prepare;
>          ExecProc;
>          GetResults;
>          ShowMessage(paramByName('Result').asString);
>          ShowMessage(SpotCof.ParamByName('Result').AsString);
>          UnPrepare;
>        End;

>You can understand that this is very boring if you have to update manually
>all your
>applications.

Other Threads