Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

Returning a Value from a StoredProcedure

https://zeoslib.sourceforge.io/viewtopic.php?t=1923

Page 1 of 1

Returning a Value from a StoredProcedure

Posted: 17.07.2008, 16:15
by nath0rn
Hey again, :)

It's that time again.

I have an idea for a event to send a message to all users from the client application. How I was going to do this was, add a stored procedure with two input variables, and one output variable.

Using Events, it will popup the output variable (the message the client user wants to send to everyone).

So far, I have a ZQuery (called SendEvent) with the SQL.text:

Code: Select all

EXECUTE PROCEDURE SEND_CUSTOM(:EVENT_NAME,:INFO)
And to execute the query I am doing the following:

Code: Select all

sendEvent.Params[0].Value := 'message';
sendEvent.params[1].Value := 'Test Message';
sendEvent.ExecSQL;
sendEvent.Refresh; // This is here out of desperation to try get it to work
showmessage(SendEventGIVEBACK.AsString);
Param[0] is the Event string, Param[1] is the Message.

With the query itself, I added a field called GIVEBACK, which should house 'Test Message'.

The procedure is as follows:

Code: Select all

CREATE PROCEDURE SEND_CUSTOM(
  EVENT_NAME VARCHAR(127) CHARACTER SET WIN1251,
  INFO VARCHAR(255) CHARACTER SET WIN1251 DEFAULT NULL)
RETURNS(
  GIVEBACK VARCHAR(255) CHARACTER SET WIN1251)
AS
BEGIN
POST_EVENT event_name;
GIVEBACK = INFO;
SUSPEND;
END;
The procedure works in an SQL Management application, and returns the inputted text INFO as GIVEBACK in a row/column.

So, what I am trying to do is, send a certain event 'message', once that is received, it will get the message contents from GIVEBACK, then display it appropriately.

All this seems fine in theory, however, when using showmessage(SendEventGIVEBACK.AsString); on the EventAlerter, it only displays a blank message.

Is anyone expert enough able to see the problem here? Perhaps I am doing something wrong (obviously LOL). This method of solving the problem is the first thing that came to my mind, so perhaps there is a different way. It's too late at the moment to use the think function in my brain. :)

Thankyou for your time, kind sir(s).

~Nathan

Posted: 28.11.2008, 09:43
by jk
i found similar problem with sql server
http://zeos.firmos.at/viewforum.php?f=22
as i see now - this is two different bugs
i made some patches in ZDbcInterbase6Statement.pas (from zeos 6.6.4 )
may be it helps you:

Code: Select all

{*********************************************************}
{                                                         }
{                 Zeos Database Objects                   }
{         Interbase Database Connectivity Classes         }
{                                                         }
{        Originally written by Sergey Merkuriev           }
{                                                         }
{*********************************************************}

