Get data to VFP9 DB via OLEDB Provider

I want to get data from a real time data system into VFP9 DB via OLEDB Provider.
Configured data as described in Walkthrough: Retrieving Data from Different Data Sources in VFP help - ADO.
I cant see the data in the grid. I checked the source is OK as I get data displayed in the OLEDB Provider tester.
moiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
After establishing the OLE DB connection you have to retrieve data, so you should continue on "Retrieving Data for Cursor Adapters" part of the same help topic.

You could also test data retrieval from Command window which will help you to learn ADO techniques. More info is in the help topic "How to: Access Visual FoxPro Data in Visual Studio"

To help better you should post your code or form which does not work, so we may look what's wrong with it.

Also, does the grid works for you when displaying standard DBF data?
0
moiaAuthor Commented:
Capturing data from real time system called let's say XX
I didnt write code but used the builder to create the cursor adapter with SelectCmd:
select time, tag, value, timestep from xxtotal where tag = 'XXX'  AND time BETWEEN '*-4d' AND '*' and timestep = '1d'
I am using XX OLEDB Provider not FoxPro OLEDB Provider.
The SQL statement is different in XX OLEDB so when built in DataAccess tab for the cursor adapter it changes to VFP style.
The original XX OLEDB SQL statement looks like:
SELECT * from xxarchive..xxtotal where tag = 'XXX'  AND time BETWEEN '*-4d' AND '*' and timestep = '1d'
The convention for current time is '*' while '*-4d' stands for 4 days back.
I dont know if I have to stick to XX OLEDB convention or I should adapt it to VFP SQL statement.
Anyway since in the builder I cannot insert the where clause XX system triggers an error message then I add the where clause in the SelectCmd
The connection string is fine as I get successful connection.
0
pcelbaCommented:
FoxPro does not know about XX OLEDB convention so you should leave the WHERE in SelectCmd. Another option is to use your own code to retrieve data from OLE DB source instead of leting builders to do this work.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

moiaAuthor Commented:
in command line I typed:
oConn = CREATEOBJECT("ADODB.Connection")
oConn.ConnectionString = "provider=xxOLEDB.1;Password='';User ID=operator;Data Source=qqxx;Persist Security Info=True"
oConn.Open

SELECT tag, time, value FROM picomp WHERE tag = '31FI50010.PV' AND BETWEEN(time,DATETIME(2010,1,1),DATETIME())

The debugger shows that the connection is open and can see the default database.
How can I point the query to the OLEDB Provider ?
0
pcelbaCommented:
You may create RecordSet:

oRS = oConn.Execute("SELECT tag, time, value FROM picomp WHERE tag = '31FI50010.PV' ")
oRS.MoveFirst
? oRS.Fields[0].Value

Notes:
BETWEEN(time,DATETIME(2010,1,1),DATETIME())  is FoxPro syntax but you should use syntax suitable for your OLE DB provider.

More about using ADO in VFP:
http://fox.wikis.com/wc.dll?Wiki~UsingADO
http://msdn.microsoft.com/en-us/library/ms917355.aspx
http://support.microsoft.com/kb/272338/en-us/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moiaAuthor Commented:
That is beautiful. Im getting the data.

I haven't worked with ADO before. :)  
Now I have to scan each record and write it into a table. Is that the only way I can get the data stored into a VFP table?

Thank you
Manuela
0
moiaAuthor Commented:
Outstanding
0
pcelbaCommented:
Interesting... I've sent one more replay which is nowhere. At least I think I did it...

So, again.

I am also not working with ADO and I've been looking for some Fill method which seems to be unavailable in FoxPro.

To store data into a table you may loop through the record set or you may use CursorAdapter class which has CursorFill method to do this work for you.

Another option is to use ODBC driver (if available) and SQL-pass through functions to store query results directly into a cursor. If you have ODBC driver then you should look at all functions beginning with SQLxxxx (SQLCONNECT, SQLEXEC, SQLTABLES, ...)
0
pcelbaCommented:
One more note: ADO is the slowest data access available in VFP :-(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.