CChalom


Hi,

I've been using Remote Views to access native VFP DBC's.through ODBC.

As I want to use some of the new functionalities of VFP data, AutoInc, Varchars, Blob fields, I was recommended to try using ADODB with Cursor Adapters.

But I've found no samples using VFP native data... Can someone recommend be some kind of literature for that

I was told that accessing data using ADO is more difficult than using ODBC, is that true

Thanks in advance

Cesar




Re: Cursor adapters, ADO and Native VFP Data

Naomi Nosonovsky


I'm not sure if this can serve as a recommendation, but I was using Mark McCasland's CA Builder for VFP Native data and everything worked smoothly.

One caution, though - try to avoid AutoInc fields if you do not want troubles. These fields are easy to use, but if you need to use them in Parent/Child relationship and get the ID before tableupdate - you're basically in trouble.

Also there is a series of articles on CA in foxite, I'm sure you're seen them.






Re: Cursor adapters, ADO and Native VFP Data

CChalom

Thanks Naomi,

I'll look for the "CA builder". But my question is about ADODB... have you used it with native data






Re: Cursor adapters, ADO and Native VFP Data


Re: Cursor adapters, ADO and Native VFP Data

Naomi Nosonovsky

No, I'm using Native. I think it's a really good link dni pointed you to.



Re: Cursor adapters, ADO and Native VFP Data

CetinBasoz

Cesar,

You can use any sample using ADO. The only thing that changes is connection string and seldomly the SQL itself (if you use provider specific syntax). Here is one sample from VFP8 days (actually this sample was against SQL2000's Northwind, I just changed it to use VFP's):

#Define MYCONNECTION "Provider=VFPOLEDB;Data Source="+_samples+'Northwind\Northwind.dbc'

oForm = Createobject('myForm',MYCONNECTION)

oForm.Show

Read Events

Define Class myForm As Form

DataSession = 2

Height = 600

Width = 800

Add Object Grd1 As myGrid With Height = 200, Width = 300

Add Object Grd2 As Grid With Height = 200, Width = 300, Left = 305

Procedure Init

Lparameters tcConn

This.AddProperty('oConn', Newobject("ADODB.Connection"))

With This.oConn

.ConnectionString = tcConn

.Mode = 16

.Open()

Endwith

This.AddProperty('Dataenvironment',Createobject('Dataenvironment'))

With This.DataEnvironment

.AddObject('caCustomers','myADOAdapter',This.oConn,'ca_customers')

With .caCustomers

.Tables = "Customers"

.KeyFieldList = "CustomerID"

.UpdatableFieldList = "CustomerID, CompanyName, ContactName, ContactTitle"

.UpdateNameList = ;

"CustomerID customers.CustomerID, "+;

"CompanyName customers.CompanyName,"+;

"ContactName customers.ContactName,"+;

"ContactTitle customers.ContactTitle"

.SelectCmd = "select * from customers"

.CursorFill()

Endwith

.AddObject('caOrders','myADOAdapter',This.oConn,'ca_orders')

With .caOrders

.Tables = "Orders"

.KeyFieldList = "ORDERID"

.UpdateNameList = "CUSTOMERID Orders.CUSTOMERID, "+;

"EMPLOYEEID Orders.EMPLOYEEID,"+;

"FREIGHT Orders.FREIGHT,"+;

"ORDERDATE Orders.ORDERDATE,"+;

"ORDERID Orders.ORDERID"

.UpdatableFieldList = "CUSTOMERID, EMPLOYEEID, FREIGHT, ORDERDATE, ORDERID"

.CursorSchema = "CUSTOMERID C(5), EMPLOYEEID I, FREIGHT Y, ORDERDATE D, ORDERID I"

.AddProperty('oCommand', Newobject('ADODB.Command') )

With .oCommand

.CommandText = "select Orders.CustomerID, Orders.EmployeeID, "+;

"Orders.Freight, Orders.OrderDate, Orders.OrderID"+;

" from Orders"+;

" where customerID = "

.Parameters.Append( .CreateParameter("CustomerID", 129,1,10,"") )