{@********************************************************}
{    Copyright (c) 1999-2006 Zeos Development Group       }
{                                                         }
{ License Agreement:                                      }
{                                                         }
{ This library is distributed in the hope that it will be }
{ useful, but WITHOUT ANY WARRANTY; without even the      }
{ implied warranty of MERCHANTABILITY or FITNESS FOR      }
{ A PARTICULAR PURPOSE.  See the GNU Lesser General       }
{ Public License for more details.                        }
{                                                         }
{ The source code of the ZEOS Libraries and packages are  }
{ distributed under the Library GNU General Public        }
{ License (see the file COPYING / COPYING.ZEOS)           }
{ with the following  modification:                       }
{ As a special exception, the copyright holders of this   }
{ library give you permission to link this library with   }
{ independent modules to produce an executable,           }
{ regardless of the license terms of these independent    }
{ modules, and to copy and distribute the resulting       }
{ executable under terms of your choice, provided that    }
{ you also meet, for each linked independent module,      }
{ the terms and conditions of the license of that module. }
{ An independent module is a module which is not derived  }
{ from or based on this library. If you modify this       }
{ library, you may extend this exception to your version  }
{ of the library, but you are not obligated to do so.     }
{ If you do not wish to do so, delete this exception      }
{ statement from your version.                            }
{                                                         }
{                                                         }
{ The project web site is located on:                     }
{   http://zeos.firmos.at  (FORUM)                        }
{   http://zeosbugs.firmos.at (BUGTRACKER)                }
{   svn://zeos.firmos.at/zeos/trunk (SVN Repository)      }
{                                                         }
{   http://www.sourceforge.net/projects/zeoslib.          }
{   http://www.zeoslib.sourceforge.net                    }
{                                                         }
{                                                         }
{                                                         }
{                                 Zeos Development Group. }
{********************************************************@}

unit ZDbcInterbase6Statement;

interface

{$I ZDbc.inc}

uses Classes, SysUtils, Types, ZDbcIntfs, ZDbcStatement, ZDbcInterbase6,
  ZPlainInterbase6, ZDbcInterbase6Utils, ZDbcInterbase6ResultSet,
  ZPlainFirebirdInterbaseConstants,
  ZCompatibility, ZDbcLogging, ZVariant, ZMessages;

type

  {** Implements Generic Interbase6 Statement. }
  TZInterbase6Statement = class(TZAbstractStatement)
  private
    FCachedBlob: boolean;
    FStatusVector: TARRAY_ISC_STATUS;
    FIBConnection: IZInterbase6Connection;
  protected
    procedure CheckInterbase6Error(const Sql: string = '');
  public
    constructor Create(Connection: IZConnection; Info: TStrings);

    function ExecuteQuery(const SQL: string): IZResultSet; override;
    function ExecuteUpdate(const SQL: string): Integer; override;
    function Execute(const SQL: string): Boolean; override;
  end;

  {** Implements Prepared SQL Statement. }
  TZInterbase6PreparedStatement = class(TZAbstractPreparedStatement)
  private
    FCachedBlob: boolean;
    FParamSQLData: IZParamsSQLDA;
    FStatusVector: TARRAY_ISC_STATUS;
    FIBConnection: IZInterbase6Connection;
  protected
    procedure CheckInterbase6Error(const Sql: string = '');
  public
    constructor Create(Connection: IZConnection; const SQL: string; Info: TStrings);

    function ExecuteQuery(const SQL: string): IZResultSet; override;
    function ExecuteUpdate(const SQL: string): Integer; override;
    function Execute(const SQL: string): Boolean; override;

    function ExecuteQueryPrepared: IZResultSet; override;
    function ExecuteUpdatePrepared: Integer; override;
    function ExecutePrepared: Boolean; override;
  end;

  TZInterbase6CallableStatement = class(TZAbstractCallableStatement)
  private
    FCachedBlob: boolean;
    FParamSQLData: IZParamsSQLDA;
    FStatusVector: TARRAY_ISC_STATUS;
    FIBConnection: IZInterbase6Connection;
    FOutParamIndexes: TIntegerDynArray;
  protected
    procedure CheckInterbase6Error(const Sql: string = '');
    procedure FetchOutParams(Value: IZResultSQLDA);
    function GetProcedureSql(SelectProc: boolean): string;
    procedure TrimInParameters;
  public
    constructor Create(Connection: IZConnection; const SQL: string; Info: TStrings);
    procedure RegisterOutParameter(ParameterIndex: Integer;
      SQLType: Integer); override;
    procedure ClearParameters; override;

    function ExecuteQuery(const SQL: string): IZResultSet; override;
    function ExecuteUpdate(const SQL: string): Integer; override;
    function Execute(const SQL: string): Boolean; override;

    function ExecuteQueryPrepared: IZResultSet; override;
    function ExecuteUpdatePrepared: Integer; override;
    function ExecutePrepared: Boolean; override;
  end;

implementation

uses ZSysUtils, ZDbcUtils;

{ TZInterbase6Statement }

{**
   Check interbase error status
   @param Sql the used sql tring
}
procedure TZInterbase6Statement.CheckInterbase6Error(const Sql: string = '');
begin
  ZDbcInterbase6Utils.CheckInterbase6Error(FIBConnection.GetPlainDriver,
    FStatusVector, lcExecute, SQL);
end;


{**
  Constructs this object and assignes the main properties.
  @param Connection a database connection object.
  @param Handle a connection handle pointer.
  @param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
  @param Info a statement parameters.
}
constructor TZInterbase6Statement.Create(Connection: IZConnection;
  Info: TStrings);
begin
  inherited Create(Connection, Info);

  FIBConnection := Connection as IZInterbase6Connection;
  ResultSetType := rtScrollInsensitive;
  FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
end;

{**
  Destroys this object and cleanups the memory.
}
{**
  Executes an SQL statement that returns a single <code>ResultSet</code> object.
  @param sql typically this is a static SQL <code>SELECT</code> statement
  @return a <code>ResultSet</code> object that contains the data produced by the
    given query; never <code>null</code>
}
{$HINTS OFF}
function TZInterbase6Statement.ExecuteQuery(const SQL: string): IZResultSet;
var
  Cursor: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  StmtHandle := nil;

  with FIBConnection do
  begin
    SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);

    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

//      if not(StatementType in [stSelect, stSelectForUpdate]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
          SQL, StmtHandle, SQLData);

      GetPlainDriver.isc_dsql_execute(@FStatusVector, GetTrHandle,
        @StmtHandle, GetDialect, SQLData.GetData);
      CheckInterbase6Error(SQL);

      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        if CursorName <> '' then
        begin
          Cursor := CursorName;
          GetPlainDriver.isc_dsql_set_cursor_name(@FStatusVector,
            @StmtHandle, PChar(Cursor), 0);
          CheckInterbase6Error(SQL);
        end;

        Result := GetCachedResultSet(SQL, Self,
          TZInterbase6ResultSet.Create(Self, SQL, StmtHandle, Cursor, SQLData, nil, FCachedBlob));
      end
      else
        raise EZSQLException.Create(SCanNotRetrieveResultSetData);

      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
       FreeStatement(GetPlainDriver, StmtHandle);
       raise;
      end;
    end;
  end;
end;
{$HINTS OFF}

{**
  Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
  <code>DELETE</code> statement. In addition,
  SQL statements that return nothing, such as SQL DDL statements,
  can be executed.

  @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
    <code>DELETE</code> statement or an SQL statement that returns nothing
  @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
    or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
}
{$HINTS OFF}
function TZInterbase6Statement.ExecuteUpdate(const SQL: string): Integer;
var
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  Result := -1;
  StmtHandle := nil;
  with FIBConnection do
  begin
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

//      if StatementType in [stExecProc, stSelect, stSelectForUpdate] then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle,
        @StmtHandle, GetDialect, nil, nil);
      CheckInterbase6Error(SQL);

      case StatementType of
        stCommit, stRollback, stUnknown: Result := -1;
        else begin
          Result := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);
          LastUpdateCount := Result;
        end;
      end;

      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    finally
      FreeStatement(GetPlainDriver, StmtHandle);
    end;
  end;
end;
{$HINTS ON}

{**
  Executes an SQL statement that may return multiple results.
  Under some (uncommon) situations a single SQL statement may return
  multiple result sets and/or update counts.  Normally you can ignore
  this unless you are (1) executing a stored procedure that you know may
  return multiple results or (2) you are dynamically executing an
  unknown SQL string.  The  methods <code>execute</code>,
  <code>getMoreResults</code>, <code>getResultSet</code>,
  and <code>getUpdateCount</code> let you navigate through multiple results.

  The <code>execute</code> method executes an SQL statement and indicates the
  form of the first result.  You can then use the methods
  <code>getResultSet</code> or <code>getUpdateCount</code>
  to retrieve the result, and <code>getMoreResults</code> to
  move to any subsequent result(s).

  @param sql any SQL statement
  @return <code>true</code> if the next result is a <code>ResultSet</code> object;
  <code>false</code> if it is an update count or there are no more results
  @see #getResultSet
  @see #getUpdateCount
  @see #getMoreResults
}
{$HINTS OFF}
function TZInterbase6Statement.Execute(const SQL: string): Boolean;
var
  Cursor: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  StmtHandle := nil;
  with FIBConnection do
  begin
    try
      Result := False;
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver, GetDBHandle, GetTrHandle,
        GetDialect, SQL, StmtHandle);

      { Check statement type }
