+ Post New Thread
Results 1 to 7 of 7
Coding Thread, Converting a BLOB from a Frontbase DB to MSSQL 2008 Express image field in Coding and Web Development; Hi Folks, Hope some can help I am trying to get a blob file out of a frontbase database and ...
  1. #1

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11

    Converting a BLOB from a Frontbase DB to MSSQL 2008 Express image field

    Hi Folks,

    Hope some can help

    I am trying to get a blob file out of a frontbase database and from all my reading it seems I need to read it out as a byte array, then convert that into an image as i want to store it in an SQL express image field.



    what I have so far is

    'Frontbase Database Connection
    FBDBConString = "Provider=MSDASQL;" & _
    "Driver={FrontBase ODBC};" & _
    "Server=" + FBHost + ";" & _
    "Database=" + FBPort + ";" & _
    "Uid=" + FBUSRName + ";" & _
    "Pwd=" + FBPassword + ""

    ' CODE TO GET IMAGE OUT OF FRONTBASE DATABASE
    Try

    FBDBSQLString = "SELECT * FROM TICKET_ATTACHMENT WHERE ID = 1680"

    MsgBox(FBDBSQLString)

    'DB con code
    FBCon = New Odbc.OdbcConnection(FBDBConString)
    FBCon.Open()
    MsgBox("We have connectivity to Frontbase")
    FBda = New Odbc.OdbcDataAdapter(FBDBSQLString, FBCon)
    FBds = New Data.DataSet
    FBda.Fill(FBds)

    'In my dataset FBds the system is returing some text string colums then the FILE_DATA COLUM with a string saying "system.byte[]" which I assume means a system byte array with all the data.

    Based on that this is my code:

    MS = New IO.MemoryStream(FBds.Tables(0).Rows(0)("FILE_DATA" ), 0, FBds.Tables(0).Rows(0)("FILE_DATA").ToString.Lengt h)
    MS.Position = 0


    myimage = BS.Deserialize(MS)


    'myimage is the image file I want to pass back to my SQL stored procedure to put in the SQL db


    Catch ex As Exception
    MsgBox(ex.Message)
    FBCon.Close()
    Finally
    FBCon.Close()
    End Try



    but it is throwing the "End of Stream encountered before parsing was completed."



    Any pointers or suggestions appreicated



    Thanks in Advance

    Conrad

  2. #2

    Join Date
    May 2008
    Location
    Cheshire
    Posts
    296
    Thank Post
    48
    Thanked 27 Times in 24 Posts
    Rep Power
    19
    Hi there,

    I remember doing something similar to this in PHP. What you'll need to do is create another page for the blob to jpeg (in this example gif). So for example you would have images.aspx. Then you would call the images.aspx. From the Microsoft article (How To Display Images Stored in a BLOB Field) it would look like this:

    images.asp
    <%@ LANGUAGE="VBSCRIPT" %>
    <%
    ' Clear out the existing HTTP header information
    Response.Expires = 0
    Response.Buffer = TRUE
    Response.Clear

    ' Change the HTTP header to reflect that an image is being passed.
    Response.ContentType = "image/gif"

    Set cn = Server.CreateObject("ADODB.Connection")
    ' The following open line assumes you have set up a System DataSource
    ' by the name of myDSN.
    'Remember to change the following connection string parameters to reflect the correct values
    'for your SQL server.
    cn.Open "DSN=myDSN;UID=<username>;PWD=<strong password>;DATABASE=pubs"
    Set rs = cn.Execute("SELECT logo FROM pub_info WHERE pub_id='0736'")
    Response.BinaryWrite rs("logo")
    Response.End
    %>

    Anotherpage.asp
    <HTML>
    <HEAD><TITLE>Display Image</TITLE></HEAD>
    <BODY>
    This page will display the image New Moon Books from a SQL Server
    image field.<BR>
    <IMG SRC="images.asp">
    </BODY>
    </HTML>


    Peter

  3. #3

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11
    Hi Peter,

    Thanks for the reply, after a lot of searching and trial and error over the last week I got a working solution please see the code below:

    In the end I had to write the files to physical items on the local machine and then read them back in.

    Imports System.Data.OleDb
    Imports System.Data
    Imports system.Data.SqlClient
    Imports System.IO

    Public Class frmFrontBasetoSQLConvertor
    'Field Variables

    Dim FBHost As String
    Dim SQLHost As String
    Dim FBPort As String
    Dim SQLPort As String
    Dim FBDBName As String
    Dim SQLDBName As String
    Dim FBUSRName As String
    Dim SQLUSRName As String
    Dim FBPassword As String
    Dim SQLPassword As String

    'FB Database Variables
    Dim FBDBConString As String
    Dim FBDBSQLString As String
    Dim FBCon As Odbc.OdbcConnection
    Dim FBcmd As Odbc.OdbcCommand
    Dim FBda As Odbc.OdbcDataAdapter
    Dim FBds As Data.DataSet
    Dim FBDataRowCount As Integer

    'SQL DB Variable
    Dim SQLDBConString As String
    Dim SQLDBSQLString As String
    Dim SQLCon As New SqlConnection
    Dim SQLcmd As SqlCommand = SQLCon.CreateCommand()
    Dim SQLda As SqlDataAdapter
    Dim SQLds As Data.DataSet
    Dim SQLRowCounter As Integer
    Dim SQLdsTables As Data.DataSet
    Dim SQLdsSelecttableData As Data.DataSet
    Dim GenericSQLStringCheckbit As Integer
    Dim GenericSQLTableName As String
    Dim GenericFBTableRows As Integer
    Dim SelectSQLTablesdataString As String

    'SQL stored procedure varible names
    Dim SPNameString As String
    Dim SPSQLColumnName As String
    Dim SPSQLType As SqlDbType
    Dim SQLTableRowCount As Integer
    Dim SQLColumParamCount As Integer
    Dim SPSQLColumnParameter As String


    More code in post below

  4. #4

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11
    The code following on

    Private Sub frmFrontBasetoSQLConvertor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    '###Setting the combo boxes pre-selected text
    cbxFBVendor.SelectedText = "FrontBase"
    cbxSQLVendor.SelectedText = "SQL 2005 +"
    '###disabling the Frontbase DB Name field as the database uses the port number instead
    tbxFBDBName.Enabled = False
    '### Enabling the Frontbase DB Name field if Frontbase is not selected
    If (cbxFBVendor.SelectedText <> "FrontBase" = True) Then
    tbxFBDBName.Enabled = False
    End If

    End Sub

    Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Click

    '###Getting string connection values from Textboxes on form
    FBHost = tbxFBHost.Text.Trim
    SQLHost = tbxSQLHost.Text.Trim
    FBPort = tbxFBPort.Text.Trim
    SQLPort = tbxSQLPort.Text.Trim
    FBDBName = tbxFBDBName.Text.Trim
    SQLDBName = tbxSQLDBName.Text.Trim
    FBUSRName = tbxFBUSRName.Text.Trim
    SQLUSRName = tbxSQLUSRName.Text.Trim
    FBPassword = tbxFBPW.Text.Trim
    SQLPassword = tbxSQLPW.Text.Trim


    '###FrontBase odbc database connection string
    'FBDBConString = "Provider=MSDASQL;" & _
    '"Driver={ODBC Driver Name as per ODBC data };" & _
    ' "Server=(Servername or IP address also refered to as host;" & _
    '"Database= (Databae name or in Frontbase databases case it port;" & _
    '"Uid=(DataBase User ID);" & _
    '"Pwd=(DataBase Password)"

    '###Frontbase ODBC Database Connection string
    FBDBConString = "Provider=MSDASQL;" & _
    "Driver={FrontBase ODBC};" & _
    "Server=" + FBHost + ";" & _
    "Database=" + FBPort + ";" & _
    "Uid=" + FBUSRName + ";" & _
    "Pwd=" + FBPassword + ""


    '### Information message to users using label field on form
    lblInformationMessages.Text = "Testing Database connection strings"
    lblInformationMessages.Refresh()

    '###Testing the FrontBase database connection string
    '### Putting code in Try catch block to deal with errors
    Try
    '### Select statment to test connectivity
    GenericSQLTableName = "ALERT"
    FBDBSQLString = "SELECT * FROM " + GenericSQLTableName

    '# Below is a usefull Frontbase Database string for returning data about all the tables in the DB and general info
    '# FBDBSQLString = "select ""table_name"", lower_limit, upper_limit, percent, persistent, preload from information_schema.schemata as t0, (information_schema.tables left join information_schema.table_cache_settings using(schema_pk, table_pk)) as t1 where t0.schema_pk = t1.schema_pk and ""schema_name"" = 'WHD' and table_type = 'BASE TABLE' order by ""table_name"""

    FBCon = New Odbc.OdbcConnection(FBDBConString) 'Create connection passing in connection string
    FBCon.Open() 'Opening DB connection
    MsgBox("We have connectivity to Frontbase") 'Letting user know we have a conenction
    FBda = New Odbc.OdbcDataAdapter(FBDBSQLString, FBCon) 'Passing Select statment and connection details to Data adapter
    FBds = New Data.DataSet 'Creating a dataset for the select statements returned data
    FBda.Fill(FBds) 'Executing and flling the data set with the data from the data adaptor
    'dgvTables.DataSource = FBds.Tables(0) 'showing the returned data to the user if needed

    '###Catching any errors thown on connection of select statment and informing user
    Catch ex As Exception
    MsgBox(ex.Message)
    FBCon.Close()

    '###Making sure that what ever happend about we close and dispose of the database connection
    Finally
    FBCon.Close()
    FBCon.Dispose()
    End Try


    '###SQL Database connection string
    'SQLDBConString = "Driver={SQLServer};" & _
    '"Server= (Server Name or IP address (sometime called host));" & _
    '"Database=(Database Name);" & _
    '"Uid= (Database Username);" & _
    '"Pwd= (Database Password);"

    '###FSQL Database Connection string
    SQLDBConString = "Server=" + SQLHost + ";" & _
    "Database=" + SQLDBName + ";" & _
    "Uid=" + SQLUSRName + ";" & _
    "Pwd=" + SQLPassword + ";"

    '### Executing SQL code to setup systems before moving data
    '### Putting code in Try catch block to deal with errors
    Try

    '# Below is a useful SQL Database string for returning data about all the tables in the DB and other info
    'SQLDBSQLString = "SELECT tbl.Name, Coalesce( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount] FROM sys.tables AS tbl INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E') order by Name Asc"

    SQLCon = New SqlConnection(SQLDBConString) 'Creating the SQL connection passing in the connection string
    SQLCon.Open() 'Opening the SQL connection
    MsgBox("We have connectivity to SQL") 'Letting user know we have a connection
    SQLcmd = New SqlClient.SqlCommand("SP_Tables_Insert", SQLCon) 'Setting Stored Procedure name and connection details for the execution
    SQLcmd.CommandType = CommandType.StoredProcedure 'Setting the command type as a Stored Procedure
    SQLda = New SqlDataAdapter() ' Creating a data adapter
    SQLdsTables = New Data.DataSet 'Creating a dataset for the data
    SQLda.SelectCommand = SQLcmd 'Executing the stored proceedure
    SQLda.Fill(SQLdsTables) 'Filling the dataset with data adapters returned data
    dgvTables.DataSource = SQLdsTables.Tables(0) 'displaying the data to the user


    '### Execute Stored Proceedure to disable Table Constraints
    '###Letting the user know what is going on
    lblInformationMessages.Text = "Disabling Table Constraints and setting the constraints flag"
    lblInformationMessages.Refresh()
    '###Setting the progress bar to start at 0
    PGB.Minimum = 0
    'PGB.Refresh()
    '###Setting the progress bars maximum size
    PGB.Maximum() = SQLdsTables.Tables(0).Rows.Count - 1
    '###Creating a for loop to go through each table in the "Tables" table and disable all SQL table constraints
    For SQLRowCounter = 0 To SQLdsTables.Tables(0).Rows.Count - 1
    '###Incrementing the Progress bar by 1 for each loop
    PGB.Increment(1)
    '###Setting the SP command and command type
    SQLcmd = New SqlClient.SqlCommand("SPDisableEnableAllTableConst raints", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    '###Setting and passing in SP Parameters
    SQLcmd.Parameters.Add("@TblName", SqlDbType.Text)
    SQLcmd.Parameters("@TblName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()
    SQLcmd.Parameters.Add("@IsCheck", SqlDbType.Bit)
    SQLcmd.Parameters("@IsCheck").Value = 0
    '###Executing the stored procedure
    SQLcmd.ExecuteNonQuery()

    'Second stored procedure in same loop to update the "Table Constrains flag" in the "Tables" table
    '###Setting and passing in SP Parameters
    SQLcmd = New SqlClient.SqlCommand("SP_Tables_Contraints_Update" , SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    '###Setting and passing in SP Parameters
    SQLcmd.Parameters.Add("@TableName", SqlDbType.Text)
    SQLcmd.Parameters("@TableName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()
    SQLcmd.Parameters.Add("@contraints_Off", SqlDbType.Char)
    SQLcmd.Parameters("@contraints_Off").Value = "Y"
    '###Executing the stored procedure
    SQLcmd.ExecuteNonQuery()

    '###Getting the next row from the table
    Next SQLRowCounter

    DatagridTablesview()

    '###Resetting the Progress bar
    PGB.Minimum = 0
    PGB.Maximum = 0
    PGB.Refresh()


    More code in post below

  5. #5

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11
    The code following on

    '### Execute Stored Proceedure to delete existing Table data if any
    '###Letting the user know what is going on
    lblInformationMessages.Text = "Deleting existing data from new SQL tables"
    lblInformationMessages.Refresh()
    '###Setting the progress bars maximum size
    PGB.Maximum() = SQLdsTables.Tables(0).Rows.Count - 1
    '###Creating a for loop to go through each table in the "Tables" table and Delete all table data
    For SQLRowCounter = 0 To SQLdsTables.Tables(0).Rows.Count - 1
    '###Incrementing the Progress bar by 1 for each loop
    PGB.Increment(1)
    '###Setting the SP command and command type
    SQLcmd = New SqlClient.SqlCommand("SP_Delete_Existing_Table_Dat a", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    '###If statements to avoid deleting data from certain tables
    If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableN ame").ToString() <> "Tables") Then
    If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableN ame").ToString() <> "EO_PK_TABLE") Then
    SQLcmd.Parameters.Add("@TblName", SqlDbType.Text)
    SQLcmd.Parameters("@TblName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()
    '###Executing the stored procedure
    SQLcmd.ExecuteNonQuery()
    End If
    End If

    'Second stored procedure in same loop to update the "Table Status" in the "Tables" table
    '###Setting and passing in SP Parameters
    SQLcmd = New SqlClient.SqlCommand("SP_Tables_Status_Update", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    '###If statements to avoid updating data from certain tables
    If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableN ame").ToString() <> "Tables") Then
    If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableN ame").ToString() <> "EO_PK_TABLE") Then
    '###Setting and passing in SP Parameters
    SQLcmd.Parameters.Add("@TableName", SqlDbType.Text)
    SQLcmd.Parameters("@TableName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()
    SQLcmd.Parameters.Add("@UpdateStatus", SqlDbType.NVarChar)
    SQLcmd.Parameters("@UpdateStatus").Value = "Table Data Deleted"
    '###Executing the stored procedure
    SQLcmd.ExecuteNonQuery()
    End If
    End If


    '###Getting the next row from the table
    Next SQLRowCounter

    DatagridTablesview()

    '###Resetting the Progress bar
    PGB.Minimum = 0
    PGB.Maximum = 0
    PGB.Refresh()

    '###Catching any errors thown on connection of select statment and informing user
    Catch ex As Exception
    MsgBox(ex.Message)
    SQLCon.Close()
    '###Making sure that what ever happend about we close and dispose of the database connection
    Finally
    SQLCon.Close()
    SQLCon.Dispose()
    End Try
    '###Creating directory to store database attachments for loading back up from Frontbase blob type to SQL Image type
    Try
    'Creating a directory by specifying it like this or passing in a textbox path
    Directory.CreateDirectory("C:\FrontbaseImages")
    Catch ex As Exception
    MsgBox(ex.Message)
    End Try
    MsgBox("Directory C:\FrontbaseImages was created successfully")

    '###Letting the user know we are ready to start conversion of data
    lblInformationMessages.Text = "Ready to Convert WHD data to SQL"
    lblInformationMessages.Refresh()


    End Sub

    Public Sub Tableloop()

    '### This loops through the SQL Tables Dataset and selects each table for
    'insterting of data when needed
    For SQLTableRowCount = 0 To SQLdsTables.Tables(0).Rows.Count - 1
    '###If statements to avoid updating data from certain tables
    If (SQLdsTables.Tables(0).Rows(SQLTableRowCount)("Tab leName").ToString() <> "Tables") Then
    If (SQLdsTables.Tables(0).Rows(SQLTableRowCount)("Tab leName").ToString() <> "EO_PK_TABLE") Then
    If (SQLdsTables.Tables(0).Rows(SQLTableRowCount)("Tab leName").ToString() <> "ACTIVE_SESSION") Then
    '###Letting the user know what table is being migrated
    lblInformationMessages.Text = "Migrating " + SQLdsTables.Tables(0).Rows(SQLTableRowCount)("Tabl eName").ToString() + " FrontBase Data to SQL"
    lblInformationMessages.Refresh()
    '###Getting the table name
    GenericSQLTableName = SQLdsTables.Tables(0).Rows(SQLTableRowCount)("Tabl eName")
    '###Passing the table name to the dataloop sub
    dataloop(GenericSQLTableName)
    End If
    End If
    End If

    '###Getting the next row from the table
    Next SQLTableRowCount


    End Sub

    Public Sub dataloop(ByVal GenericSQLTableName As String)
    '###This sub gets all the FrontBase data for the current table from
    'table passed to it from the dataloop sub

    '###Select statment to get all the data
    FBDBSQLString = "SELECT * FROM " + GenericSQLTableName
    '###Clearing the data set to ensure not old data is in it
    FBds.Reset()

    '###Opening the Front Base database connection getting the data
    'and filling the dataset and then closeing the connection
    FBCon = New Odbc.OdbcConnection(FBDBConString)
    FBCon.Open()
    FBda = New Odbc.OdbcDataAdapter(FBDBSQLString, FBCon)
    FBda.Fill(FBds)

    FBCon.Close()


    '###This gets a list of the columns and their data types for
    ' the passed in table
    '###We connect to SQL execute the statement and fill the dataset
    SQLCon = New SqlConnection(SQLDBConString)
    SQLCon.Open()
    SQLcmd = New SqlClient.SqlCommand("SP_SelectTableDatabytable", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    SQLcmd.Parameters.Add("@ActiveTableName", SqlDbType.NVarChar)
    SQLcmd.Parameters("@ActiveTableName").Value = GenericSQLTableName
    SQLda = New SqlDataAdapter()
    SQLdsSelecttableData = New Data.DataSet
    SQLda.SelectCommand = SQLcmd
    SQLda.Fill(SQLdsSelecttableData)
    dgvTables.DataSource = SQLdsSelecttableData.Tables(0)
    SQLCon.Close()

    'We now loop through each row in the dataset calling the SP sub
    FBDataRowCount = 0
    PGB.Minimum = 0
    PGB.Maximum = (FBds.Tables(0).Rows.Count)

    '###This for loop goes through each row in the dataset and calls the BuildSPLoop sub
    For FBDataRowCount = 0 To FBds.Tables(0).Rows.Count - 1
    PGB.Increment(1)
    BuildSPLoop(FBDataRowCount, GenericSQLTableName)

    Next FBDataRowCount
    SQLdsSelecttableData.Dispose()

    End Sub
    More code in post below

  6. #6

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11
    The code following on

    Public Sub BuildSPLoop(ByVal FBDataRowCount As Integer, ByVal GenericSQLTableName As String)

    '###This sub assigns the data type and value for each colum
    'and then builds up a stored proceedure for each row in the
    'data table to insert the data from FrontBase into SQL
    SQLCon = New SqlConnection(SQLDBConString)
    SQLCon.Open()

    'Code to generate insert String for SPs
    SPNameString = "SPUpdate" + GenericSQLTableName
    'MsgBox(SPNameString)
    SQLcmd = New SqlClient.SqlCommand(SPNameString, SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure


    For SQLRowCounter = 0 To SQLdsSelecttableData.Tables(0).Rows.Count - 1

    SPSQLColumnParameter = "@" + SQLdsSelecttableData.Tables(0).Rows(SQLRowCounter) ("ColName")
    SPSQLType = SQLdsSelecttableData.Tables(0).Rows(SQLRowCounter) ("TypeID")
    SPSQLColumnName = SQLdsSelecttableData.Tables(0).Rows(SQLRowCounter) ("ColName")
    Dim Unique_ID As Integer = 0
    Dim ImageName As String
    '###To move the blob data from Frontbase to SQL I have to convert
    'the blobs into files. Store them on the local machine and
    'then re-import them back into SQL
    '###So in the if statment below I am checking if the field is of type
    'image and if so I am calling a seperate proceedure to do the export of the images
    'the if statement contains some else if statments to deal with some variations
    ' in this scenario
    If (SPSQLType <> SqlDbType.Image) Then
    SQLcmd.Parameters.Add(SPSQLColumnParameter, SPSQLType)
    SQLcmd.Parameters(SPSQLColumnParameter).Value = FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnNam e)
    ElseIf (FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnNa me) Is DBNull.Value) Then
    SQLcmd.Parameters.Add(SPSQLColumnParameter, SPSQLType)
    SQLcmd.Parameters(SPSQLColumnParameter).Value = FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnNam e)
    ElseIf (GenericSQLTableName <> "TICKET_ATTACHMENT" And GenericSQLTableName <> "GENERIC_ATTACHMENT" And GenericSQLTableName <> "PO_ATTACHMENT" And GenericSQLTableName <> "FAQ_ATTACHMENT") Then 'And GenericSQLTableName <> "CUSTOM_LOGO"
    SQLcmd.Parameters.Add(SPSQLColumnParameter, SPSQLType)
    SQLcmd.Parameters(SPSQLColumnParameter).Value = FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnNam e)
    Else
    If (GenericSQLTableName <> "TECH") Then
    Unique_ID = FBds.Tables(0).Rows(FBDataRowCount)("ID")
    Else
    Unique_ID = FBds.Tables(0).Rows(FBDataRowCount)("CLIENT_ID")
    End If
    If (GenericSQLTableName = "TICKET_ATTACHMENT" Or GenericSQLTableName = "GENERIC_ATTACHMENT" Or GenericSQLTableName = "PO_ATTACHMENT" Or GenericSQLTableName = "FAQ_ATTACHMENT") Then
    ImageName = FBds.Tables(0).Rows(FBDataRowCount)("FILE_NAME").T oString
    Else
    ImageName = ".txt"
    End If

    '###Calling the getimagebytearray sub and assigning it to a byte array to put in the database
    Dim MyByteArray() As Byte = getimagebytearray(GenericSQLTableName, SPSQLColumnName, Unique_ID, ImageName)
    Dim MyByteArraySize As Integer = MyByteArray.Length()

    SQLcmd.Parameters.Add(SPSQLColumnParameter, SqlDbType.Image, MyByteArraySize)
    SQLcmd.Parameters(SPSQLColumnParameter).Value = MyByteArray
    End If

    Next SQLRowCounter

    SQLcmd.ExecuteNonQuery()
    SQLCon.Close()

    End Sub


    Public Sub EOPKTABLEUPDATE()

    '###This sub updates the EO_PK_Table using update statements as
    'this table does not exist in the Frontbase database
    lblInformationMessages.Text = "Updating the EO_PK_TABLE"
    lblInformationMessages.Refresh()
    SQLCon = New SqlConnection(SQLDBConString)
    SQLCon.Open()

    SQLcmd = New SqlClient.SqlCommand("SP_Update_EO_PK_TABLE", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure
    SQLcmd.ExecuteNonQuery()
    SQLCon.Close()
    End Sub

    Private Sub btnTestConnections_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestConnections.Click

    '###This sub is calling the other subs to do the data migration
    MsgBox("Starting Conversion")
    Tableloop()
    EOPKTABLEUPDATE()
    PGB.Minimum = 0
    PGB.Maximum = 0
    'PGB.Refresh()
    PGB.Maximum() = SQLdsTables.Tables(0).Rows.Count - 1
    'enabling table constriants
    lblInformationMessages.Text = "Enabling the table constraints"
    lblInformationMessages.Refresh()

    SQLCon = New SqlConnection(SQLDBConString)
    SQLCon.Open()
    ' MsgBox("We have connectivity to SQL")

    For SQLRowCounter = 0 To SQLdsTables.Tables(0).Rows.Count - 1
    PGB.Increment(1)
    SQLcmd = New SqlClient.SqlCommand("SPDisableEnableAllTableConst raints", SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure

    SQLcmd.Parameters.Add("@TblName", SqlDbType.Text)
    SQLcmd.Parameters("@TblName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()

    SQLcmd.Parameters.Add("@IsCheck", SqlDbType.Bit)
    SQLcmd.Parameters("@IsCheck").Value = 1

    SQLcmd.ExecuteNonQuery()

    SQLcmd = New SqlClient.SqlCommand("SP_Tables_Contraints_Update" , SQLCon)
    SQLcmd.CommandType = CommandType.StoredProcedure

    SQLcmd.Parameters.Add("@TableName", SqlDbType.Text)
    SQLcmd.Parameters("@TableName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableNa me").ToString()

    SQLcmd.Parameters.Add("@contraints_Off", SqlDbType.Char)
    SQLcmd.Parameters("@contraints_Off").Value = "N"

    SQLcmd.ExecuteNonQuery()

    Next SQLRowCounter
    PGB.Refresh()
    SQLCon.Close()
    MsgBox("DATA MIGRATION COMPLETE")
    lblInformationMessages.Text = "DATA MIGRATION COMPLETE"
    lblInformationMessages.Refresh()
    End Sub
    More code in post below

  7. #7

    Join Date
    Mar 2010
    Posts
    65
    Thank Post
    1
    Thanked 3 Times in 3 Posts
    Rep Power
    11
    Last bit of code
    Public Function getimagebytearray(ByVal TableName As String, ByVal FBImageColName As String, ByVal Unique_ID As Integer, ByVal Imagename As String)

    '###This sub gets called and passed some variables, it then get sthe data
    'out of front base blob file and into a physical file on the machine
    'it then readas that file back into a new byte aray and passes it back
    'for insertion into the SQL database
    Dim IDColumName As String

    '###Setting variable values based on certain conditions
    If (TableName <> "TECH") Then
    IDColumName = "ID"
    Else
    IDColumName = "CLIENT_ID"
    End If

    If (TableName = "TICKET_ATTACHMENT" And Imagename.Contains("=") Or Imagename.Contains("?") Or Imagename.Contains("/")) Then
    Imagename = ".txt"
    End If

    '###Frontbase DB connections
    FBDBConString = "Provider=MSDASQL;" & _
    "Driver={FrontBase ODBC};" & _
    "Server=" + FBHost + ";" & _
    "Database=" + FBPort + ";" & _
    "Uid=" + FBUSRName + ";" & _
    "Pwd=" + FBPassword + ""

    Dim fbcon = New Odbc.OdbcConnection(FBDBConString)

    '###Creating my select statment to get the blob data using my passed in variables
    FBDBSQLString = "SELECT " + FBImageColName + " FROM " + TableName + " WHERE " + IDColumName + " = " + Unique_ID.ToString()

    '###Decalaring all my variables
    Dim fbcommand = New Odbc.OdbcCommand(FBDBSQLString, fbcon)

    Dim fs As IO.FileStream = Nothing ''Writes the BLOB to a file .
    Dim bw As IO.BinaryWriter ''Streams the BLOB to the FileStream object.
    Dim bufferSize As Integer = 3989 '' Size of the BLOB buffer.
    Dim outbyte(bufferSize - 1) As Byte '' The BLOB byte[] buffer to be filled by GetBytes.
    Dim retval As Long ''The bytes returned from GetBytes.
    Dim startIndex As Long = 0 ''The starting position in the BLOB output.

    Dim myreader As Odbc.OdbcDataReader

    '###Open the connection and read data into the DataReader.
    fbcon.Open()
    myreader = fbcommand.ExecuteReader(CommandBehavior.Sequential Access)

    While (myreader.Read())

    '###Create a file to hold the output.

    fs = New IO.FileStream("c:\FrontbaseImages\" + TableName + "-" + Unique_ID.ToString() + "-" + Imagename, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)

    bw = New IO.BinaryWriter(fs)

    '###Reset the starting byte for the new BLOB.
    startIndex = 0

    '###Read the bytes into outbyte[] and retain the number of bytes returned.
    retval = myreader.GetBytes(0, startIndex, outbyte, 0, bufferSize)

    '###Continue reading and writing while there are bytes beyond the size of the buffer.
    While (retval = bufferSize)

    bw.Write(outbyte)
    bw.Flush()

    '###Reposition the start index to the end of the last buffer and fill the buffer.
    startIndex += bufferSize
    retval = myreader.GetBytes(0, startIndex, outbyte, 0, bufferSize)

    End While

    '###Write the remaining buffer.
    bw.Write(outbyte, 0, retval)
    bw.Flush()

    '###Close the output file.
    bw.Close()
    fs.Close()

    End While

    '###Close the reader and the connection.
    myreader.Close()
    fbcon.Close()

    '###Reading the file from the machine back into the byte arrray
    Dim fs2 As IO.FileStream = Nothing
    Try
    '###Reading file
    fs2 = New IO.FileStream("c:\FrontbaseImages\" + TableName + "-" + Unique_ID.ToString() + "-" + Imagename, IO.FileMode.Open)

    '###Finding out the size of the file to be uploaded

    Dim fi As IO.FileInfo = New IO.FileInfo("c:\FrontbaseImages\" + TableName + "-" + Unique_ID.ToString() + "-" + Imagename)
    Dim temp As Long = fi.Length
    Dim lung As Integer = Convert.ToInt32(temp)

    '###Reading the content of the file into an array of bytes.

    Dim picture As Byte() = New Byte(lung - 1) {}
    fs2.Read(picture, 0, lung - 1)
    fs2.Close()


    Return picture
    Catch e As Exception
    Console.WriteLine(e.Message & " - " & e.StackTrace)
    Return -1
    End Try



    End Function
    Public Sub DatagridTablesview()

    '###Refreshing the data grid view to show the current data in the Tables Table

    SQLDBSQLString = "select * from Tables order by TableName Asc"
    SQLda = New SqlDataAdapter(SQLDBSQLString, SQLCon)
    SQLds = New Data.DataSet
    SQLda.Fill(SQLds)
    dgvTables.DataSource = SQLds.Tables(0)
    dgvTables.AutoResizeColumns()


    End Sub


    End Class

SHARE:
+ Post New Thread

Similar Threads

  1. Moodle / Server 2008 / IIS7 / MS SQL Server 2008 Express
    By Kamran7860 in forum Virtual Learning Platforms
    Replies: 3
    Last Post: 23rd November 2010, 06:03 AM
  2. SQL 2008 express
    By Boon72 in forum Windows Server 2008 R2
    Replies: 1
    Last Post: 3rd February 2010, 10:55 AM
  3. SQL Server Express 2008
    By damienharrison in forum Windows Vista
    Replies: 6
    Last Post: 13th March 2009, 11:26 AM
  4. Converting citrix presentation server to VM image
    By Andi in forum Thin Client and Virtual Machines
    Replies: 0
    Last Post: 7th November 2008, 11:33 AM
  5. Search Server 2008 Express
    By cookie_monster in forum Windows
    Replies: 7
    Last Post: 9th March 2008, 11:37 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •