bug with ado.net LEFT OUTER JOIN and visual fox pro

[Guest]

I've seem to have found a bug in ado.net:

I'm using visual studio 2003
and trying to connect to visual fox pro dbase ( i think version 8.0)
using the OleDb* components.

I've created the OleDbConnection and OleDbDataAdapter using the visual
studio wizards
and I run the SQL statements using:

try {
this.oleDbSelectCommand1.CommandText = "....";
DataTable dt=new DataTable();
oleDbDataAdapter1.Fill(dt);
dataGrid1.DataSource=dt;
} catch (Exception ex) {
MessageBox.Show(ex.ToString());
}


and I've run across a strange problem:

when I run this sql statement:

SELECT fldirord.order_num
FROM fldirord,fldirg
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID

I get an error:
System.Data.OleDb.OleDbException: SQL: Column 'FU_ID' is not found.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,

CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at testSQL.Form1.button2_Click(Object sender, EventArgs e) in
c:\\projects1.1\\utils\\testsql\\form1.cs:line 191

The strange thing is :
1. this statement works OK: ( I removed the "fldirg" table from the 'from'
line)

SELECT fldirord.order_num
FROM fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID

2. this statement also works OK: ( I changed the LEFT OUTER JOIN to a WHERE)

SELECT fldirord.order_num
FROM fldirord,fldirg,FLDORDF
where (fldirord.FU_ID = FLDORDF.FU_ID)

3. if I execute the original SQL statement (that fails) using CuteSQL
(connecting to the dbase using ODBC) it works!

If anybody got any idea what is happenning here,and how to fix this problem
please let me know

Thanks
Nadav

 

[Kevin Yu [MSFT]]

Hi Nadav,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you use LEFT OUTER JOIN IN
ado.net, an exception will be thrown. If there is any misunderstanding,
please feel free to let me know.

Based on my research, you're getting this error because there are three
table names in the SELECT statement. The order_num column is in fldirord
table, while it is joining the fldordf table. If you add fldirg in the FROM
clause, the driver will misunderstand to join the fldirg table, in which
FU_ID is not available.

The CuteSQL can execute this SQL because it is using the different driver.
So this is a limitation for the Foxpro OleDb driver.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi Kevin,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when you use LEFT OUTER JOIN IN
ado.net, an exception will be thrown. If there is any misunderstanding,
please feel free to let me know.

Yes, an exception is thrown.

Based on my research, you're getting this error because there are three
table names in the SELECT statement. The order_num column is in fldirord
table, while it is joining the fldordf table. If you add fldirg in the FROM
clause, the driver will misunderstand to join the fldirg table, in which
FU_ID is not available.

