'  Copyright GBASE. 2009  All rights reserved.                 */
'                                                                    */
' This sample program is owned by International Business Machines    */
' Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
' and licensed, not sold.                                            */
'                                                                    */
' You may copy, modify, and distribute Me sample program in any    */
' form without payment to IBM,  for any purpose including developing,*/
' using, marketing or distributing programs that include or are      */
' derivative works of the sample program. Licenses to IBM patents    */
' are expressly excluded from Me license.                          */
' The sample program is provided to you on an "AS IS" basis, without */
' warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
' WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
' THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
' ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
' limitation of implied warranties, so the above limitations or      */
' exclusions may not apply to you.  IBM shall not be liable for any  */
' damages you suffer as a result of using, modifying or distributing */
' the sample program or its derivatives.                             */
'                                                                    */
' Each copy of any portion of Me sample program or any derivative  */
' work,  must include the above copyright notice and disclaimer of   */
' warranty.                                                          */
'                                                                    */


Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Imports System.Data
Imports GBS.Data.GBasedbt
Imports System.Xml

Namespace StoredProc_INOUT
    Class Program

#Region "Data members"
        Private _connection As IfxConnection
        Private _outStream As StreamWriter
        Private _command As IfxCommand
#End Region

#Region "Internal Functions"
        '/ <summary>
        '/ Constructor
        '/ </summary>
        Public Sub New()
            _connection = Nothing
            _outStream = New StreamWriter(Console.OpenStandardOutput())
            _outStream.AutoFlush = True
        End Sub

        '/ <summary>
        '/ Property to set the output stream for the application
        '/ </summary>
        Public Property OutStream() As StreamWriter
            Get
                Return _outStream
            End Get
            Set(ByVal Value As StreamWriter)
                _outStream = value
            End Set
        End Property

        '/ <summary>
        '/ Establishes connection with the server
        '/ </summary>
        '/ <param name="connectionString"></param>
        '/ <returns>bool</returns>
        Public Function EstablishConnection(ByVal connectionString As String) As Boolean
            Try
                _connection = New IfxConnection(connectionString)
                _connection.Open()
                _command = New IfxCommand()
                _command.Connection = _connection
            Catch excep As Exception
                WriteLine(String.Format("Connection Error: {0}", excep.Message))
                Return False
            End Try

            Return True
        End Function

        '/ <summary>
        '/ Clean up
        '/ </summary>
        Public Sub CleanUp()
            If Not _connection Is Nothing Then
                _connection.Close()
            End If

            _outStream.Close()
        End Sub

        '/ <summary>
        '/ Write status messages to file/console
        '/ </summary>
        '/ <param name="logMessage"></param>
        Public Sub WriteLine(ByVal logMessage As String)
            _outStream.WriteLine(logMessage)
        End Sub
#End Region

