Clipper tables & SQL Server ?

[Steve]

I have clipper application that used clipper dbf and cdx tables.
How can I setup a Linked Server from SQL Server 2005 to access these
Clipper tables in SQL Server?
And would it be possible to write to these Clipper tables from SQL
Server? What about the Indexes, will they get updated as well?

thanks

[dlzc]

Dear Steve:

On Jul 19, 12:48 pm, Steve <aber...@gmail.com> wrote:
> I have clipper application that used clipper
> dbf and cdx tables. How can I setup a Linked
> Server from SQL Server 2005 to access these
> Clipper tables in SQL Server?

You can't, as I understand it. SQL can import / export, but not
natively treat it like a Clipper / (x)Harbour application.

> And would it be possible to write to these
> Clipper tables from SQL Server?

As an export, yes. I doubt the indexes would be created however.

> What about the Indexes, will they get updated
> as well?

Very doubtful.

The basis of SQL is to hide the implementation of how data is handled,
and manipulating DBFs is just too "dirty".

You can, with xHarbour, use your existing code base (depending on what
third party libraries you might have used), and use the SQL databases
directly. The SQLRDD is only available with the paid commercial
verion, however. But it'd be a lot cleaner.
http://www.xharbour.com/

David A. Smith

[Gerhard Bunzel]

Steve,

you can't write from the SQL-Server to your DBF-Files, but you can access to
SQL-Server from your Clipper-App and read/write your DBF-Files:
http://www.otc.pl/index.asp?s=1&l=2

HTH

Gerhard

"Steve" <abe...@gmail.com> schrieb im Newsbeitrag
news:3b7b3b62-3626-4521...@q12g2000yqj.googlegroups.com...

[Steve]

How about if the Clipper tables were connected to Access Database and
the Access in turn linked to SQL Server as a Linked server?

On Jul 19, 5:48 pm, "Gerhard Bunzel" <g.bun...@domonet.de> wrote:
> Steve,
>
> you can't write from the SQL-Server to your DBF-Files, but you can access to
> SQL-Server from your Clipper-App and read/write your DBF-Files:http://www.otc.pl/index.asp?s=1&l=2
>
> HTH
>
> Gerhard
>

> "Steve" <aber...@gmail.com> schrieb im Newsbeitragnews:3b7b3b62-3626-4521...@q12g2000yqj.googlegroups.com...

[Appr3nt1c3]

Hi Steve,

I tried this in SS2K5. What I did was

1. Create a DBASE III DSN in ODBC manager (control panel -> admin
tools)
2. Tested in Excel and it works.
3. In SSMS, created a linked server
Provider=Microsoft OLE DB Provider for ODBC Drivers
Product Name = DBASE III
Data source = <name of ODBC DSN> created in step 1

4. In the Providers node (under Linked Servers, righ click MSDASQL.
Check both Level Zero Only and Non-transacted Updates (Allow inprocess
is already checked by default)

Tried this in a new query window:

SELECT * FROM OPENQUERY( SUP, 'SELECT * FROM S3SAMPLES ORDER BY LNAME,
FNAME' )
UPDATE OPENQUERY( SUP, 'SELECT * FROM S3SAMPLES' ) SET FNAME = 'REY'
WHERE LNAME = 'AHLBERG'

(btw i used the dbf included in samples of superlib found ar oasis. i
havent done clipper for many years, but i still keep track of what's
happening)

Not sure about the index issue though. But i think maybe if you're not
going to update index fields then you'll be ok (not sure). Insertions
would be a major issue.

Hope this helps,

Rey

[Steve]

Rey,
After reading what you wrote, I tried Select, Update, Delete, and
Insert and they all seemed to work, though not updating the CDX. Does
this look right?

select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase
III;HDR=NO;IMEX=2;DATABASE=c:\data\dbf','select * from [customer]
where customerid > "9876"')
update OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase
III;HDR=NO;IMEX=2;DATABASE=c:\data\dbf','select * from [customer]
where customerid = "4107"') set customerid = '9876'
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase
III;HDR=NO;IMEX=2;DATABASE=c:\data\dbf','select * from [customer]')
([Customerid]) values ('xxx1')
delete OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase
III;HDR=NO;IMEX=2;DATABASE=c:\data\dbf','select * from [customer]
WHERE customerid = "xxx1"')

[Appr3nt1c3]

Hi Steve,

I haven't tried using OPENROWSET as I used OPENQUERY in my tests. IIRC
Jet OLEDB provider is for msaccess db.

[Steve]

What's the dif between OpenQuery and OpenRecordset?

Why would the Jet OLEDB provider work with dBase files?

[aardvark]

The Visual Foxpro ADO Driver does an okay job of accessing DBF files
with CDX indexes. There may be issues with record locking schemes in
Clipper though if it is used concurrently.

xHarbour doens't have this issue as it has the option to set record
locking scheme. xHarbour DBFCDX RDD and VisualFoxPro ADO works
concurrently for .

To make an adhoc request to DBF file data in Sql Server:-
SELECT * FROM openrowset ('VFPOLEDB.1', 'C:\SOMEDIR'; ''; '','SELECT
uid, name FROM customer')

For permanent use a linked server is probably better:-

SELECT * FROM OpenQuery(MY_VFP_LNKSVR, 'SELECT uid, name FROM customer')

..where MY_VFP_LNKSVR is a previous linked server. The following link
has more details on linked VFP servers:-

http://www.sqldev.org/sql-server-data-access/how-to-use-insert-into-select-from-query-to-export-ms-sql-data-in-foxpro-26-dbf-60445.shtml

Provided you are using a linked server you can also perform updates,
inserts, etc using the EXECUTE command as follows:-

EXECUTE ( 'update customers set name=''test2'' where uid=2' ) AT
MY_VFP_LNKSVR;

Note that the update command in the example above will be performed by
the VFP ADO driver so the SQL grammar needs to be the FoxPro flavour.

Alteratively you could use the Advantage Database Server (Commercial
Product) & access it via it's ADO Driver in MSSQL. From memory it can
be configured to coexist nicely with Clipper/CDX DBF files for
concurrent use. The licensing on this is probably hefty since you are
(potentially) sharing ADS capabilities with multiple users.

Regards
"aardvark"