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

[Conrad]

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.Length)

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

[Pashers]

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=;PWD=;DATABASE=pubs"

Set rs = cn.Execute("SELECT logo FROM pub_info WHERE pub_id='0736'")

Response.BinaryWrite rs("logo")

Response.End

%>

 

Anotherpage.asp

This page will display the image New Moon Books from a SQL Server

image field.

images.asp

 

 

Peter

[Conrad]

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

[Conrad]

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("SPDisableEnableAllTableConstraints", 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)("TableName").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)("TableName").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

[Conrad]

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_Data", SQLCon)

SQLcmd.CommandType = CommandType.StoredProcedure

'###If statements to avoid deleting data from certain tables

If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableName").ToString() <> "Tables") Then

If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableName").ToString() <> "EO_PK_TABLE") Then

SQLcmd.Parameters.Add("@TblName", SqlDbType.Text)

SQLcmd.Parameters("@TblName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableName").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)("TableName").ToString() <> "Tables") Then

If (SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableName").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)("TableName").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)("TableName").ToString() <> "Tables") Then

If (SQLdsTables.Tables(0).Rows(SQLTableRowCount)("TableName").ToString() <> "EO_PK_TABLE") Then

If (SQLdsTables.Tables(0).Rows(SQLTableRowCount)("TableName").ToString() <> "ACTIVE_SESSION") Then

'###Letting the user know what table is being migrated

lblInformationMessages.Text = "Migrating " + SQLdsTables.Tables(0).Rows(SQLTableRowCount)("TableName").ToString() + " FrontBase Data to SQL"

lblInformationMessages.Refresh()

'###Getting the table name

GenericSQLTableName = SQLdsTables.Tables(0).Rows(SQLTableRowCount)("TableName")

'###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

[Conrad]

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)(SPSQLColumnName)

ElseIf (FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnName) Is DBNull.Value) Then

SQLcmd.Parameters.Add(SPSQLColumnParameter, SPSQLType)

SQLcmd.Parameters(SPSQLColumnParameter).Value = FBds.Tables(0).Rows(FBDataRowCount)(SPSQLColumnName)

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)(SPSQLColumnName)

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").ToString

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("SPDisableEnableAllTableConstraints", SQLCon)

SQLcmd.CommandType = CommandType.StoredProcedure

 

SQLcmd.Parameters.Add("@TblName", SqlDbType.Text)

SQLcmd.Parameters("@TblName").Value = SQLdsTables.Tables(0).Rows(SQLRowCounter)("TableName").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)("TableName").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

[Conrad]

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.SequentialAccess)

 

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