.Parameters("CustomerID") = ca_customers.customerID

Endwith

.oCommand.ActiveConnection = .Datasource.ActiveConnection

.AddProperty( 'oRS', .oCommand.Execute() )

.CursorFill(.T., .F., 0, .oRS)

Endwith

Endwith

This.Grd1.RecordSource = 'ca_customers'

This.Grd2.RecordSource = 'ca_orders'

Endproc

Procedure QueryUnload

Clear Events

Endproc

Procedure Destroy

Endproc

Procedure DispError

Local Array errors(1)

Aerror(errors)

'Error--------------'

For ix=1 To 7

errors[ix]

Endfor

'Error--------------'

Endproc

Enddefine

Define Class myGrid As Grid

Procedure AfterRowColChange

Lparameters nColIndex

With Thisform.DataEnvironment.caOrders

.oRS.ActiveCommand.Parameters("CustomerID") = ca_customers.customerID

.CursorRefresh()

Endwith

Thisform.Refresh()

Endproc

Enddefine

Define Class myADOAdapter As CursorAdapter

AllowUpdate = .T.

AllowInsert = .T.

AllowDelete = .T.

WhereType = 1

UpdateType= 1

SendUpdates = .T.

DataSourceType = "ADO"

Procedure Init

Lparameters toConn, tcAlias

With This

.Alias = tcAlias

.Datasource = Newobject("ADODB.Recordset")

With .Datasource

.ActiveConnection = toConn

.LockType=4

.CursorLocation = 3

.CursorType = 2

Endwith

Endwith

Endproc

Procedure UpdateTable

This.Datasource.Updatebatch()

Endproc

Enddefine





Re: Cursor adapters, ADO and Native VFP Data

CChalom

Hi Cetin, Dni and Naomi.

You've provided some good places and codes to start.

Thanks very much !

But I'd like to know another thing...

What's the reason that in my researches I've found the vast majority ~80% of people using ODBC and not OleDB

Is ODBC more reliable, easier or faster

Thanks again

Cesar





Re: Cursor adapters, ADO and Native VFP Data

Naomi Nosonovsky

You can not use ODBC with new types of data, since ODBC driver was not updated since VFP6. May be you're referring to OleDb



Re: Cursor adapters, ADO and Native VFP Data

CChalom

Yes, I was comparing ODBC and ADODB.

Sorry, I've just updated the original message.

Thanks

Cesar





Re: Cursor adapters, ADO and Native VFP Data

CetinBasoz

Cesar,

Historical reasons I think. SPT and RV predate the CA. In SPT and RV your only chance was to use ODBC. ADO was an unknown territory to majority of VFPers. Not only VFP audience but say in office applications, though they supported, you had to do that either in code or through tricky hoops.

ADO provides much more capabilities than you can do with ODBC both provider wise and others (ie: hierarchical data, multivalued items, ability to load/save both XML and ADO datagram formats ...).

Today with CA, whatever your backend is, IMHO you should use ADO (actually it is only ADO support that makes CA attractive to me). Why you would ask.

-ODBC drivers are not updating for all backends (ie: VFP last ODBC driver was in MDAC 2.5 or 2.6 - not compatible with VFP9).

-VFP is not processing ODBC results right for all backends. This is a bug (might or might not be corrected before support ends in 2015). A typical example is new SQL2005 xml fields.

-Stored procedures are not supported on all backends (ie: VFP)

-With ODBC there might be limited or no way to access data on all backends when backend specific capabilities are activated (ie: VFP).

