'  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 BLOBOperation
    Class Program
#Region "data members"

        Private _connection As IfxConnection
        Private _outStream As StreamWriter
        Private _command As IfxCommand
        Private _dataReader As IfxDataReader

#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()
            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 "Table Create/Insert/Select/Drop"

        '/ <summary>
        '/ Create a table
        '/ </summary>
        '/ <returns>bool</returns>
        Public Function CreateTable() As Boolean
            Try
                _command = _connection.CreateCommand()
                _command.CommandText = "create table blobtable(id integer, blobdata blob, clobdata clob);"
                _command.ExecuteNonQuery()

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

        '/ <summary>
        '/ Insert data rows into the table
        '/ </summary>
        Private Function InsertTableRows() As Boolean
            Dim bStatus As Boolean = False

            Try
                _command = _connection.CreateCommand()
                _command.CommandText = "insert into blobtable values(1, ?, ?);"

                'Read the bytes from the image file
                Dim imageData() As Byte = System.IO.File.ReadAllBytes("blobTest.bmp")

                Dim param As IfxParameter
                param = New IfxParameter()
                param.IfxType = IfxType.Blob
                param.Value = imageData
                _command.Parameters.Add(param)

                param = New IfxParameter()
                param.IfxType = IfxType.Clob
                param.Value = "Original Clob data"
                _command.Parameters.Add(param)

                WriteLine("Inserting into table with the command: " + _command.CommandText)
                _command.ExecuteNonQuery()

                bStatus = True
            Catch excep As Exception
                WriteLine(String.Format("Insert into table failed : {0}", excep.Message))
                bStatus = False
            End Try

            Return bStatus
        End Function

        '/ <summary>
        '/ Select and display table contents
        '/ </summary>
        '/ <returns>bool</returns>
        Public Function SelectTableRows() As Boolean
            Dim bStatus As Boolean = False
            _command = _connection.CreateCommand()

            Try
                '********** SELECT blob data *******************
                Dim out_data() As Byte = Nothing
                _command.CommandText = "select blobdata from blobtable where id='1'"
                WriteLine("Results after executing the command: " + _command.CommandText)
                _dataReader = _command.ExecuteReader()
                ' Create a buffer 'out_data' to store the BLOB object obtained from the DataReader


                If _dataReader.Read() Then
                    ' Obtain the BLOB object
                    Dim actualSize As Integer = CType(_dataReader.GetBytes(0, 0, Nothing, 0, Int32.MaxValue), Integer)
                    out_data = New Byte(actualSize) {}
                    _dataReader.GetBytes(0, 0, out_data, 0, actualSize)
                End If
                _dataReader.Close()
                'Write the bytes to a file
                System.IO.File.WriteAllBytes("ResultImage.bmp", out_data)
                WriteLine(String.Format("BLOB data read operation successful, verify the file: ResultImage.bmp"))


                '***************** SELECT clob data *************************
                Dim clobVal As IfxClob = Nothing
                Dim clobDataBuff() As Char = New Char(1000) {}
                Dim clobResult As String
                Dim trimChars() As Char = {Chr(0), " "c}

                _command.CommandText = "select clobdata from blobtable where id='1'"
                WriteLine("Results after executing the command: " + _command.CommandText)
                _dataReader = _command.ExecuteReader()
                While _dataReader.Read()
                    If Not _dataReader.IsDBNull(0) Then
                        clobVal = _dataReader.GetIfxClob(0)
                    End If
                End While
                clobVal.Open(IfxSmartLOBOpenMode.ReadOnly)
                clobVal.Read(clobDataBuff)
                clobResult = New String(clobDataBuff).Trim(trimChars)
                clobVal.Close()
                _dataReader.Close()
                WriteLine(String.Format("CLOB data read operation successful, the data value is: {0}", clobResult))

                bStatus = True
            Catch excep As Exception
                WriteLine(String.Format("Error executing the select command: {0}", excep.Message))
                bStatus = False
            End Try

            Return bStatus
        End Function


        '/ <summary>
        '/ Delete existing table with the same name if present
        '/ </summary>
        Private Sub DropTable()
            Try
                _command = _connection.CreateCommand()
                _command.CommandText = "drop table blobtable"
                _command.ExecuteNonQuery()

            Catch excep As Exception
                WriteLine(String.Format("The particular table cannot be dropped. : {0}", excep.Message))
            End Try
        End Sub

#End Region

        '/ <summary>
        '/ The demo app needs to be invoked as  EXE -conn "connection string" -log "log filename"
        '/ Eg: BLOBOperation.exe //This will pick the connection string from ConnInfo.xml
        '/ OR
        '/ BLOBOperation.exe -conn "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;"
        '/ OR
        '/ BLOBOperation.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("BlobOperation.exe //This will pick the connection string from ConnInfo.xml")
                    Console.WriteLine("BlobOperation.exe -conn" & "DataBase=perf;Server=ol_ids_1150_1;User ID=gbasedbt;Password=gbasedbt123;" & "OR")
                    Console.WriteLine("BlobOperation.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.CreateTable()
            End If

            If bResult = True Then
                prg.WriteLine("*****    Table creation successful     *****")
                bResult = prg.InsertTableRows()
            End If

            If bResult = True Then
                prg.WriteLine("*****    Insert into table successful     *****")
                bResult = prg.SelectTableRows()
            End If

            prg.DropTable()

            If bResult = False Then
                prg.WriteLine("There is an ERROR in the execution!")
            Else
                prg.WriteLine("*****    Table operation demo  has successfully executed!  *****")
            End If

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

            prg.CleanUp()

        End Sub
    End Class

End Namespace

