Varbinary key in tables

[vfp4ever]

Hello,

I am trying to connect to a local data source:

[tt]lnHandle = SQLSTRINGCONNECT( "Driver={Microsoft Visual FoxPro Driver}; UID=; PWD=; SourceDB=ex_database.dbc; SourceType=DBC; Exclusive=No; BackgroundFetch=No; Collate=Machine; Deleted=Yes; Null=No" )
SQLEXEC( lnHandle, "SELECT * FROM ex_table", "ex_cursor", laCount )
SQLDISCONNECT( lnHandle )
[/tt]
Everything works fine as long as I have an Integer key in ex_table, but when I change it to Varbinary type SQLEXEC refuses to work and returns a "Not a table" error. What do I have to do in order to use a Varbinary field in my table?

Regards,
Dario

 

[atlopes]

Vfp4ever,

The VFP ODBC driver does not support database features introduced after VFP6. The Varbinary data type is one of them.

Assuming there is a sound reason not to use VFP's direct access, you can either work with another VFP-compatible ODBC driver, such as Devart's, or access the database using a different connector, such as ADODB.Connection and VFPOLEDB.

 

[Chris Miller]

Exactly that.

Besides Devart there is

https://fox.wikis.com/wc.dll?Wiki~AdvantageDatabaseServer~Wiki

and you could handle the DBC via SQL Server as linked server, which can use the VFP OleDB Provider to be able to use newer features.

Chriss

 

[vfp4ever]

Oh, I see... Suppose I wanted to use VFPOLEDB, then the above statements should look like:

[tt]lnHandle = SQLSTRINGCONNECT( "Provider=VFPOLEDB; UID=; PWD=; Data Source=ex_database.dbc; Exclusive=No; BackgroundFetch=No; Collate=Machine; Deleted=Yes; Null=No" )
SQLEXEC( lnHandle, "SELECT * FROM ex_table", "ex_cursor", laCount )
SQLDISCONNECT( lnHandle )[/tt]

Unfortunately the Select Data Source pops up, and I cannot find the right entry to establish the connection. I must be missing some correct statement to make it DSN less, too.

 

[atlopes]

Vfp4ever,

You cannot use the VFPOLEB provider as an ODBC source. As I said before, you can use it with an ADODB connector.

You can also use a CursorAdapter to transform the RecordSet that the ADODB connection returns into a VFP cursor, and also to provide a framework for data processing. If you don't want to use a CursorAdapter, you'll have to deal with the RecordSet object to retrieve data.

LOCAL AC AS ADODB.Connection
LOCAL RS AS ADODB.Recordset
LOCAL CA AS CursorAdapter

m.CA = CREATEOBJECT("CursorAdapter")
m.CA.DataSourceType = "ADO"
m.CA.Alias = "ex_cursor"

m.AC = CREATEOBJECT("ADODB.Connection")
m.AC.ConnectionString = "Provider=vfpoledb;Data Source=ex_database.dbc;Collating Sequence=machine;"

m.AC.Open()

m.RS = CREATEOBJECT("ADODB.RecordSet")
m.RS.ActiveConnection = m.AC

m.CA.Datasource = m.RS

m.CA.SelectCmd = "SELECT * FROM ex_table"

IF m.CA.CursorFill()

	SELECT ex_cursor
	BROWSE

ENDIF

 

[vfp4ever]

Thanks atlopes,

but I still cannot see the Varbinary key field in the browse after executing your code. It shows as "Memo".

 

[atlopes]

Vfp4ever,

It's a binary memo.

If you want to enforce specific data types to your cursor, you can set a schema before filling the cursor.

m.CA.CursorSchema = "targetColumnName Varbinary(200)"
m.CA.UseCursorSchema = .T.

Your actual schema will require all columns set, though.

 

[vfp4ever]

atlopes,

that is fine, with the latest assignments I was able to display the key field in the browse. And by changing a few more RecordSet properties I can also update fields. Unfortunately I get quite confused in all the examples I see around, so your short, precise answers help a lot.

