Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2268
  • Last Modified:

VFP 9 Select from MySQL Into dbf

Hi all. I know this isn't the complete code but hopefully it is enough that someone with experience can see where my problem is.

When run normally the cursor is populated correctly and I have access to the data in it. I say this so you know that the items not shown, such as pnConnection and pcTemp are correct.
In other words this does function as is, however...

I would like to visibly view the result in a dbf table just for testing purposes. I cannot for the life of me figure out how to get this into a table instead of a cursor.

(pcTemp) points to a temp directory and is used throughout the program, and the syntax shown is how I normally create tables there.

this works:
lcQuery="SELECT * FROM product " + ;
"WHERE (SUBSTRING(product_model, 1, 2) = '45')"
SQLEXEC(pnConnection,lcQuery,'mycursor')

I want to do this for testing:
lcTableName = (pcTemp) + "temp_table.dbf"
lcQuery="SELECT * FROM product " + ;
"WHERE (SUBSTRING(product_model, 1, 2) = '45')"
SQLEXEC(pnConnection,lcQuery,(lcTableName))

Open in new window

0
formadmirer
Asked:
formadmirer
2 Solutions
 
GreatSolutionsC.I.OCommented:
Run the query to the cursor, name your temp table then run

Select myCursor
Copy to (lcTableName)

That's it!

Jaime
0
 
Olaf DoschkeSoftware DeveloperCommented:
The parameter cCursorname is for a cursorname. That's litterally. You can't trick it into writing into a dbf by passing in a dbf file name.

From the help:
cCursorName
Specifies the name of the Visual FoxPro cursor to which the result set is sent. If you don't include a cursor name, Visual FoxPro uses the default name SQLRESULT.

For multiple result sets, new cursor names are derived by appending an incremented number to the name of the first cursor.
So do as Jaime suggests, and afterward put the cursor data into a table, if you really want to. Or get used to working with cursors. Cursors are already temp tables. The only difference is VFP itself cares for the file location and name, you only have to give a cursor a unique name. Just a name, no extension, no path, no space, just the same naming rules as a variable name.

Bye, Olaf.
0
 
pcelbaCommented:
One more thing - your MySQL table contains columns having names longer than 10 characters so you'll need to include the output table into database to preserve these long column names:
lcTableName = (pcTemp) + "temp_table.dbf"
lcCursor = SYS(2015) && Random cursor name
lcDatabase =  (pcTemp + "temp_database.dbc")  && You may use any other database name
lcQuery="SELECT * FROM product " + ;
"WHERE (SUBSTRING(product_model, 1, 2) = '45')"
SQLEXEC(pnConnection,lcQuery,lcCursor)
IF NOT FILE(lcDatabase)
  CREATE DATABASE (lcDatabase) && Create the database if it does not exist yet
ENDIF
SELECT (lcCursor)
COPY TO (lcTableName) DATABASE (lcDatabase)
*-- And finally close the cursor and open the table:
USE (lcTableName) 
BROWSE

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Have you tried this:

lcTableName = pcTemp + "temp_table.dbf"
SQLEXEC(pnConnection,lcQuery, lcTableName)
0
 
Olaf DoschkeSoftware DeveloperCommented:
Cyril, who do you ask?

Anyway, it won't work, you can't let SQLEXEC create a dbf file. You get a syntax error, if the cCursorname parameter of SQLExec contains any char invalid for a cursor name.

Bye, Olaf.
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Ah yes you are right, Olaf. It creates a cursor and then you have to copy to a table.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Formadmirer:

Just another thing I observe: You make use of parenthesis at places, where it's unneeded. So let me explain a bit about that, too. Perhaps you think it's needed for use as a name expression.

1. lcTableName = (pcTemp) + "temp_table.dbf"

In this case the parenthesis is not around a name, but around a path. pcTemp must be a variable containing a path to a temp directory. That's not a name, neither an alias, cursor nor file name, just a path, and won't need any parentheses. It's not a syntax error, but it is as unneeded as parenthesis is unneeded in an expression like a=(3*2)+2. You can also write lcTableName = pcTemp + "temp_table.dbf" to concatenate the pcTemp value with "temp_table.dbf".

2. SQLEXEC(pnConnection,lcQuery,(lcTableName))

As already discussed, this third parameter is taken as a cursor name only. lcTablename here contains a full qualified file name with path and file extension, this is a file name, but not a valid name for a cursor and putting it in parenthesis doesn't change this at all. Indeed parenthesis for name expression is only needed and only taken as a name expression with commands, not with functions. Here (lcTableName) is a parameter of a function, parameters can be expressions, but they are evaluated before the result values are passed as the parameters. Here the parenthesis also simply is delimiting a simple expression of one variable name, and that doesn't make it anything else but the variable value, as if you removed that parenthesis.

Places where parenthesis is needed to make clear you mean a variable name and not a field name or file name only ever occur in commands, that take a name seperated by space from other parameters or command options and what's more important than that, take it literally, not enclosed in quotes. If commands would requite names to be quoted, there would not be ambiguity and no need for name expression parenthesis at all.

Then there is another viewpoint from the type of parameter expected. Let us take a look at a function, which needs a string as a parameter, let's take LEN:
Valid uses are LEN("abc") and LEN(abc), in the first case you get 3 in the second abc is not delimited in string delimiters, which makes the parser think of it as an expression. In the first place the parser tries abc as a field name, then as a variable name. It doesn't need parenthesis around the expression to know it's meant as an expression, eg it doesn't need LEN((abc)). It's clear, because that parameter must be a string, and if the code doesn't have a string delimiter of a string literal, it must be an expression. And that also is the reason you won't get any behaviour change from putting a parameter into parenthesis, it's taken as the same expression just with an additional outer parenethesis, and the outer parenethesis of an expression always is unnneeded, the seperation already is done by the commas seperating parameters and the parenthesis around all parameters.

Bye, Olaf.
0
 
formadmirerAuthor Commented:
I wound up making use of what pcelba posted, but I wanted to thank everyone who replied, especially Olaf for taking the time to so thoroughly explain some points that I was having difficulty with. Your reply was extraordinary - thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now