//      if not (StatementType in [stExecProc]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      { Create Result SQLData if statement returns result }
      if StatementType = stSelect then
      begin
        SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
        PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
          SQL, StmtHandle, SQLData);
      end;

      { Execute prepared statement }
      GetPlainDriver.isc_dsql_execute(@FStatusVector, GetTrHandle,
              @StmtHandle, GetDialect, nil);
      CheckInterbase6Error(Sql);
      { Set updated rows count }
      LastUpdateCount := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);

      case StatementType of
        stInsert, stDelete, stUpdate, stSelectForUpdate: Result := False;
      else
        Result := True;
      end;

      { Create ResultSet if possible else free Stateent Handle }
      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        if CursorName <> '' then
        begin
          Cursor := CursorName;
          GetPlainDriver.isc_dsql_set_cursor_name(@FStatusVector,
            @StmtHandle, PChar(Cursor), 0);
          CheckInterbase6Error(SQL);
        end;

        LastResultSet := GetCachedResultSet(SQL, Self,
          TZInterbase6ResultSet.Create(Self, SQL, StmtHandle, Cursor,
            SQLData, nil, FCachedBlob));
      end else  begin
        LastResultSet := nil;
        FreeStatement(GetPlainDriver, StmtHandle);
      end;

      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
       FreeStatement(GetPlainDriver, StmtHandle);
       raise;
      end;
    end;
  end;
end;
{$HINTS ON}

{ TZInterbase6PreparedStatement }

{**
   Check interbase error status
   @param Sql the used sql tring
}
procedure TZInterbase6PreparedStatement.CheckInterbase6Error(const Sql: string);
begin
  ZDbcInterbase6Utils.CheckInterbase6Error(FIBConnection.GetPlainDriver,
    FStatusVector, lcExecute, SQL);
end;

{**
  Constructs this object and assignes the main properties.
  @param Connection a database connection object.
  @param Handle a connection handle pointer.
  @param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
  @param Info a statement parameters.
}
constructor TZInterbase6PreparedStatement.Create(Connection: IZConnection;
  const SQL: string; Info: TStrings);
begin
  inherited Create(Connection, SQL, Info);

  FIBConnection := Connection as IZInterbase6Connection;
  ResultSetType := rtScrollInsensitive;
  FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
  with FIBConnection do
    FParamSQLData := TZParamsSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