Now the question is... do I have to mess with ADODB and CursorAdapter objects just because I decided to index my ex_table on a Varbinary key instead of an Integer? Is this the only way it can be done? After all, I am just trying to manage a local table and database as if it was remote data.

 

[Chris Miller]

do I have to mess with ADODB and CursorAdapter objects just because I decided to index my ex_table on a Varbinary key instead of an Integer?

Yes, only non-MS ODBC drivers are able to deal with field types introduced after VFP6. So the only official driver from MS is the OleDB Provider. And SQL PassThrough (SQLCONNECT and SQLEXEC et) only works with ODBC, to be clear about that once more. Atlopes already said that, if you read back.

I am just trying to manage a local table and database as if it was remote data.

The question is why?

I'm not questioning your use of varbinary as a data type, it's fine to store GUIDs in binary format, for example, but why not deal with local DBFs natively? If you want to be prepared to change platform you'll very likely not continue to use local DBFs but switch to XML config files or Local DB of browsers, so an access layer like ODBC or OleDB other platforms can use, too, becomes unimportant, you'll not keep the DBFs. And while you're in VFP you can continue to simply use these local DBFs, there's even no problem with opportunistic locks, it's only local usage, that has no network problems.

Chriss

 

[vfp4ever]

Yes Chris,

indeed I have always dealt with local DBFs so far. I was wondering what if one day I should create a remote database, based on what I now use (local), and connect to it. I imagine I should use CursorAdapters and make lots of changes in code, but once is done it should work for both Native and ADO data source types, shouldn't it? That is why I am asking. Forgive me if I got it wrong again.

Going back to the examples suggested above, I added a VFPCOM.COMUtil object to help me with RecordSets, so that I can convert them into cursors. The problem now is that method oVFPCOM.RSToCursor seems to ignore the CursorSchema property that I have previously set and shows "Memo" in my browse again (instead of the Varbinary key). Also, because I have _VFP.LanguageOptions set on strict memvar declaration, a __TEMP_ARRAY variable remains undeclared in RSToCursor. How do I tell the compiler to ignore external missing declarations?

 

[Chris Miller]

Well, forget about VFPCOM.COMUtil, the cursoradapter will work via ADODB. That can also use the VFP OLEDB Provider, but DBFs can also be used "native".

3-tier architecture doesn't need to go through the ODBC or ADO layer, that's just for remote data which isn't DBFs. So you're overdoing this, if you try to make local DBFs work with an ADODB Recordset. Even if you go through ADO, as atlopes said already. With the code sample he provided, you end up in a VFP Cursor with the cursor adapter without the VFPCOM.COMUtil.

If I assume your local data isn't just config or other simple local exclusive for each user, but is the database backend for a single user app and you want to convert to multi user, then you should aim for migrating data to a SQL Server (MySQL or MSSQL, whatever) and then you surely don't go through VFP OleDB Provider, you'll use ODBC for the remote database.



Chriss

 

[Mike Lewis]

I'm coming late to this thread, so apologies if I have missed something.

But, Dario, I was wondering why you are treating the database as a remote data source. You appear to be accessing a VFP DBF from within VFP. Why not just use native VFP commands?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vfp4ever]

I'm not sure if I have made myself understood, let me try explain it again.

On my local PC I have a database and application for my single use and leisure. It works fine, using dbf tables and primitive commands such as USE, SET RELATION, SEEK, etc... So far I never needed to access remote data (e.g. a MySQL database on an internet server), but just for the sake of curiosity I am now wondering what I should change in code in order to reach out for it. From what I understood, it should be possible to wrap the above primitive commands for local access into a higher level using the CursorAdapter class. I believe it would eventually allow me to switch from local (DBFs) to remote (MySQL) only by changing a flag in my application (once it is developed for ADODB, ODBC or whatever). If that is not true, then forgive my embarassing confusion; otherwise, I assume I should be able to use the CursorAdapter class also for local tables, to keep code as close as possible to each version. Obviously my remote database may have different data types from my local .dbc, but still it should resemble the original for as much as possible.

