OLEDB Foxpro codepage character translation

[MikeyRoberts]

Hi All,

I am working with a legacy application that uses Foxpro .dbf files for
some of its data storage. I am writing a new .net datalayer and I am
connecting to the .dbf files using the Foxpro OLEDB driver version 9
SP1.

One of the character fields in the DB uses characters with an
underlying byte value outside of the standard ASCII character set (eg
A0,D4).

The problem I am having is that the OLEDB driver is encoding the
characters within this field using codepage 850(multilingual DOS) and
providing the wrong characters in my .net dataset. Instead of bytes
values for my characters of A0,D4,D0 etc I am getting A3, CE, 2B etc.

This is complicated by the fact that I am observing the values of the
returned characters within windows, and I'm not sure if there is also a
codepage conversion happening to encode the characters to the Windows
Default encoding.

I have confirmed that it is a codepage issue with the OLEDB driver and
the .dbf file by editing the codepage flage (byte 29) in the .dbf.
Setting this to 00 or 03(codepage 1252) means that the characters are
returned exactly how I was expecting them. However, there doesn't seem
to be a way to set up the OLEDB driver to ignore the codepage flag in
the file. I have tried setting the locale identifier in the connection
string, but this didn't help.

So, in summary, here is the problem:
- I can not permanently change the codepage flag in the file as it may
have some effect in the legacy application.
- I can not use the CPCONVERT function as it is not supported from
within SQL queries in the OLEDB (only in Stored Procs)
- I can not see anyway to set up the OLEDB driver to ignore the
codepage flag in the .dbf
- I am currently only using free table access to the .dbf tables - a
.dbc might be a possibility, but I do not have access to Foxpro

I have a couple of thoughts of how I may approach this problem:

- Use ADOX to create a .dbc and add a stored proc to this .dbc to
extract the data, using CPCONVERT to extract the field data (this is
tricky - how do I create a stored proc for Foxpro in ADOX???)

- Find some way to reverse the codepage encodings that have been
applied to my string data to get the the original values

- Before executing my query, open the .dbf file as a byte stream and
reset the codepage flag (what a lovely Kludge!), remembering to set
this back when my query has finished - oh, and ignore any file locking
problems this may cause.

This issue has been driving me mad for a while now, any help would be
appreciated.

Cheers,

Mike

[Olaf Doschke]

Post by MikeyRoberts
The problem I am having is that the OLEDB driver is encoding the
characters within this field using codepage 850(multilingual DOS) and
providing the wrong characters in my .net dataset. Instead of bytes
values for my characters of A0,D4,D0 etc I am getting A3, CE, 2B etc.

I don't see CPConvert() mentioned in the help chapters.
neither in the one on supported nor in the one on unsopported
language.

Did you try executing an SQL Select with CPConvert()?
Something like
Select CPConvert(850,1252,characterfield) As characterfield From Table

Bye, Olaf.

[swdev2]

[Shudder]
Mikey
1. take a look at the VFP toolkit for dot something -
http://fox.wikis.com/wc.dll?Wiki~VFPToolkitForDotNet~DotNet

2. take a look at ActiveVFP at www.activevfp.com - add it in as a layer
for your dot something project. Let IT deal with the data layer instead.

3. Whilst the underlaying data has different codepages. - Are You certain
that the NEXT version (IE - the one that you are writing) _REQUIRES_ the
ability to handle different codepages?

Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

Post by MikeyRoberts
Hi All,
I am working with a legacy application that uses Foxpro .dbf files for
some of its data storage. I am writing a new .net datalayer and I am
connecting to the .dbf files using the Foxpro OLEDB driver version 9
SP1.

[snip]

[Cindy Winegarden]

"MikeyRoberts" <***@hotmail.com> wrote in message news:***@k70g2000cwa.googlegroups.com...

Hi Mike,

Post by MikeyRoberts
I have confirmed that it is a codepage issue with the OLEDB driver and
the .dbf file by editing the codepage flage (byte 29) in the .dbf.
Setting this to 00 or 03(codepage 1252) means that the characters are
returned exactly how I was expecting them. However, there doesn't seem
to be a way to set up the OLEDB driver to ignore the codepage flag in
the file. I have tried setting the locale identifier in the connection
string, but this didn't help.

Have you tried something like "Collating Sequence=GENERAL" or "CodePage =
1252" in your connection string?

Post by MikeyRoberts
- I can not use the CPCONVERT function as it is not supported from
within SQL queries in the OLEDB (only in Stored Procs)

I couldn't find any reference to using CPConvert via OLE DB in the VFP Help,
but you may be right.

Post by MikeyRoberts
- I am currently only using free table access to the .dbf tables - a
.dbc might be a possibility, but I do not have access to Foxpro

If the tables are free tables then creating a DBC and adding them to it
changes the table headers and would probably make them unusable from the
other application. However, you can use a DBC with a view to the free
tables. Alternatively you could write the stored proc such that it opened
the free table, read the data, closed it again, and returned the data to
you.

Post by MikeyRoberts
- Use ADOX to create a .dbc and add a stored proc to this .dbc to
extract the data, using CPCONVERT to extract the field data (this is
tricky - how do I create a stored proc for Foxpro in ADOX???)

Here's some code posted elsewhere by MVP Anders Altberg to create a DBC via
OLE DB:
I opened Excel and then VB with Alt+Fll and opened the Immediate window from
the View menu. I pasted this code into the window and executed each row with
Enter:

set o=CreateObject("adox.catalog")
o.Create("Provider=VFPOLEDB.1;Data Source=d:\vfpdata\vfp7beta\newdb.dbc")
o=null
set oc=CreateObject("adodb.connection")
oc.open("Provider=VFPOLEDB.1;Data Source=d:\vfpdata\vfp7beta\newdb.dbc")
oc.execute("create table newtable (id i, mem m)")
oc.execute("insert into newtable (id) values (123)")
oc = null

This worked perfectly and I could verify in VFP that the database and table
had been created and the value 123 was on the first row of the table.
Instead of the path d:\vfpdata\vfp7beta\ that I used, take one that actually
exists on your test machine.
<<


Here's an example I posted elsewhere that shows how to create a stored
procedure via OLE DB:
Here's an example of how to create a stored procedure. The stored procedure
is in a text file, C:\HelloWorld.txt You can read more about the
ExecScript() and Append Procedures commands in the VFP Help in the MSDN
Library.

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1

Sub Main()

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Program
Files\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind.dbc;")
cn1.Open()
'-- C:\HelloWorld.txt
'-- Procedure(HelloWorld)
'-- Return "Hello World"
'-- EndProc()

Dim cmd1 As New OleDbCommand( _
"ExecScript('Append Procedures From C:\HelloWorld.txt')",
cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand("HelloWorld()", cn1)
Dim val1 As String = cmd2.ExecuteScalar()
MsgBox(val1.ToString())

cn1.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

End Sub

End Module
<<