end;

{**
  Executes an SQL statement that may return multiple results.
  Under some (uncommon) situations a single SQL statement may return
  multiple result sets and/or update counts.  Normally you can ignore
  this unless you are (1) executing a stored procedure that you know may
  return multiple results or (2) you are dynamically executing an
  unknown SQL string.  The  methods <code>execute</code>,
  <code>getMoreResults</code>, <code>getResultSet</code>,
  and <code>getUpdateCount</code> let you navigate through multiple results.

  The <code>execute</code> method executes an SQL statement and indicates the
  form of the first result.  You can then use the methods
  <code>getResultSet</code> or <code>getUpdateCount</code>
  to retrieve the result, and <code>getMoreResults</code> to
  move to any subsequent result(s).

  @param sql any SQL statement
  @return <code>true</code> if the next result is a <code>ResultSet</code> object;
  <code>false</code> if it is an update count or there are no more results
  @see #getResultSet
  @see #getUpdateCount
  @see #getMoreResults
}

function TZInterbase6PreparedStatement.Execute(const SQL: string): Boolean;
begin
  Self.SQL := SQL;
  Result := ExecutePrepared;
end;

{**
  Executes any kind of SQL statement.
  Some prepared statements return multiple results; the <code>execute</code>
  method handles these complex statements as well as the simpler
  form of statements handled by the methods <code>executeQuery</code>
  and <code>executeUpdate</code>.
  @see Statement#execute
}
{$HINTS OFF}
function TZInterbase6PreparedStatement.ExecutePrepared: Boolean;
var
  Cursor: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  Result := False;
  StmtHandle := nil;
  with FIBConnection do
  begin
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

//      if not (StatementType in [stExecProc]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      if StatementType in [stSelect, stExecProc] then
      begin
        SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
        PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
          SQL, StmtHandle, SQLData);
      end;

      PrepareParameters(GetPlainDriver, SQL, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);

      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle, @StmtHandle,
            GetDialect, FParamSQLData.GetData, nil);
      CheckInterbase6Error(SQL);

      LastUpdateCount := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);

      case StatementType of
        stInsert, stDelete, stUpdate, stSelectForUpdate: Result := False;
      else
        Result := True;
      end;

      { Create ResultSet if possible else free Stateent Handle }
      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        Cursor := RandomString(12);
        LastResultSet := GetCachedResultSet(SQL, Self,
          TZInterbase6ResultSet.Create(Self, SQL, StmtHandle, Cursor,
            SQLData, nil, FCachedBlob));
      end
      else begin
        LastResultSet := nil;
        FreeStatement(GetPlainDriver, StmtHandle);
      end;

      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
       FreeStatement(GetPlainDriver, StmtHandle);
       raise;
      end;
    end;
  end;
end;
{$HINTS ON}

{**
  Executes an SQL statement that returns a single <code>ResultSet</code> object.
  @param sql typically this is a static SQL <code>SELECT</code> statement
  @return a <code>ResultSet</code> object that contains the data produced by the
    given query; never <code>null</code>
}
function TZInterbase6PreparedStatement.ExecuteQuery(const SQL: string): IZResultSet;
begin
  Self.SQL := SQL;
  Result := ExecuteQueryPrepared;
end;

{**
  Executes the SQL query in this <code>PreparedStatement</code> object
  and returns the result set generated by the query.

  @return a <code>ResultSet</code> object that contains the data produced by the
    query; never <code>null</code>
}
{$HINTS OFF}
function TZInterbase6PreparedStatement.ExecuteQueryPrepared: IZResultSet;
var
  Cursor: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  StmtHandle := nil;
  with FIBConnection do
  begin
    SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

//      if not(StatementType in [stSelect, stSelectForUpdate]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
        SQL, StmtHandle, SQLData);
      PrepareParameters(GetPlainDriver, SQL, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);

      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle, @StmtHandle,
        GetDialect, FParamSQLData.GetData, nil);
      CheckInterbase6Error(SQL);

      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        if CursorName <> '' then
        begin
          Cursor := CursorName;
          GetPlainDriver.isc_dsql_set_cursor_name(@FStatusVector,
            @StmtHandle, PChar(Cursor), 0);
          CheckInterbase6Error(SQL);
        end;  

        Result := GetCachedResultSet(SQL, Self,
          TZInterbase6ResultSet.Create(Self, SQL, StmtHandle, Cursor, SQLData, nil, FCachedBlob));
      end
      else
        raise EZSQLException.Create(SCanNotRetrieveResultSetData);

     LastResultSet := Result;
     { Logging SQL Command }
     DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
        FreeStatement(GetPlainDriver, StmtHandle);
        raise;
      end;
    end;
  end;
end;
{$HINTS ON}

