TSQLStoredProc

[rom]

Hello!

Code: Select all

StoredProc:=TSQLStoredProc.Create(nil); 
with StoredProc do try 
  //I take the connection from an other window (This connection is right : it works with TSQLQuery) 
  SQLConnection:=DlgPrint.SQLConnection; 
  StoredProcName:='CALC' 
  Params.Clear; 
  Params.CreateParam(ftString, 'p_CDGRP', ptInput); 
  ParamByName('p_CDGRP').AsString:=cdgrp; 
  ExecProc; 
finally 
  StoredProc.Free; 
end;

It doesn't work!
There's an error "procedure CALC wait for the argument CDGRP varchar(10)"
For all my stored proc, there's some problem.

Is something wrong with TSQLStoredProc? DbExpress? My code?

Thanks

Rom

[Ikar]

Please send us a script to create this StoredProc

[rom]

Here is the very simple script I use to test my code :

Code: Select all

CREATE PROCEDURE CALC(@p_CDGRP varchar(10)) as
BEGIN
  Update societe set CDSOC='001'
  where CDGRP=@p_CDGRP

END
go

thanks for your help

rom

[Ikar]

To solve the problem use the next code:

StoredProc:=TSQLStoredProc.Create(nil);
with StoredProc do try
//I take the connection from an other window (This connection is right : it works with TSQLQuery)
SQLConnection:=SQLConnectionTest;
SQLConnection.Open;
StoredProcName:='CALC';
ParamByName('p_CDGRP').AsString:='111';
ExecProc;
finally
StoredProc.Free;
end;

[rom]

It works!!!!!

Thank you very much!

However, I have an other problem. When I want to pass a datetime param :

Code: Select all

//dtdeb is a datetime
ParamByName('p_PDTDEB').AsDateTime:=dtdeb;

With this stored proc :

Code: Select all

CREATE PROCEDURE CALC(@p_CDGRP varchar(10), @p_PDTDEB datetime) as 
BEGIN 
  Update societe set CDSOC='001', DTDEB=@p_PDTDEB 
  where CDGRP=@p_CDGRP 
END 
go

an error occurs : "Invalid argument to date encode"


What's wrong?

Thanks a lot

Rom

[Ikar]

The reason is in incorrect processing of re-assigning parameter type for StoredProc in dbExpress.
So instead of

Code: Select all

ParamByName('p_PDTDEB').AsDateTime:=dtdeb; 

you should write

Code: Select all

ParamByName('p_PDTDEB').AsSQLTimeStamp := DateTimeToSQLTimeStamp(dtdeb); 

[Guest]

It's OK.

Thank you very much for your help.

Have a good day.

Rom

[xupacabr@]

My problem is very very strange....

Details:

################ Systems ####################
Delphi 7
Firebird 1.5
Windows XP

################ Program ####################

object SQLConnectionPS: TSQLConnection
ConnectionName = 'Firebird'
DriverName = 'Interbase'
GetDriverFunc = 'getSQLDriverINTERBASE'
LibraryName = 'dbexpint.dll'
LoginPrompt = False
Params.Strings = (
'DriverName=Interbase'
'BlobSize=-1'
'CommitRetain=False'
'Database=c:\psbiolite\PSBIOLITE.Fdb'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=masterkey'
'RoleName=RoleName'
'ServerCharSet=win1252'
'SQLDialect=3'
'Interbase TransIsolation=ReadCommited'
'User_Name=sysdba'
'WaitOnLocks=True')
VendorLib = 'c:\psbiolite\fbclient.dll'
Connected = True
Left = 8
Top = 336
end

################ SToredProc in Delphi ####################
object SQLStoredProcAlterar: TSQLStoredProc
MaxBlobSize = -1
Params = <
item
DataType = ftInteger
Name = 'ID'
ParamType = ptInput
end
item
DataType = ftString
Name = 'NOME'
ParamType = ptInput
end
SQLConnection = SQLConnectionPS
StoredProcName = 'PROC_ALTER_PS'
Left = 8
Top = 496
end

################ SToredProc in Firebird ####################
SET TERM ^ ;

CREATE PROCEDURE PROC_ALTER_PS (
ID INTEGER,
NOME VARCHAR(60)
AS
begin
UPDATE "ps" SET NOME = :NOME
where "id" = :id;
WHEN ANY do
exception erro_atualizacao;
END
^
SET TERM ; ^
GRANT SELECT,UPDATE ON "ps" TO PROCEDURE PROC_ALTER_PS;
GRANT EXECUTE ON PROCEDURE PROC_ALTER_PS TO SYSDBA;

################ Delphi Code ####################
procedure TF_PS.ActionConfirmarExecute(Sender: TObject);
begin
With SQLStoredProcAlterar do
ParamByName('NOME').AsString := LabeledEditPS.Text;
Try
SQLStoredProcAlterar.ExecProc;
Except
on E:Exception do
Begin
MessageDlg(E.Message,mtError, [mbok],0 );
Abort;
end;
end;
End;//end do with
end;


################ PROBLEM ####################

I have 3 TSQLStoredProc

1 - To Insert
2 - To Update
3 - To Delete

1 and 3 work fine !!!!

2 - Update, never update nothing....

If I try use the StoredProc in DBEXPERT, work fine !!! (after commit dialog) Not problem with StoredProc...

The Code is Right !

But dont Work and my hair be down !!!!!!

[Ikar]

DbxSda provide access only to MS SQL, not Interbase/Firebird.

[xupacabr@]

DbxSda provide access only to MS SQL, not Interbase/Firebird.

What is DBXSdA ?

[Ikar]

DbxSda is our dbExpress driver for SQL Server. This forum is intended for DbxSda support.