• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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.
0
moia
Asked:
moia
  • 5
  • 4
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now