Has anyone gotten this to work

I wanted to make a view that pivots a table like this:

From columns

Size 1, Size 2, Size 3

12 12 24

To Rows:

Size1 12

Size2 12

Size3 24

That way I can do matching from another table from row to row instead of using a complex program

I did this in SQL very quickly but can't seem to do it in oledb for foxpro. I've created a foxpro view once before but I never knew how to select from it... assuming they are like sql views... over oledb within VB

Re: FoxPro Views over VFP Oledb with VB?

Naomi Nosonovsky

OleDB supports Execscript function. So, you can create a program as a string and run it through execscript. There was a discussion on tek-tips forum with samples how to do that.

Re: FoxPro Views over VFP Oledb with VB?

Cindy Winegarden

Hi Kanaida,

What did you do in SQL that worked What have you tried against VFP data via OLE DB You can select data from a Fox view the same as you would work with data from any other Fox table.

Re: FoxPro Views over VFP Oledb with VB?


VFP doesn't have pivot/unpivot clauses as SQL2005 does. However you can use DDL/DML commands with VFPOLEDB to create a temporary table to have the same result. Or with no temp table you could run a query like:

select myField, 'Size1' as cSize, Size1 as nSize from myPivot ;

union ;

select myField, 'Size2' as cSize, Size2 as nSize from myPivot ;


The problem is with the original design of data. Pivoting should be on demand, not original data design even in SQL server.

Re: FoxPro Views over VFP Oledb with VB?


I'm interested in seeing a small sample of creating a simple view and selecting from it using a command that i can pass to the vfp oledb provider..

last time i tried, I did something like (not exact but close)

create view View1 (select order where order > 1000)

then I created an oledbCommand and tried to execute a select and I got table doesent exist...

when i tried referencing to it another way, it told me it wasnt a table. eg. select * from `c:\view1`

One last question i have...

Is it possible to send multiple commands in one oledbCommand object or oledb query.

I have never been able to do a simple 2 liner such as:


select * from table group by something

wich really sucks when you only have one command you can run in SQL Reporting Services...

it will only execute the first command it runs into.

Re: FoxPro Views over VFP Oledb with VB?

Naomi Nosonovsky

See this code by Borislav Borissov in the tek-tips forum about using Execscript as I suggested in my first reply:

oAdoConn = CREATEOBJECT([ADODB.Connection])
oAdoConn.ConnectionString = [Provider=vfpoledb.1;Data Source=D:\All_zapl_4_9_0\;Collating Sequence=machine;]

oAdoComm = CREATEOBJECT([ADODB.Command])
oAdoComm.ActiveConnection = oAdoConn
oAdoComm.CommandText = [EXECSCRIPT("SELECT 0"+CHR(13)+CHR(10)+"USE Test EXCLUSIVE"+CHR(13)+CHR(10)+"INDEX ON Fld1 TAG Test")]

Re: FoxPro Views over VFP Oledb with VB?

Cindy Winegarden

Hi Kanaida,

You probably know that FoxPro tables have a DBF extension. are basically flat tables and can have indexes. With VFP3, along came the "Database Container" or DBC file. It contains, among other things, view definitions. When there is no DBC present then the tables are "free" tables and a typical connection string points only to the directory where the DBFs are. If a DBC file is present then the connection string should point to it.

In order to access a view you have to be connecting to a database container where the view definition will be stored.

Although I was able to run Naomi's example, I was not able to execute "Execscript([Create Database MyDatabase])" or "ExecScript([Create View My View As Select * From MyTable])" via ExecScript. Neither Create Database or Create View are supported (without ExecScript) by VFP OLE DB.

I don't have a clue why you would want to Set Enginebehavior 70; people mainly use it when they have old, non-standard, sloppy, meaningless SQL Select commands. (I guess you can tell I don't like it.) "Select * From Table Group By Something" is an example of this type of programming. I'm sure you meant that only as an oversimplified example.

Re: FoxPro Views over VFP Oledb with VB?


You can execute multiple commands if they are like in your sample ( set command, followed by a DML ). Just send them separately (yes different then SQL server). Most of the set commands are supported and it is easy enough to call them once on the connection. Then make your other calls.

BTW w/o enginebehavior 70 that woudl error, right but that error if it were completely in SQL serverSmile You can have the same result of that behavior w/o grouping (would be somewhat a tricky SQL but you can). Or get it into a SQL server temp table and do whatever there.

I don't use views and thus I did't try but probably selecting from a view it would be like:

select ... from databaseName!viewname

No need to use a view if you already have the definition SQL. Directy use that SQL (and parameters collection).

You can directly create table with DDL w/o execscript. Execscript is seldomly needed but I don't suggest (its memory space is handled separately, tricky to setup right).

Re: FoxPro Views over VFP Oledb with VB?


I'll give that exec script a go to see if it helps.

My problem is actually pretty massive

They're FoxPro 2.0 Flat tables accross Samba on a SCO Unix 5.06 Box

Sometimes samba wants to corrupt indexes because of poor file locking mechanisms (no file system transactions... and poor speed among other things)

Oledb is my only choice within SQL Reporting Services that doesen't require messy web services

There are times because of bad database design (it was like that long before me... like 10 years Smile )

that i did need to set the EngineBehaviour to 7.0... I can't think of an example but it had to do with matching

up the impossible tables that i have to deal with...

Well I'll let you know how it goes to see if i can make those views on the free tables..

Re: FoxPro Views over VFP Oledb with VB?


and yeah that group by was just an example Smile