-ODBC drivers are supported by less by external data formats (as of today I think every data source has support by OLEDB but not ODBC - there might be few that supports only ODBC, I don't know).

Since my experience is mostly limited to VFP and SQL server I don't know other backends limitations.




Re: Cursor adapters, ADO and Native VFP Data

CChalom

Hi Cetin,

I've been making some tests with ADODB acessing some tables (DBFs) in a DBC.

I'd like just a confirmation...

The best way to work with ADO is in programatic mode, NOT using the builders, right





Re: Cursor adapters, ADO and Native VFP Data

CetinBasoz

Hi Cesar,

Wrong target for the questionSmile I'm biased in that I like to "code"Smile However builders for CA have come a long way since VFP8 and it might be better to use them.





Re: Cursor adapters, ADO and Native VFP Data

CChalom

Thanks again Cetin,

I've converted it to a form, and I managed to access the DBC data using ADODB.

The updates are working fine.

But in this table I have an Integer Autoinc field as PK.

When accessing data using the CA, when I "APPEND BLANK", in the ID field, I receive the value 0 - ZERO. After the modifications, even sending a TABLEUPDATE, the modifications are not saved.

If I change manually the value of the Autoinc field to a valid value, the INSERT is saved correctly.

But if I open the table DIRECTLY without the CA, with the USE command, when I APPE BLANK, the AutoInc integer field is updated correctly.

How can I tell the cursoradapter to update the Autoinc Field Value automatically Shouldn't the CA update this value automatically

I've seen that you recommended in another post that you prefer to use GUIDs instead of Autoinc... Can you tell me why

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=97447&SiteID=1

Thanks in advance !





Re: Cursor adapters, ADO and Native VFP Data

Naomi Nosonovsky

Hi Cesar,

Are you talking about a native VFP data and AutoInc fields If yes, I would strongly advise you against using AutoInc fields and go with GUID instead or use NextID() approach if you want to use integer PKs. I was recently using AutoInc fields with CA and retrieving them was a real PITA. You can not get the value until you do tableupdate(). That's one problem. Also I'm currently using VFP8 (to my regret Sad, I asked for VFP9 several months ago with no result yet) and therefore few new features in CA are unavailable to me. But even in VFP9 it would be a complex task.

If you still want to use AutoInc fields, you may try to ask on the forum "not to be named" (sorry for referencing another forum, but I had several threads on the topic there recently).





Re: Cursor adapters, ADO and Native VFP Data

CetinBasoz

Hi Cesar,

Though I don't use autoinc fields myself, formerly I created test routines for them using OleDb from .Net and they all worked right. If you're using .Net then I could send a sample C# code.

I never tried an autoinc with CA but I'd start by either removing the autoinc field or marking it as not updatable in CA (it's readonly anyway so I don't really have an idea what's going on).

Yes I'm suggesting GUIDs as primary keys at every opportunitySmile With GUIDs the outshining advantage is that you can create the key even before that you have an idea if you'd create a record and submit to server. IOW I could now create a key here, pass it to you and you could use it anywhere you need a PKSmile Speaking of that let me write one here: {94C7B4B4-9A7F-458D-B9A2-DF3B2531CCDF}

I generated this one on my machine now and it's guaranteed to be unique worldwide (in theory it could have a duplicate but in theory). That means I don't need to ask to backend what my key is, I know it before it knows. When I submit, I send the key as an updatable value, but do not ask (requery) back what my key is now, I know it.

Another advantage is during merging or replicating the data. When merging data from 2 offices for example you might guess the problems of same keys being generated independantly. We all have made workaround solutions, sometimes by allocating ranges, sometimes having "fixup" codes. Worse what if both offices also had records that were really common (got for offline usage).

The most spoken 3 disadvantages:

1) They're slower. Well I think this is a myth and could be debated upon. In theory and academic level tests they're slower but not in daily practical usage. Maybe I'm too much spoiled with VFP and SQL server's speed.

2) Hard to read. Yes they are hard to read and never should be available to end user (ints too should never be presented to user). Being hard to read matters to developer during testing. That's a price you need to pay.

3) Takes more space. Yes compared to 4bytes of int they take up at least 16bytes. In most expanded form they take up 38 bytes (I simply use 36 or 38 version). Since I design my tables with few fields as possible this had never been an issue to me. However in design sometimes I create my tables that would seem to be a crazy thing to doSmile - For example think of Employee table, if it were my design, photo, notes and maybe some other fields would be in a different table linked 1-to-1 (assuming I might have a huge number of Employees).

GUIDs are also portable to other backends like SQL server. SQL server have built-in NewID() to generate them and now in 2005 have another NewSequentialID to create them sequentially in order to prevent torn pages and speed up insertions.