Passing parameterised SQL statements to FoxPro OLEDB

[Richard]

Hi,

Does anyone know if the VisualFoxPro 9.0 OLEDB driver supports
parameters? Trying to pass parameterised queries from the SQL Server
2005 SSIS integrated environment results in the error reported below,
which implies that the driver does support parameters, but that SSIS is
not calling the it properly somehow. Strange, as both the driver and the
environment are written by Microsoft!

Regards,

Richard

-----------------------------------------------

Error MsgBox:

TITLE: Microsoft Visual Studio
------------------------------

Parameters cannot be extracted from the SQL command. The provider might
not help to parse parameter information from the command. In that case,
use the "SQL command from variable" access mode, in which the entire SQL
command is stored in a variable.

------------------------------
ADDITIONAL INFORMATION:

Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Visual FoxPro)

[Cindy Winegarden]

Hi Richard,

Please be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.

Does this Visual Studio example work for you? If it does and your code still
throws errors, try posting the code so we can try to duplicate the problem.

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

Module Module1
Sub Main()
Try

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

Dim cmd1 As New OleDbCommand( _
"Create Table TestMemo (Field1 M)", cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand()
cmd2.Connection = cn1
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"

Dim p1 As New OleDbParameter()
cmd2.Parameters.Add(p1)
cmd2.Parameters(0).Value = "Hello World"
cmd2.ExecuteNonQuery()

Dim da1 As New OleDbDataAdapter( _
"Select * From TestMemo", cn1)
Dim ds1 As New DataSet()
da1.Fill(ds1)
Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
Console.ReadLine()

Catch e As Exception
MsgBox(e.ToString())

End Try
End Sub
End Module

[Richard]

Hi Cindy,

I have the latest drivers, and your code runs perfectly from a raw VS
project, so the problem is not FoxPro per se.

Unfortunately SSIS is a heavily integrated environment, all OLEDB
configuration properties are controlled through dialog boxes, the
properties held as XML, and the final executable deployed in some manner
I can't get to. So I can't post the code it is using.
I'll have a hunt and see what I can find.
I've also posted on the SQL Server SSIS forum on forums.Microsoft.com,
but no-one has replied as yet.

Just to check the obvious-
Am I right in using the normal syntax for the parameter:
"SELECT * FROM tbl_Stuff WHERE DateChanged > ? "

Thanks for your help,

Richard,

Post by Cindy Winegarden
Hi Richard,
Please be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
Does this Visual Studio example work for you? If it does and your code still
throws errors, try posting the code so we can try to duplicate the problem.
Imports System
Imports System.Data
Imports System.Data.OleDb
Module Module1
Sub Main()
Try
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table TestMemo (Field1 M)", cn1)
cmd1.ExecuteNonQuery()
Dim cmd2 As New OleDbCommand()
cmd2.Connection = cn1
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "Insert Into TestMemo (Field1) Values (?)"
Dim p1 As New OleDbParameter()
cmd2.Parameters.Add(p1)
cmd2.Parameters(0).Value = "Hello World"
cmd2.ExecuteNonQuery()
Dim da1 As New OleDbDataAdapter( _
"Select * From TestMemo", cn1)
Dim ds1 As New DataSet()
da1.Fill(ds1)
Console.WriteLine(ds1.Tables(0).Rows(0).Item(0).ToString())
Console.ReadLine()
Catch e As Exception
MsgBox(e.ToString())
End Try
End Sub
End Module

[Cindy Winegarden]

Hi Richard,

This works for me:

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

Dim cmd1 As New OleDbCommand( _
"Create Table Test (Field1 C(10))", cn1)
cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand( _
"Insert Into Test Values ('HelloWorld')", cn1)
cmd2.ExecuteNonQuery()

Dim cmd3 As New OleDb.OleDbCommand( _
"Select * From Test Where Field1 = ?", cn1)
Dim p1 As New OleDbParameter()
p1.Value = "HelloWorld"
cmd3.Parameters.Add(p1)

Dim da1 As New OleDbDataAdapter(cmd3)
Dim ds1 As New DataSet()
da1.Fill(ds1)
MsgBox(ds1.Tables(0).Rows(0).Item(0).ToString())

[Richard]

Hi Cindy,

Works for me to - which implies there is a BUG in the SQL Server
Integration Services UI, as it clearly does not parse the string in such
a way that the OLEDB driver can pick up the parameter. Reading the SSIS
forums, at least one other chap has seen similar behaviour from the UI,
and that was with an OLECB driver into a SQL database.

I've worked round the problem by creating the entire SQL query as a
string, and passing that to the OLEDB driver in one go. This works fine,
it is just less transparent when someeome else comes to maintain the code.

Thanks for all your help on this one - I'm off to annoy the SQL team ;-)


Best Regards,

Richard

Post by Cindy Winegarden
Hi Richard,
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table Test (Field1 C(10))", cn1)
cmd1.ExecuteNonQuery()
Dim cmd2 As New OleDbCommand( _
"Insert Into Test Values ('HelloWorld')", cn1)
cmd2.ExecuteNonQuery()
Dim cmd3 As New OleDb.OleDbCommand( _
"Select * From Test Where Field1 = ?", cn1)
Dim p1 As New OleDbParameter()
p1.Value = "HelloWorld"
cmd3.Parameters.Add(p1)
Dim da1 As New OleDbDataAdapter(cmd3)
Dim ds1 As New DataSet()
da1.Fill(ds1)
MsgBox(ds1.Tables(0).Rows(0).Item(0).ToString())

[Cindy Winegarden]

Hi Richard,

I'm glad it wasn't the FoxPro OLE DB data provider, otherwise I'd be off to
bug the Fox team!

[Liska Station]

For transparency, you could write the whole thing into a function, pass
your parameters to it, and make it look a little neater.
Although, I'm of the school of thought that says if it works, don't fix it.
Plus, there's the added job-security factor. (kidding) ; )

Seriously, if it's a program maintained by a lot of different people,
squirreling things away in functions, classes, and programs is a way to
keep everything neat, as long as each one consistently gives the correct
output.

Cheers!

Post by Richard
Hi Cindy,
Works for me to - which implies there is a BUG in the SQL Server
Integration Services UI, as it clearly does not parse the string in such
a way that the OLEDB driver can pick up the parameter. Reading the SSIS
forums, at least one other chap has seen similar behaviour from the UI,
and that was with an OLECB driver into a SQL database.
I've worked round the problem by creating the entire SQL query as a
string, and passing that to the OLEDB driver in one go. This works fine,
it is just less transparent when someeome else comes to maintain the code.
Thanks for all your help on this one - I'm off to annoy the SQL team ;-)
Best Regards,
Richard

Post by Cindy Winegarden
Hi Richard,
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table Test (Field1 C(10))", cn1)
cmd1.ExecuteNonQuery()
Dim cmd2 As New OleDbCommand( _
"Insert Into Test Values ('HelloWorld')", cn1)
cmd2.ExecuteNonQuery()
Dim cmd3 As New OleDb.OleDbCommand( _
"Select * From Test Where Field1 = ?", cn1)
Dim p1 As New OleDbParameter()
p1.Value = "HelloWorld"
cmd3.Parameters.Add(p1)
Dim da1 As New OleDbDataAdapter(cmd3)
Dim ds1 As New DataSet()
da1.Fill(ds1)
MsgBox(ds1.Tables(0).Rows(0).Item(0).ToString())