{**
  Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
  <code>DELETE</code> statement. In addition,
  SQL statements that return nothing, such as SQL DDL statements,
  can be executed.

  @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
    <code>DELETE</code> statement or an SQL statement that returns nothing
  @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
    or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
}
function TZInterbase6PreparedStatement.ExecuteUpdate(const SQL: string): Integer;
begin
  Self.SQL := SQL;
  Result := ExecuteUpdatePrepared;
end;

{**
  Executes the SQL INSERT, UPDATE or DELETE statement
  in this <code>PreparedStatement</code> object.
  In addition,
  SQL statements that return nothing, such as SQL DDL statements,
  can be executed.

  @return either the row count for INSERT, UPDATE or DELETE statements;
  or 0 for SQL statements that return nothing
}
{$HINTS OFF}
function TZInterbase6PreparedStatement.ExecuteUpdatePrepared: Integer;
var
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  Result := -1;
  StmtHandle := nil;

  with FIBConnection do
  begin
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver, GetDBHandle,
        GetTrHandle, GetDialect, SQL, StmtHandle);

//      if StatementType in [stExecProc, stSelect, stSelectForUpdate] then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      PrepareParameters(GetPlainDriver, SQL, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);

      GetPlainDriver.isc_dsql_execute(@FStatusVector, GetTrHandle,
        @StmtHandle, GetDialect, FParamSQLData.GetData);
      CheckInterbase6Error(SQL);

      Result := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);
      LastUpdateCount := Result;

      case StatementType of
        stCommit, stRollback, stUnknown: Result := -1;
      end;

      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    finally
      FreeStatement(GetPlainDriver, StmtHandle);
    end;
  end;
end;
{$HINTS ON}


{ TZInterbase6CallableStatement }

{**
   Check interbase error status
   @param Sql the used sql tring
}
procedure TZInterbase6CallableStatement.CheckInterbase6Error(const Sql: string);
begin
  ZDbcInterbase6Utils.CheckInterbase6Error(FIBConnection.GetPlainDriver,
    FStatusVector, lcExecute, SQL);
end;

{**
  Constructs this object and assignes the main properties.
  @param Connection a database connection object.
  @param Handle a connection handle pointer.
  @param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
  @param Info a statement parameters.
}
procedure TZInterbase6CallableStatement.ClearParameters;
var
  I: integer;
begin
  for I := 0 to OutParamCount-1 do
  begin
    OutParamValues[I] := NullVariant;
    OutParamTypes[I] := ZDbcIntfs.stUnknown;
  end;
end;

constructor TZInterbase6CallableStatement.Create(Connection: IZConnection;
  const SQL: string; Info: TStrings);
begin
  inherited Create(Connection, SQL, Info);

  FIBConnection := Connection as IZInterbase6Connection;
  ResultSetType := rtScrollInsensitive;
  FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
  with FIBConnection do
    FParamSQLData := TZParamsSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
end;

{**
  Executes an SQL statement that may return multiple results.
  Under some (uncommon) situations a single SQL statement may return
  multiple result sets and/or update counts.  Normally you can ignore
  this unless you are (1) executing a stored procedure that you know may
  return multiple results or (2) you are dynamically executing an
  unknown SQL string.  The  methods <code>execute</code>,
  <code>getMoreResults</code>, <code>getResultSet</code>,
  and <code>getUpdateCount</code> let you navigate through multiple results.

  The <code>execute</code> method executes an SQL statement and indicates the
  form of the first result.  You can then use the methods
  <code>getResultSet</code> or <code>getUpdateCount</code>
  to retrieve the result, and <code>getMoreResults</code> to
  move to any subsequent result(s).

  @param sql any SQL statement
  @return <code>true</code> if the next result is a <code>ResultSet</code> object;
  <code>false</code> if it is an update count or there are no more results
  @see #getResultSet
  @see #getUpdateCount
  @see #getMoreResults
}

function TZInterbase6CallableStatement.Execute(const SQL: string): Boolean;
begin
  Self.SQL := SQL;
  Result := ExecutePrepared;
end;

{**
  Executes any kind of SQL statement.
  Some prepared statements return multiple results; the <code>execute</code>
  method handles these complex statements as well as the simpler
  form of statements handled by the methods <code>executeQuery</code>
  and <code>executeUpdate</code>.
  @see Statement#execute
}
{$HINTS OFF}
function TZInterbase6CallableStatement.ExecutePrepared: Boolean;
var
  Cursor, ProcSql: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  Result := False;
  StmtHandle := nil;
  with FIBConnection do
  begin
    TrimInParameters;
    ProcSql := GetProcedureSql(False);
    SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
    try
      { Prepare statement }
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver, GetDBHandle, GetTrHandle,
        GetDialect, ProcSql, StmtHandle);
      PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
        SQL, StmtHandle, SQLData);
      PrepareParameters(GetPlainDriver, ProcSql, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);
      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle, @StmtHandle,
            GetDialect, FParamSQLData.GetData, SQLData.GetData);
      CheckInterbase6Error(SQL);

      LastUpdateCount := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);

      case StatementType of
        stInsert, stDelete, stUpdate, stSelectForUpdate: Result := False;
      else
        Result := True;
      end;

      { Create ResultSet if possible else free Stateent Handle, ResultSQlData and
        ParamSqlData }
      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        Cursor := RandomString(12);
        LastResultSet := GetCachedResultSet(SQL, Self,
          TZInterbase6ResultSet.Create(Self, SQL, StmtHandle, Cursor,
            SQLData, nil, FCachedBlob));
      end else begin
        { Fetch data and fill Output params }
        FetchOutParams(SQLData);
        FreeStatement(GetPlainDriver, StmtHandle);
        LastResultSet := nil;
      end;

      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
       FreeStatement(GetPlainDriver, StmtHandle);
       raise;
      end;
    end;
  end;