You kindly explained me that, because I am using Varbinary key fields, I cannot use ODBC or SQL pass-through commands and am therefore confined to using ADODB and CursorAdapters (that sounds great though). I immediately discovered that RecordSets must be converted to/from cursors in order to keep controls and grids working, that is why I installed VFPCOM. And here I am again, I am not able to edit my Varbinary field because it reads "Memo" again. I suspect it's just a matter of settings and flags (as it is also for the __TEMP_ARRAY undeclared variable). At this point I would expect replies like "Hey, you can't do that as long as you keep using Varbinary fields in your tables" or "If you care so much about Varbinary fields then abandon the idea to use CursorAdapters". Or perhaps there is something else?

 

[Chris Miller]

From what I understood, it should be possible to wrap the above primitive commands for local access into a higher level using the CursorAdapter class.

I explained in length why that's not the case unless you already programmed in 3-tier architecture for a DBF backend in my lst 2 answers to

https://www.tek-tips.com/viewthread.cfm?qid=1809288

The kind of compatibility you expect from cursoradapter does not exist. There is a major change of how things work with a real remote backend, you can't use a table, you could select all data from it, but that'd be a long term performance desaster.

Chriss

 

[Chris Miller]

That said, any database access in VFP, no matter if native to DBFs or via views or SQL passthrough or cursor adapters results in cursors with data. And within them, you can use LOCATE, SET RLEEATION etc. but you expect too much if you think this means simple switching of backends. For your ET FILTER, LOCATE or SET RELATION to work you'd always need to fetch all data, which is bad for the performance and still has the disadvantage of not sharing this fetched data until you commit changes.

I suggest you first learn how to program in 3-tier architecture with DBFs and then you may switch to other backends.

Chriss

 

[atlopes]

Vfp4ever,

At this point I would expect replies like "Hey, you can't do that as long as you keep using Varbinary fields in your tables" or "If you care so much about Varbinary fields then abandon the idea to use CursorAdapters". Or perhaps there is something else?

It would help if you remembered why SPT fails when connecting to VFP data: the ODBC driver you're using is outdated. So, the simple answer seems to be: get a new ODBC driver.

I renew my suggestion:

https://www.devart.com/odbc/xbase/.

See if it solves your problem.

 

[Chris Miller]

I don't think the goal is to be able to use DBFs remotely. So once you aim for client/server you aim for MSSQL, MySQL, PostGreSQL or other, and ODBC drivers are available for them.

To first try with VFP you can use native access and learn about buffering and tableupdate/tablerevert, etc. and then you can use the cursoradapter with neither ADO nor ODBC, but native access to have a basis for switching backends. There are a few catches with this, but you don't need a more modern VFP driver to learn and test this.


Chriss

 

[vfp4ever]

@atlopes

I'm glad there is no problem at all to be solved, as I still program just for pleasure. Investing much of my free time in outdated software is not my biggest worry, as there is still so much to be explored in it.

@Chris

I did not realize how closely related that thread was with mine, I had read only half of that before starting this one and missed your last two posts. Sure, perhaps my expectations were too high, I agree. Unfortunately the three-tier architecture is a chapter I have found on books more than once, but never really understood, I guess it's always been easier for me to learn while looking at code examples rather than reading theoretical concepts.

I'd better look deeper into code and closer to practical examples. Thanks both for your suggestions.

Regards,
Dario

 

[TamarGranor]

@vfp4ever, I had a terrible time getting my head around business objects. When I finally did, I used that phrase as the title for a paper:

http://tomorrowssolutionsllc.com/ConferenceSessions/Getting Your Head Around Business Objects.pdf

This doesn't address the CS issues, but it at least looks at separation of business logic from data.

Tamar

 

[Chris Miller]

That's already a plus, I think you already know the goals of 3 tier architecture to separate concerns and have tiers or layers specializing in their functionalities.



Chriss