Are you saying that the OleDb Driver for visual fox pro will try to link
fldordf to
fldirg (the last table in the from clause)
even if I specificaly tell it to link to fldirord ("... LEFT OUTER JOIN
FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID ...")
I just tested it and this does seem to be the case, if I put fldirord last
in the 'from' clause the SQL works OK.

if this is the case how do I perform LEFT OUTER JOIN if i need to link from
two different tables?

i.e.
* linking fldirord -> fldordf -> fldunit works.
SELECT fldirord.order_num,fldunit.name
FROM fldirg,fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
LEFT OUTER JOIN FLDUNIT ON fldordf.FU_ID = FLDUNIT.ID

* linking fldirord -> fldordf, fldirord -> fldunit works.
SELECT fldirord.order_num,fldunit.name
FROM fldirg,fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
LEFT OUTER JOIN FLDUNIT ON fldirord.FU_ID = FLDUNIT.ID

* linking fldirord -> fldordf, fldirg -> fldunit does NOT work!
SELECT fldirord.order_num,fldunit.name
FROM fldirg,fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
LEFT OUTER JOIN FLDUNIT ON fldirg.FU_ID = FLDUNIT.ID

This is very strange!

Nadav

 

[Guest]

Hi Kevin,

I've just tested the same SQL statements using the
OdbcConnection,OdbcDataAdapter Components.
I still get the same problem.

How come I don't get this problem with CuteSQL (that connects using odbc)?

If the bug is in the visual fox pro driver shouldn't it happen with all
programs who are trying to connect to visual fox pro tables?

Thanks
Nadav

 

[Guest]

Hi Kevin,

I just noticed something.
The bug is even worse than I thought!

Based on my research, you're getting this error because there are three
table names in the SELECT statement. The order_num column is in fldirord
table, while it is joining the fldordf table. If you add fldirg in the FROM
clause, the driver will misunderstand to join the fldirg table, in which
FU_ID is not available.

However, fldirg does have a FU_ID field!

the SQL statement:
SELECT fldirord.order_num,fldunit.name
FROM fldirg,fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
LEFT OUTER JOIN FLDUNIT ON fldirg.FU_ID = FLDUNIT.ID

works with CuteSQL, but fails with ADO.NET!

Thanks,
Nadav

 

[Patrice]

It's likely that thr driver doesn't handle using two differents methods for
joining tables. (you have two tables in the FROM clause but you use also the
JOIN notation).

Waht if you try to use one method or the other but not mix them together ?

Patrice

 

[Guest]

Hi Patrice!

Waht if you try to use one method or the other but not mix them together ?

This is a great idea!
so instead of using
SELECT fldirord.order_num,fldunit.name
FROM fldirord,fldirg
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
LEFT OUTER JOIN FLDUNIT ON fldirg.FU_ID = FLDUNIT.ID
WHERE fldirord.fu_id = fldirg.fu_id
I use
SELECT fldirord.order_num,fldunit.name
FROM fldirord
LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID
INNER JOIN fldirg ON fldirord.fu_id = fldirg.fu_id
LEFT OUTER JOIN FLDUNIT ON fldirg.FU_ID = FLDUNIT.ID

And it works!

Thanks! You solved my problem!

Nadav

 

[Cindy Winegarden]

Hi Kevin,

I beg to differ. The following code works perfectly well for me:

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.OleDb

Module Module1
Sub Main()
Try

Dim cn As OleDbConnection
cn = New OleDbConnection("Provider=VFPOLEDB.1;Data Source=C:\;")
cn.Open()

Dim cmd1 As New OleDbCommand("Create Table fldirord " & _
"(Fu_ID I, Order_Num I)", cn)
Dim cmd2 As New OleDbCommand("Insert Into fldirord Values (1, 1)", cn)
Dim cmd3 As New OleDbCommand("Insert Into fldirord Values (2, 2)", cn)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()

Dim cmd4 As New OleDbCommand("Create Table Fldordf (FU_Id I)", cn)
Dim cmd5 As New OleDbCommand("Insert Into Fldordf Values (1)", cn)
Dim cmd6 As New OleDbCommand("Insert Into Fldordf Values (2)", cn)
cmd4.ExecuteNonQuery()
cmd5.ExecuteNonQuery()
cmd6.ExecuteNonQuery()

Dim cmd7 As New OleDbCommand("Create Table FldIrg (Fu_Id I)", cn)
Dim cmd8 As New OleDbCommand("Insert Into FldIrg Values (1)", cn)
Dim cmd9 As New OleDbCommand("Insert Into FldIrg Values (2)", cn)
cmd7.ExecuteNonQuery()
cmd8.ExecuteNonQuery()
cmd9.ExecuteNonQuery()

Dim da As New OleDbDataAdapter( _
"SELECT fldirord.order_num " & _
"FROM fldirord, fldirg " & _
"LEFT OUTER JOIN FLDORDF ON fldirord.FU_ID = FLDORDF.FU_ID", cn)
Dim ds As New DataSet()
da.Fill(ds)

MsgBox(ds.Tables(0).Rows(0).Item(0).ToString())

Catch ex As Exception
MsgBox(ex.ToString())
End Try

End Sub
End Module

 

[Guest]

Hi Cindy!

I Copied your code, and ran it and I got Exception:
"System.Data.OleDb.OleDbException: SQL: Column 'FU_ID' is not found."

The System I'im using is:
Windows XP Pro 2002 (SP1)
Visual Studio 2003
vfpoledb.dll ver:8.0.00.3117

If I change the order of fldirg,fldirord in the FROM clause the sql
statements executes OK.

It's strange and irritating but you can work around it by joining all tables
using INNER JOIN so it's not critical.

Thanks for your response

Nadav

 

[Cindy Winegarden]

Hi Nadav,

It works for me in VS2003, but I have the latest VFP OLE DB data provider -
the one that installs with VFP9. VFP9 RTMed in December and is available
from the MSDN subscriber downloads. It's version 9.0.0.2412. By the way, it
is backwardly compatible to read any version of FoxPro tables.

Time to upgrade? <g>

 

[Guest]

Hi Nadav,

It works for me in VS2003, but I have the latest VFP OLE DB data provider -
the one that installs with VFP9. VFP9 RTMed in December and is available
from the MSDN subscriber downloads. It's version 9.0.0.2412. By the way, it
is backwardly compatible to read any version of FoxPro tables.

Time to upgrade? <g>

Thanks, I'll download the new OLEDB data provider and see if it works.

Nadav

 

[Cindy Winegarden]

Just for the record, the VFP9 OLE DB data provider has been posted publicly
at
http://www.microsoft.com/downloads/...8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en .

It will work with all versions of FoxPro and Visual FoxPro tables.