end;
{$HINTS ON}

{**
  Executes an SQL statement that returns a single <code>ResultSet</code> object.
  @param sql typically this is a static SQL <code>SELECT</code> statement
  @return a <code>ResultSet</code> object that contains the data produced by the
    given query; never <code>null</code>
}
function TZInterbase6CallableStatement.ExecuteQuery(
  const SQL: string): IZResultSet;
begin
  Self.SQL := SQL;
  Result := ExecuteQueryPrepared;
end;

{**
  Executes the SQL query in this <code>PreparedStatement</code> object
  and returns the result set generated by the query.

  @return a <code>ResultSet</code> object that contains the data produced by the
    query; never <code>null</code>
}
{$HINTS OFF}
function TZInterbase6CallableStatement.ExecuteQueryPrepared: IZResultSet;
var
  Cursor: string;
  ProcSql: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
  StmtHandle := nil;

  with FIBConnection do
  begin
    TrimInParameters;
    ProcSql := GetProcedureSql(True);
    SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, ProcSql, StmtHandle);

//      if not(StatementType in [stSelect, stSelectForUpdate]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
        SQL, StmtHandle, SQLData);
      PrepareParameters(GetPlainDriver, ProcSql, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);

      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle, @StmtHandle,
        GetDialect, FParamSQLData.GetData, nil);
      CheckInterbase6Error(ProcSql);

      if (StatementType in [stSelect, stExecProc])
        and (SQLData.GetFieldCount <> 0) then
      begin
        if CursorName <> '' then
        begin
          Cursor := CursorName;
          GetPlainDriver.isc_dsql_set_cursor_name(@FStatusVector,
            @StmtHandle, PChar(Cursor), 0);
          CheckInterbase6Error(ProcSql);
        end;  

        Result := GetCachedResultSet(ProcSql, Self,
          TZInterbase6ResultSet.Create(Self, ProcSql, StmtHandle, Cursor, SQLData, nil, FCachedBlob));
      end;
          
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
        FreeStatement(GetPlainDriver, StmtHandle);
        raise;
      end;
    end;
  end;
end;
{$HINTS ON}

{**
  Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
  <code>DELETE</code> statement. In addition,
  SQL statements that return nothing, such as SQL DDL statements,
  can be executed.

  @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
    <code>DELETE</code> statement or an SQL statement that returns nothing
  @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
    or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
}
function TZInterbase6CallableStatement.ExecuteUpdate(const SQL: string): Integer;
begin
  Self.SQL := SQL;
  Result := ExecuteUpdatePrepared;
end;

{**
  Executes the SQL INSERT, UPDATE or DELETE statement
  in this <code>PreparedStatement</code> object.
  In addition,
  SQL statements that return nothing, such as SQL DDL statements,
  can be executed.

  @return either the row count for INSERT, UPDATE or DELETE statements;
  or 0 for SQL statements that return nothing
}
function TZInterbase6CallableStatement.ExecuteUpdatePrepared: Integer;
var
  ProcSql: string;
  SQLData: IZResultSQLDA;
  StmtHandle: TISC_STMT_HANDLE;
  StatementType: TZIbSqlStatementType;
begin
//  Result := -1;
  StmtHandle := nil;

  with FIBConnection do
  begin
    TrimInParameters;
    ProcSql := GetProcedureSql(False);
    SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
    try
      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, ProcSql, StmtHandle);

//      if not (StatementType in [stSelect, stSelectForUpdate]) then
//        raise EZSQLException.Create(SStatementIsNotAllowed);

      PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
        SQL, StmtHandle, SQLData);
      PrepareParameters(GetPlainDriver, ProcSql, InParamValues, InParamTypes,
        InParamCount, GetDialect, StmtHandle, FParamSQLData);

      GetPlainDriver.isc_dsql_execute2(@FStatusVector, GetTrHandle, @StmtHandle,
        GetDialect, FParamSQLData.GetData, SQLData.GetData);
      CheckInterbase6Error(ProcSql);

      Result := GetAffectedRows(GetPlainDriver, StmtHandle, StatementType);
      LastUpdateCount := Result;
      { Fetch data and fill Output params }
      FetchOutParams(SQLData);
      { Autocommit statement. }
      if Connection.GetAutoCommit then
        Connection.Commit;
      { Logging SQL Command }
      DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
    except
      on E: Exception do begin
        FreeStatement(GetPlainDriver, StmtHandle);
        raise;
      end;
    end;
  end;
