mrfitness


I created two programs in which both connect to SQL database to execute a SELECT query.
The first one is a straight forward one with one table and works fine when I display info
SQLEXEC(m.lnConn, 'SELECT * FROM Cluster_info', 'cinfo')
scan
cinfo.cluster_name

The second one is trying to JOIN a table and a view. I first went into SQL and used the query view to select and join my table/view to automatically create the select statement below:

SELECT contract_dim.contract_no, COUNT(_Trans_2010.shipment_key) AS ships, SUM(_Trans_2010.total_revenu) AS rev
FROM _Trans_2010 INNER JOIN
contract_dim ON _Trans_2010.cust_contract_key = contract_dim.contract_key
GROUP BY contract_dim.contract_no

I used this in VFP6, replacing the old straight forward SELECT that did work. At first it did not recognize the GROUP BY so I deleted it. I changed it and added an ALIAS 'cinfo' below:

SQLEXEC(m.lnConn, 'SELECT contract_dim.contract_no, COUNT(_Trans_2010.shipment_key) AS ships,;
SUM(_Trans_2010.total_revenu) AS rev FROM _Trans_2010 INNER JOIN;
contract_dim ON _Trans_2010.cust_contract_key = contract_dim.contract_key','cinfo')

When I try to display information (i.e. (cinfo.contract_no), I get an error "ALIAS cinfo not found." I tried to use the BROWSE command after the SQL Select statement and instead of showing the results in an alias table as it did with the first program, it asks me to open a foxpro table in my current folder. To me this is saying the SQL statement is not working.

Is it possible in VFP6 to use joins on the select statements, or do I have to put this join in a view and access that view in foxpro instead of joining (which I want to avoid as I want to save space) Is it possible to use a GROUP BY as well



Re: Using a JOIN when communicating with SQL from VFP6

Alex Feldstein


Yes it is.

You seem to be confusing SQL Pass-Through (SPT) where you pass you query as text to the SQL-Server ODBC driver, as you are doing in the SQLEXEC() function with the Fox subset of the SQL language which is used to query FoxPro tables (DBF) directly (i.e. without using ODBC and SQLEXEC).

In queying DBFs you are limited to what flexibility that old version gave you (and yes, it can do GROUP BY). Remember that VFP 6.0 is very old and the SQL subset has evolved tremendouysly in newer versions up to Visual FoxPro 9.0

When you use SPT, you are passing a query un-validated by VFP. It is up to the ODBC driver you are using, to accept it and pass it through, and to the underlying database (SQL Server of whatever version you are using) to accept or reject the structure of your query. Therefore JOINS and GROUP BY should work.






Re: Using a JOIN when communicating with SQL from VFP6

mrfitness

Ok fair enough..but how would I retrieve the data in a cursor that I am running on SQL server into a table on my local computer folder I tried doing the code below but get an error "function name is missing )"

lnConn = SQLStringConnect("Driver={SQL Server};Server=pclpricing;Database=2010;UID=Brad;PWD=fred2004;Trusted_Connection=no;")

IF m.lnConn > 0 && Success.

SQLEXEC(m.lnConn, 'SELECT * FROM Cluster_info' into table cinfo, 'cinfo')





Re: Using a JOIN when communicating with SQL from VFP6

Naomi Nosonovsky

SQLEXEC(m.lnConn, 'SELECT * FROM Cluster_info', 'cinfo')

Remove into table here.






Re: Using a JOIN when communicating with SQL from VFP6

Alex Feldstein

mrfitness wrote:
Ok fair enough..but how would I retrieve the data in a cursor that I am running on SQL server into a table on my local computer folder I tried doing the code below but get an error "function name is missing )"

lnConn = SQLStringConnect("Driver={SQL Server};Server=pclpricing;Database=2010;UID=Brad;PWD=fred2004;Trusted_Connection=no;")

IF m.lnConn > 0 && Success.

SQLEXEC(m.lnConn, 'SELECT * FROM Cluster_info' into table cinfo, 'cinfo')

Just use:

SQLEXEC(m.lnConn, "SELECT * FROM Cluster_info", 'cinfo')