#Region "Stored Procedure Create/Execute/Drop"
        '/ <summary>
        '/ Delete existing procedure with the same name if present
        '/ </summary>
        Private Sub DeleteProcedures()
            Try
                _command.CommandText = "drop procedure multiplyvalues"
                _command.ExecuteNonQuery()

            Catch excep As Exception
                WriteLine(String.Format("The stored procedures do not exist.: {0}", excep.Message))
            End Try
        End Sub

        '/ <summary>
        '/ Create a procedure with INOUT and OUT params
        '/ </summary>
        '/ <returns>bool</returns>
        Public Function CreateStoredProcedures() As Boolean
            Try
                _command.CommandText = "create procedure multiplyvalues(INOUT value1 int, INOUT value2 int, OUT outbigintparam bigint) " & _
                " returning int; " & _
                " define rvar1 int; " & _
                " let rvar1 = value1 * value2; " & _
                "  let value1 = 100; " & _
                "  let value2 = 200; " & _
                "  let outbigintparam = 1234567890; " & _
                "  return rvar1; " & _
                "end procedure; "

                _command.ExecuteNonQuery()

            Catch excep As Exception
                WriteLine(String.Format("The stored procedures cannot be created: {0}", excep.Message))
                Return False
            End Try
            Return True
        End Function

        '/ <summary>
        '/ Execute the procedure.
        '/ </summary>
        '/ <returns>bool</returns>
        Public Function ExecuteStoredProcedures() As Boolean
            Dim param1 As IfxParameter, param2 As IfxParameter, param3 As IfxParameter, param4 As IfxParameter

            _command.CommandText = "{? = call multiplyvalues(?,?,?)};"
            param4 = New IfxParameter("retValparam", IfxType.Integer)
            param1 = New IfxParameter("intparam1", IfxType.Integer)
            param2 = New IfxParameter("intparam2", IfxType.Integer)
            param3 = New IfxParameter("bigintparam", IfxType.BigInt)

            Dim paramX As IfxParameter = _command.Parameters.Add("@retValparam", IfxType.Integer)
            paramX.Direction = ParameterDirection.ReturnValue
            paramX = _command.Parameters.Add("@intparam1", IfxType.Integer)
            paramX.Direction = ParameterDirection.InputOutput
            paramX.Value = 5
            paramX = _command.Parameters.Add("@intparam2", IfxType.Integer)
            paramX.Direction = ParameterDirection.InputOutput
            paramX.Value = 6
            paramX = _command.Parameters.Add("@bigintparam", IfxType.BigInt)
            paramX.Direction = ParameterDirection.Output

            WriteLine("***** Input values before executing the procedure *****")
            WriteLine(String.Format("INOUT Param Value1: {0}", _command.Parameters("@intparam1").Value))
            WriteLine(String.Format("INOUT Param Value2: {0}", _command.Parameters("@intparam2").Value))

            Try
                _command.ExecuteNonQuery()
            Catch excep As Exception
                WriteLine(String.Format("Error executing procedure: {0}", excep.Message))
                Return False
            End Try

            WriteLine("***** Results after execution of the procedure *****")
            WriteLine(String.Format("Return value: {0}", _command.Parameters("@retValparam").Value))
            WriteLine(String.Format("INOUT Param Value1: {0}", _command.Parameters("@intparam1").Value))
            WriteLine(String.Format("INOUT Param Value2: {0}", _command.Parameters("@intparam2").Value))
            WriteLine(String.Format("OUT bigint param: {0}", _command.Parameters("@bigintparam").Value))
            Return True
        End Function
#End Region


        '/ <summary>
        '/ The demo app needs to be invoked as  EXE -conn "connection string" -log "log filename"
        '/ Eg: StoredProc.exe //This will pick the connection string from ConnInfo.xml
        '/ OR
        '/ StoredProc.exe -conn "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;"
        '/ OR
        '/ StoredProc.exe -conn "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;" -log log.txt
        '/ </summary>
        '/ <param name="args"></param>
        Shared Sub Main(ByVal args() As String)
            Dim bResult As Boolean = False
            Dim connString As String = String.Empty
            Dim logFile As String = String.Empty

            Dim prg As Program = New Program()

            If args.Length = 0 Then
                Try
                    Dim settings As XmlReaderSettings = New XmlReaderSettings()
                    settings.ConformanceLevel = ConformanceLevel.Auto
                    Dim reader As XmlReader = XmlReader.Create("ConnInfo.xml", settings)

                    Try
                        connString = reader.ReadElementString("ConnectionString")
                        logFile = reader.ReadElementString("LogFile")
                    Catch
                        'Ignore this error
                    End Try

                Catch excep As System.Exception
                    Console.WriteLine(excep.Message)
                    Console.WriteLine("Problem reading ConnInfo.xml file, either use")
                    Console.WriteLine("StoredProc.exe //This will pick the connection string from ConnInfo.xml")
                    Console.WriteLine("StoredProc.exe -conn" & "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;" & "OR")
                    Console.WriteLine("StoredProc.exe -conn" & "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;" & "-log log.txt")
                End Try
            End If

            If args.Length > 1 Then
                connString = args(1)
            End If

            If args.Length = 4 Then
                logFile = args(3)
            End If

            If logFile.Length <> 0 Then
                prg.OutStream = New StreamWriter(logFile, False)
            End If

            If connString.Length <> 0 Then
                bResult = prg.EstablishConnection(connString)
            Else
                prg.WriteLine("*****    Connection Failed!!!     *****")
            End If

            If bResult = True Then
                prg.WriteLine("*****    Connection successful     *****")
                bResult = prg.CreateStoredProcedures()
            End If

            If bResult = True Then
                prg.WriteLine("*****    Stored Procedure creation successful     *****")
                bResult = prg.ExecuteStoredProcedures()
            End If

            prg.DeleteProcedures()

            If bResult = False Then
                prg.WriteLine("There is an ERROR in the execution!")
            Else
                prg.WriteLine("*****    Demo app for Stored Procedure with INOUT params has successfully executed!  *****")
            End If

            If logFile.Length <> 0 Then
                Console.WriteLine("Execution Complete!")
                Console.WriteLine("Refer to results in the log file: {0}", logFile)
            End If

            prg.CleanUp()

        End Sub
    End Class
End Namespace