end;

{**
  Set output parameters values from IZResultSQLDA.
  @param Value a IZResultSQLDA object.
}
procedure TZInterbase6CallableStatement.FetchOutParams(
  Value: IZResultSQLDA);
var
  I: Integer;
  Temp: TZVariant;
begin
//  SetOutParamCount(Value.GetFieldCount);
  for I := 0 to Value.GetFieldCount-1 do
  begin
    if Value.IsNull(I) then
      DefVarManager.SetNull(Temp)
    else
      case Value.GetFieldSqlType(I) of
      stBoolean:
        DefVarManager.SetAsBoolean(Temp, Value.GetBoolean(I));
      stByte:
        DefVarManager.SetAsInteger(Temp, Value.GetByte(I));
      stShort:
        DefVarManager.SetAsInteger(Temp, Value.GetShort(I));
      stInteger:
        DefVarManager.SetAsInteger(Temp, Value.GetInt(I));
      stLong:
        DefVarManager.SetAsInteger(Temp, Value.GetLong(I));
      stFloat:
        DefVarManager.SetAsFloat(Temp, Value.GetFloat(I));
      stDouble:
        DefVarManager.SetAsFloat(Temp, Value.GetDouble(I));
      stBigDecimal:
        DefVarManager.SetAsFloat(Temp, Value.GetBigDecimal(I));
      stString:
        DefVarManager.SetAsString(Temp, Value.GetString(I));
      stUnicodeString:
//        DefVarManager.SetAsUnicodeString(Temp, Value.GetUnicodeString(I));
        DefVarManager.SetAsUnicodeString(Temp, Value.GetString(I));
      stDate:
        DefVarManager.SetAsDateTime(Temp, Value.GetDate(I));
      stTime:
        DefVarManager.SetAsDateTime(Temp, Value.GetTime(I));
      stTimestamp:
        DefVarManager.SetAsDateTime(Temp, Value.GetTimestamp(I));
    end;
    OutParamValues[FOutParamIndexes[I]] := Temp;
  end;
end;

{**
   Create sql string for calling stored procedure.
   @param SelectProc indicate use <b>EXECUTE PROCEDURE</b> or
    <b>SELECT</b> staement
   @return a Stored Procedure SQL string 
}
function TZInterbase6CallableStatement.GetProcedureSql(
  SelectProc: boolean): string;

  function GenerateParamsStr(Count: integer): string;
  var
    I: integer;
  begin
    for I := 0 to Count - 1 do
    begin
      if Result <> '' then
        Result := Result + ',';
      Result := Result + '?';
    end;
  end;

var
  InParams: string;
begin
  InParams := GenerateParamsStr(High(InParamValues) + 1);
  if InParams <> '' then
    InParams := '(' + InParams + ')';

  if SelectProc then
    Result := 'SELECT * FROM ' + SQL + InParams
  else
    Result := 'EXECUTE PROCEDURE ' + SQL + InParams;
end;

{**
   Function remove stUnknown paramters from InParamTypes and InParamValues
}
procedure TZInterbase6CallableStatement.RegisterOutParameter(
  ParameterIndex, SQLType: Integer);
var
  k: integer;
begin
  inherited;
  k := Length(FOutParamIndexes);
  SetLength(FOutParamIndexes,k+1);
  FOutParamIndexes[k] := ParameterIndex-1;
end;

procedure TZInterbase6CallableStatement.TrimInParameters;
var
  I: integer;
  ParamValues: TZVariantDynArray;
  ParamTypes: TZSQLTypeArray;
  ParamCount: Integer;
begin
  ParamCount := 0;
  SetLength(ParamValues, InParamCount);
  SetLength(ParamTypes, InParamCount);

  for I := 0 to High(InParamTypes) do
  begin
    if InParamTypes[I] = ZDbcIntfs.stUnknown then
     Continue;

    ParamTypes[ParamCount] := InParamTypes[I];
    ParamValues[ParamCount] := InParamValues[I];
    Inc(ParamCount);
  end;
  if ParamCount = InParamCount then
    Exit;
  InParamTypes := ParamTypes;
  InParamValues := ParamValues;
  SetInParamCount(ParamCount);
end;

end.

Posted: 03.12.2008, 10:07
by mdaems
Hi jk,

A question : isn't this a general TZStoredProcedure problem over all databases?
Shouldn't this patch be done to TZAbstractCallableStatement to fix this for all databases?

