Hi I have struggling with stored procedure problems with SQL 6.5 most of it
using stored procedure is very new to me.
I have tried it both ways and have different problems with each way. Note: I
want to do it run time is my end result.
I set up in my project in my datamodule the following when I set the active
properity true I get the error message - Error Creating cursor handle
See If you can follow what I did methord 1
Name - properity was set auto to StoredProc1
Step One - Drop Stored Procedure control into my datamodule which is unit2
in my project
Step Two - Set The Database Properity to MyDatabase
Step Three - ParambindMode default to pbByName
Step Four - StoredProcName was set by select in list of stored procedure the
name
dbo.UnGenUniqueCallHistoryID;1
Step Five - Params using the edit two varables displayed Result, @TestUID
when selected on each one set the following:
DataType ftInteger
Name Result
Paramtype ptResult
DataType FtInteger
Name @TestUID
ParamType ptInput
Value 0
Type Integer
Step Six - Set the active properity to True
HERE IS WHERE I GOT THE ERROR MESSAGE -
This is methord 2 where I get a different error message - I even tried to
change the paramters around but still same error.
When I do it in code I get the Error message - general SQL Error
1 17:06:35 SQL Prepare: MSSQL - :1 = dbo.UnGenUniqueCallHistoryID;1
:2,
2 17:06:35 SQL Misc: MSSQL - Set stored procedure on or off
3 17:06:35 SQL Data In: MSSQL - Param = 1, Name = Result, Type =
fldINT32, Precision = 0, Scale = 0, Data = NULL
4 17:06:35 SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
fldINT32, Precision = 0, Scale = 0, Data = 659
5 17:06:35 SQL Misc: MSSQL - Set statement type
6 17:06:35 SQL Execute: MSSQL - :Result =
dbo.UnGenUniqueCallHistoryID;1 :InputInt,
7 17:06:35 SQL Error: MSSQL -
8 17:06:35 SQL Error: MSSQL - Unmapped SQL Error Code: 0
9 17:06:35 SQL Stmt: MSSQL - Reset
Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
Var
SProc: TStoredProc;
Begin
try
SProc := TStoredProc.Create(Nil);
SProc.DatabaseName := 'MyDataBase';
SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
// Reverese The order Parameters - Fix - Test Now - Still Fails
SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
SProc.Params.CreateParam(ftInteger, 'InputInt', ptInput);
SProc.ParamByName('InputInt').AsInteger := MyValue;
SProc.ExecProc;
Result := Sproc.ParamByName('Result').AsInteger; // Returns the value
that was returned from Stored Procedure
if Result > 0 Then
Begin
ShowMessage('This is the Old Call History Number Returned From
Server ' + IntToStr(Result));
CallHistNo := Result;
End
Else
Begin
ShowMessage('The Call History Number Could Not Be Decreased' +
Char(13) +
' Because of another User Increased It On Server');
// CallHistNo Stays the same as was - could Set Global Flag Here
// Result will Equal -1 Self Induced - Error from Server
End;
finally
SProc.Free;
End;
End;
THIS IS THE OLD LOG HERE AND OLD FUNCTION
1 11:59:09 SQL Prepare: MSSQL - :1 = dbo.UnGenUniqueCallHistoryID;1
:2,
2 11:59:09 SQL Misc: MSSQL - Set stored procedure on or off
3 11:59:09 SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
fldINT32, Precision = 0, Scale = 0, Data = 658
4 11:59:09 SQL Data In: MSSQL - Param = 1, Name = Result, Type =
fldINT32, Precision = 0, Scale = 0, Data = NULL
5 11:59:09 SQL Misc: MSSQL - Set statement type
6 11:59:09 SQL Execute: MSSQL - :Result =
dbo.UnGenUniqueCallHistoryID;1 :InputInt,
7 11:59:09 SQL Error: MSSQL -
8 11:59:09 SQL Error: MSSQL - Unmapped SQL Error Code: 0
9 11:59:09 SQL Stmt: MSSQL - Reset
Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
Var
SProc: TStoredProc;
Begin
try
SProc := TStoredProc.Create(Nil);
SProc.DatabaseName := 'MyDataBase';
SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
SProc.Params.CreateParam(ftInteger, 'InPutInt', ptInput);
SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
SProc.ParamByName('InputInt').AsInteger := MyValue;
SProc.ExecProc;
Result := Sproc.ParamByName('Result').AsInteger; // Returns the value
that was returned from Stored Procedure
if Result > 0 Then
Begin
ShowMessage('This is the Old Call History Number Returned From
Server ' + IntToStr(Result));
CallHistNo := Result;
End
Else
Begin
ShowMessage('The Call History Number Could Not Be Decreased' +
Char(13) +
' Because of another User Increased It On Server');
// CallHistNo Stays the same as was - could Set Global Flag Here
// Result will Equal -1 Self Induced - Error from Server
End;
finally
SProc.Free;
End;
End;
HERE IS THE ACTUAL STORED PROCEDURE - NOTE: THE STORED PROCEDURE RUN BY ITS
SELF IN ISQL QUERY TOOL.
if exists (select * from sysobjects where id =
object_id('dbo.UnGenUniqueCallHistoryID') and sysstat & 0xf = 4)
drop procedure dbo.UnGenUniqueCallHistoryID
GO
CREATE PROCEDURE UnGenUniqueCallHistoryID(
@TestUID Int)
As
Begin
Declare @UID Int
Declare @MessageString VarChar(255)
Print 'Stored Procedure - UNGENUNIQUECALLHISTORYID'
Begin Tran
Select @UID = Last_Call_History_No
From SystemInfo
if (@TestUID = @UID)
Begin
Select @MessageString = 'This CallHistoryNo Can Be decreased
from the server :' + CONVERT(Char(20),@UID)
Print @MessageString
update SystemInfo
set Last_Call_History_No = Last_Call_History_No - 1 Select
@UID = Last_Call_History_No
From SystemInfo
End
Else
Begin
Select @MessageString = 'This CallHistoryNo Can NOT Be
decreased from the server :' + CONVERT(Char(20),@UID)
Print @MessageString
Select @UID = -1
End
Commit Tran
Return @UID
End
GO
