CODEWITHSUNDEEP
sqlvisual-foxpro
Lisa
Lisa

Reputation: 1

Visual FoxPro-query with parameters

Do somebody know how can i write in SQL language in Visual FoxPro a query with parameters? It doesn't work in the same way like it does in Access and I am a little bit lost here. Thank you in advance!

Upvotes: 0

Views: 3711

Answers (3)

Alejandro
Alejandro

Reputation: 7829

It depends on what kind of query you're using, either using the built-in SQL engine that queries native FoxPro tables or if it's a query using an external data source.

First for the case of a native query, it's really simple. Since it's a native FoxPro instruction, you can access to every single feature out there, including variables, where you can simply put your user supplied data without worries:

LOCAL data                                   &&This could come from user input
data = "hello world"
SELECT * FROM SomeTable WHERE Column = data  &&Filtered parametrized query

The other option is to submit a SQL query for an external server to process, where the query is sent as a text string. In this case, special markers for parameters can be used, matching FoxPro variables, and in turn that would result in a parametrized query sent to the server:

LOCAL data
data = "hello world"

LOCAL hConn = SQLSTRINGCONNECT(connectionString)
SQLEXEC(hConn, "SELECT * FROM SomeTable WHERE Column = ?data", "SomeTable")    &&The "?data" parametrizes the query, sending the value separate from the query itself
SQLDISCONNECT(hConn)

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23867

You can visit w3schools.com to see SQL basics. It doesn't work like access, that is true, because access, which is a so called SQL database engine, has its own understanding of the SQL. VFP OTOH is closer to ANSI SQL (IOW closer to SQL Server).

You haven't specified the language you are using, but saying access, I would assume you are trying with VBA. Here is a sample in VBA (excel) using parameters:

Sub Macro1()
   Dim oRecordset1 As ADODB.Recordset
   Dim oConnection As ADODB.Connection
   Dim oCommand As ADODB.Command
   Dim oParameter1 As ADODB.Parameter
   Dim oParameter2 As ADODB.Parameter


  Set oConnection = New ADODB.Connection
  Set oCommand = New ADODB.Command

  oConnection.ConnectionString = "Provider=VFPOLEDB;Data Source=C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind"

  oConnection.Open
  oCommand.ActiveConnection = oConnection
  oCommand.CommandText = "select * from Orders where OrderDate >= ? and OrderDate < ?"

  Set oParameter1 = oCommand.CreateParameter("start")
  oParameter1.Type = adDate
  oParameter1.Value = CDate("1996-08-01")
  oCommand.Parameters.Append oParameter1

  Set oParameter2 = oCommand.CreateParameter("end")
  oParameter2.Type = adDate
  oParameter2.Value = CDate("1996-10-01")
  oCommand.Parameters.Append oParameter2

  Set oRecordset = oCommand.Execute()
  Sheet1.Range("A1").CopyFromRecordset (oRecordset)
End Sub

Note: Parameters are positional, not named.

Upvotes: 1

Dhugalmac
Dhugalmac

Reputation: 574

Unfortunately your question is too broad to provide a simple answer.
The syntax for one query will vary from the syntax to perform a different query.

And Yes, the SQL Query syntax is likely slightly different than M$ Access.

However you can always do a Google search for: vfp sql query syntax to find specific syntax equivalents.

Note: The "WITH" parameters will be in a simple WHERE clause similar to most other SQL Query languages,
Such as WHERE Field1 = "ABC" AND Field2 = 235
but it will be using the VFP language syntax.

Also you might want to spend some time looking at the free, on-line VFP tutorial videos at: free on-line VFP tutorial videos
Specifically the one labeled: FoxPro and the SQL Language

Upvotes: 1

Related Questions