Can you please check and eventually send me a more general patch (just the necessary changes) and I'll commit it ASAP to SVN. (Probably even as a 6.6-patch)

Mark

Posted: 20.03.2009, 14:09
by jk
mdaems,
> isn't this a general TZStoredProcedure problem over all databases?
No, ADO and Oracle works fine.

>Shouldn't this patch be done to TZAbstractCallableStatement to fix this for all databases?
no - there is different logic in different databases, bug was similar but changes - differenet.


Index: C:/test1/ZDbcDbLibStatement.pas
===================================================================
--- C:/test1/ZDbcDbLibStatement.pas (revision 8)
+++ C:/test1/ZDbcDbLibStatement.pas (working copy)
@@ -115,6 +115,7 @@

public
constructor Create(Connection: IZConnection; ProcName: string; Info: TStrings);
+ procedure ClearParameters; override;



procedure RegisterOutParameter(ParameterIndex: Integer;
SqlType: Integer); override;
@@ -816,5 +817,19 @@
SetOutParamCount(NewParamCount);
end;

+procedure TZDBLibCallableStatement.ClearParameters;
+var
+ I: integer;
+begin
+ for I := 0 to OutParamCount-1 do
+ OutParamValues := NullVariant;
+
+ for I := 0 to InParamCount-1 do
+ begin
+ InParamValues := NullVariant;
+ InParamTypes := ZDbcIntfs.stUnknown;
+ end;
+end;
+
end.


--------------------

Index: C:/test1/ZDbcInterbase6Statement.pas
===================================================================
--- C:/test1/ZDbcInterbase6Statement.pas (revision 8)
+++ C:/test1/ZDbcInterbase6Statement.pas (working copy)
@@ -57,7 +57,7 @@

{$I ZDbc.inc}

-uses Classes, SysUtils, ZDbcIntfs, ZDbcStatement, ZDbcInterbase6,
+uses Classes, SysUtils, Types, ZDbcIntfs, ZDbcStatement, ZDbcInterbase6,
ZPlainInterbase6, ZDbcInterbase6Utils, ZDbcInterbase6ResultSet,
ZPlainFirebirdInterbaseConstants,
ZCompatibility, ZDbcLogging, ZVariant, ZMessages;
@@ -107,6 +107,7 @@
FParamSQLData: IZParamsSQLDA;
FStatusVector: TARRAY_ISC_STATUS;
FIBConnection: IZInterbase6Connection;
+ FOutParamIndexes: TIntegerDynArray;
protected
procedure CheckInterbase6Error(const Sql: string = '');
procedure FetchOutParams(Value: IZResultSQLDA);
@@ -114,6 +115,9 @@
procedure TrimInParameters;
public
constructor Create(Connection: IZConnection; const SQL: string; Info: TStrings);
+ procedure RegisterOutParameter(ParameterIndex: Integer;
+ SQLType: Integer); override;
+ procedure ClearParameters; override;

function ExecuteQuery(const SQL: string): IZResultSet; override;
function ExecuteUpdate(const SQL: string): Integer; override;
@@ -682,6 +686,23 @@
@param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
@param Info a statement parameters.
}
+procedure TZInterbase6CallableStatement.ClearParameters;
+var
+ I: integer;
+begin
+ for I := 0 to OutParamCount-1 do
+ begin
+ OutParamValues := NullVariant;
+ OutParamTypes := ZDbcIntfs.stUnknown;
+ end;
+
+ for I := 0 to InParamCount-1 do
+ begin
+ InParamValues := NullVariant;
+ InParamTypes := ZDbcIntfs.stUnknown;
+ end;
+end;
+
constructor TZInterbase6CallableStatement.Create(Connection: IZConnection;
const SQL: string; Info: TStrings);
begin
@@ -963,7 +984,6 @@
I: Integer;
Temp: TZVariant;
begin
- SetOutParamCount(Value.GetFieldCount);
for I := 0 to Value.GetFieldCount-1 do
begin
if Value.IsNull(I) then
@@ -998,7 +1018,7 @@
stTimestamp:
DefVarManager.SetAsDateTime(Temp, Value.GetTimestamp(I));
end;
- OutParamValues := Temp;
+ OutParamValues[FOutParamIndexes] := Temp;
end;
end;

@@ -1039,6 +1059,17 @@
{**
Function remove stUnknown paramters from InParamTypes and InParamValues
}
+procedure TZInterbase6CallableStatement.RegisterOutParameter(
+ ParameterIndex, SQLType: Integer);
+var
+ k: integer;
+begin
+ inherited;
+ k := Length(FOutParamIndexes);
+ SetLength(FOutParamIndexes,k+1);
+ FOutParamIndexes[k] := ParameterIndex-1;
+end;
+
procedure TZInterbase6CallableStatement.TrimInParameters;
var
I: integer;
@@ -1067,5 +1098,3 @@
end;

end.
-
-
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited