Objective: read BLOB field from a Paradox database with VB.NET.
What I am able to do so far:
Connect to the database and change (non-BLOB) values. Great!*
Read entire tables (including BLOB fields) into a DataGrid (via OledbDataAdapter.Fill into DataTable used as ItemsSource) but... the blob fields come with lots of junk characters.
*My connection string is referencing a User DSN (named ABADB in code below) through my ODBC Data Source Adnimistrator in Windows 10. So far, I seem to be too dumb to make a connection string that doesn't depend on a DSN. Any tips here are welcome, too.
What doesn't seem to work:
- Using the CONVERT SQL command (this errors: [Microsoft][ODBC Paradox Driver] Syntax error (missing operator) in query expression 'CONVERT (Description USING utf8)'.)
FileStream.Write stream from OledbDataReader.GetBytes (This works on SOME blob fields but not all and includes junk characters that are different than those displayed into the DataGrid. Baffling why some only some BLOB fields are interpreted correctly.)
Debug.Print byte encoded with System.Text.Encoding (gives junk characters for all BLOB values - despite the using the same byte stream as used for FileStream.Write which occasionally gives readable values)
Debug.Print the BLOB value directly from the DataGrid (Oddly, I get just the character G - note that the DataGrid itself DOES read the BLOB field correctly and adds junk characters before and after)
Debug.Print BLOB value directly from DataTable used as ItemsSource for DataGrid (Same issue as above - it just reads out G)
What Next? I'm experienced with VBA in Excel/Office but unexperienced in VB.NET so I may be doing something stupid with my code below. I've attempted OledbDataReader.GetChars but haven't been successful yet. I've also looked briefly at other libraries/tools to use.
Please any wisdom is welcome!
Photo of successfuly populated DataGrid (with extra junk chars) and Code Snippets:
Attempting SQL CONVERT:
Dim CN As New OdbcConnection("Dsn=ABADB;dbq=" & strDBPath & ";defaultdir=" & strDBPath & ";driverid=538;fil=Paradox 5.X;maxbuffersize=2048;pagetimeout=5;uid=admin")
Dim CMD As New OdbcCommand("SELECT CONVERT (Description USING utf8) FROM slptrans WHERE RecordID=2", CN) 'recordID 2, 3 or 4. 2 is interpreted correctly; 3 and 4 are not.
CN.Open()
Dim DR As OdbcDataReader = CMD.ExecuteReader()
DR.Read()
Debug.Print(DR(0))
Can't get value from this method as it errors.
From FileStream.Write and System.Text.Encoding:
Dim CN As New OdbcConnection("Dsn=ABADB;dbq=" & strDBPath & ";defaultdir=" & strDBPath & ";driverid=538;fil=Paradox 5.X;maxbuffersize=2048;pagetimeout=5;uid=admin")
Dim CMD As New OdbcCommand("SELECT Description FROM slptrans WHERE RecordID=2", CN) 'recordID 2, 3 or 4. 2 is interpreted correctly; 3 and 4 are not.
CN.Open()
Dim DR As OdbcDataReader = CMD.ExecuteReader()
DR.Read()
Dim bytBLOB(DR.GetBytes(0, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
DR.GetBytes(0, 0, bytBLOB, 0, bytBLOB.Length)
DR.Close()
CN.Close()
Dim FS As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)
FS.Write(bytBLOB, 0, bytBLOB.Length)
FS.Close()
'trying to encode within memory... none of these work. tried many.
Debug.Print(System.Text.Encoding.Unicode.GetString(bytBLOB))
Debug.Print(System.Text.Encoding.UTF8.GetString(bytBLOB))
Debug.Print(System.Text.Encoding.UTF32.GetString(bytBLOB))
Debug.Print(System.Text.Encoding.UTF7.GetString(bytBLOB))
Value from TXT File: G Ðc Draft/revise here we go I'm adding a bunch of new stuff heresadgkj 2@#@ 2355 @^@&&()""" wlgkjw end H H H H H H8 H8 HB HB HX HX H^ H^ (data including and between 'Draft' ---> 'end' is correct.)
Value from Encoding: can't put result here because stackoverflow flags the Chinese and unknown characters as spam
directly from DataGrid (DataGrid selection change event):
Debug.Print("value " & dgSlips.SelectedCells(0).Item(dgSlips.SelectedCells(0).Column.DisplayIndex))
Value from DataGrid: G
Directly from DataTable used as ItemsSource for DataGrid:
'get recordset of selected table
OpenDB()
Dim RS As New ADODB.Recordset
RS = rsQueryTable(cbTables.Text)
'cast recordset into datatable
Dim DT As New DataTable
Dim DA As New OleDbDataAdapter()
DA.Fill(DT, RS) 'fills the data table with the recordset
'cast datagrid with datatable
dgSlips.ItemsSource = DT.DefaultView
dgSlips.DataContext = DT
Debug.Print("Value from DataTable: " & DT(2)("Description"))
CloseDB()
Value from DataTable: Gཇߐc牄晡⽴敲楶敳栠牥敷朠❉摡楤杮愠戠湵档漠敮⁷瑳晵敨敲慳杤橫㈠⍀⁀㌲㔵䀠䁞☦⤨∢•汷歧睪攠摮ḀĀഀ䠀䠀᠏䠁᠏Ԁ䠀ᬏԀ䠁ᬏ䠀㠏䠁㠏Ѐ䠀䈏Ѐ䠁䈏䠀堏䠁堏Ѐ䠀帏Ѐ䠁帏G���������������������������������������������
