Visual FoxPro/Oracle Question: How to pull data into Foxpro from Oracle Database 4

[Allwolfpackfan]

Visual FoxPro/Oracle Question: I need to pull table data from an Oracle database into Visual FoxPro (Version 6.0). I need it to query based on 4 different fields but there are 2 fields it has problems with. Here is the code: Mserver, msuer, mpass, mconnect are all memory variables.

lnConn = SQLCONNECT((mserver), (muser), (mpass))
IF m.lnConn > 0 && Success.
WAIT "Connected" WINDOW NOWAIT
* Set the active database
SQLEXEC(m.lnConn, (mconnect))
* Execute SELECT statement.
SQLEXEC(m.lnConn, 'SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT FROM WC20.VCPRMTL where MTLTYPE=01 AND YEARNO>=2018')
BROWSE
SQLDISCONNECT(m.lnConn)
ELSE
WAIT "Unable to connect to SQL Server" WINDOW
ENDIF
RETURN
=================================================================
The code above works perfectly but I need the Select Statement to include 2 more fields in the where clause. It needs to read like this:
SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT FROM WC20.VCPRMTL where MTLTYPE=01 AND YEARNO>=2018 AND STOCKID=M AND PRODUNIT=M
But whenever I add STOCKID or PRODUNIT into the Select statement it returns no records. I cannot figure out why it works perfectly without those 2 but bombs out every time if I add either of those fields into the where clause. Any help would be greatly appreciated.
Tim

 

[GriffMG]

If M is another memory variable :

SQLEXEC(m.lnConn, 'SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT FROM WC20.VCPRMTL where MTLTYPE=01 AND YEARNO>=2018 AND STOCKID=' + TRANSFORM(M)+' AND PRODUNIT='+TRANSFORM(M))

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Presumably your Stock ID and Product ID are character fields. If that's the case, you need to use delimiters around the values (the value M in your example). In other words, instead of:

.... STOCKID=M AND PRODUNIT=M  ....

you need :

... STOCKID= [b]"[/b]M[b]"[/b] AND PRODUNIT = [b]"[/b]M[b]"[/b] ....

If you don't do that, Oracle will look for a field whose name is M, which I assume is not what you intended.

Note that I have used double-quotes for the delimiters in the above example. That's what I would do in VFP, but Oracle might need a different delimiter, such as single-quotes. Check your Oracle documentation for that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Also, it's always a good idea to test the value returned by SQLEXEC(). If it is negative, it indicates that the back-end has detected an error, and will therefore not return any results. This is different from the case where there are simply no records that match your WHERE clause.

If SQLEXEC() returns a negative number, use AERROR() to return the error code and error message. These will be Oracle errors, not VFP. If the message is not self-explanatory, refer to your Oracle documentation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Allwolfpackfan]

Hi Guys, Neither of those solutions work. Griff, M is not a memory variable.
Mike, STOCKID and PRODUNIT are both character fields BUT MTLTYPE is also a character field. MTLTYPE works fine without quotes but none of them work if I use single or double quotes. I notice FoxPro does create the cursor with the correct data type fields. It does allow me to add more criteria to the Where clause IF it's a numeric field BUT I can't get any of the character fields to pull except for the MTLTYPE=01.

 

[atlopes]

Allwolfpackfan,

If MTLTYPE is of character type, then MTLTYPE = 01 executes an implicit conversion from number to char.

If the SQLExec() returns a well-formed cursor, then everything is working as it should, and it is not "bombing out": an error condition wouldn't return a cursor, and certainly not one that would correspond to the selected columns.

So, if M is not a memory variable, what is it, then? If your WHERE clause includes STOCKID=M, then M must be an object that Oracle understands, a column, for instance. Certainly, it is not a literal character expression. If it were, it should be enclosed by single quotes, or encoded under the alternative encoding mechanism.

 

[Allwolfpackfan]

Atlopes, I think you misread what I originally stated. When the where clause says:
where MTLTYPE=01 AND YEARNO>=2018 it works fine and returns 77,154 records..... BUT
When the where clause says:
where MTLTYPE=01 AND YEARNO>=2018 AND STOCKID=M AND PRODUNIT=M
it returns nothing...… ie, it bombs out.
I know 77,154 records is correct because I can pull it using MSQuery via Excel with no problem.
If I use:
where MTLTYPE="01" AND YEARNO>=2018 bombs out and returns nothing
where MTLTYPE='01' AND YEARNO>=2018 errors out saying Function name is missing ).
so the only way I can get it to work is
where MTLTYPE=01 AND YEARNO>=2018 without any quotes.

STOCKID AND PRODUNIT are both character fields with M being what is populated in that field...….
STOCKID can be M, 4 or 7..... in this case I need STOCKID=M
PRODUNIT can be M, G, 1, Z, or any letter or number depending on which production unit I need. In this case I need PRODUNIT=M
I thought about storing the entire string to a memory variable and trying that but was hoping I could get to work without having to do that.
Sorry for the confusion.

 

[Mike Lewis]

Yes, exactly as Atlopes says. The only way for[tt] WHERE ... PRODUNIT=M[/tt] to be valid syntax is if M is a column in the Oracle table. If it is not that, what is it?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

it returns nothing...… ie, it bombs out.

Please be more precise. "Bombing out" is not the same as returning nothing. It is perfectly possible for the cmmmand to execute correctly and to return a valid cursor, but for the cursor to be empty. That is not what most of us would understand by "bombing out".

mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Swap your delimiters a bit then:

SQLEXEC(m.lnConn, "SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT FROM WC20.VCPRMTL where MTLTYPE='01' AND YEARNO>=2018 AND STOCKID='M' AND PRODUNIT='M'")

I am assuming YearNo is a numeric.

VFP will understand the double quotes, and I reckon Oracle will understand the single quotes to delineate a string constant.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Allwolfpackfan]

PRODUNIT is a Character field. M is one of the acceptable string/values in that character field..... it could be any letter or number
Bombs out means it returned no records but should have returned records. Ie, it executes the command without displaying an error on the screen during execution. Hope that helps clarify the issue.

 

[Allwolfpackfan]

Griff, yes YEARNO is a numeric. I've used FoxPro since the 90's back when the first MS Dos for FoxPro came out. Double quotes work great for it BUT it's a whole different animal trying to use SQLEXEC.

 

[Olaf Doschke]

You need to find out how to delimit strings in Oracle. In MSSQL double quotes are not string delimiters, only single quotes. As far as I remember it's the same with Oracle.

As you put together your SQL in VFP in single quote delimited strings you have a hard time putting in single quoted. Single quotes end your VFP strings, they don't get into the string.

You have four ways in VFP to write out strings, single quotes, double quotes, square brackets and TEXT..ENDTEXT.

Even Griff had that wrong, as Mike saw.

The easiest way to prepare SQL is with TEXT..ENDTEXT as it even allo0ws writing complex queries in multiple lines.

You can strip out CHR(13) and CHR(10) before sending it to Oracle, but it might not even be necessary. It's not a good idea to write your SQL directly into SQLEXEC calls, as that has the small 254 character limit of string literals in source code, too, and queries get longer quite fast. Get real about this, prepare your SQL in a variable, then you can also ? it to screen or write it into a file or put it into _cliptext to see what you're sending over to Oracle.

LOCAL lnConn, lcSQL
...your code despite SQLEXEC
TEXT TO lcSQL TEXTMERGE NOSHOW
SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT 
FROM WC20.VCPRMTL 
WHERE MTLTYPE=1 AND YEARNO>=2018 AND STOCKID='M' AND PRODUNIT='M'
ENDTEXT
SQLEXEC(m.lnConn, lcSQL,"crsOracleData")
BROWSE
...

And whether that works with single quoted 'M' is depending on Oracle, not VFP. You have to know what Oracle needs as string delimiters.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides that, Mike already gave you an important advice, you have to look for erros yourself, SQLEXEC won't throw an error in the VFP process when the SQL you send causes an exception in the Oracle process running elsewhere on a server. Even if you're working on the Oracle server itself, it's another process executing your query, so indeed, as told within thread184-1794233 and other recent threads:

LOCAL lnReturnValue && somewhere in the variable declaration section of your code
....
lnReturnValue = SQLEXEC(....your parameterization, can vary a lot...)
If lnReturnValue<0
   AERROR(paError)
   SET STEP ON
Endif

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Allwolfpackfan,

If M is a value you're looking for in the PRODUNIT column, then the Oracle syntax for the comparison is PRODUNIT = 'M'. If you're getting syntax errors on this, then there is an error somewhere else in your SQL statement.

If you want a few extra pair of eyes to have a look at it, I advise you to paste here exactly the statement you're using and it is raising an error, or what you believe is returning an incorrect result. Please, put the statement inside a code markup.

 

[vernpace]

Same as SQL Server, Oracle requires single quotes for character data type: 'M'

The reason you got an error is that your statement 'SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, ...' was already wrapped with a single quote. You can try wrapping your statement in double quotes and then use 'M'.

Better yet, take Olaf's advice and use TEXT...ENDTEXT. This is what I do - it gives you MUCH more flexibility. I use it to UPDATE, DELETE, INSERT, create databases, create tables, drop databases, drop tables, database backups, log backups... with the right permissions, you can do anything. Furthermore, you can have multiple statements inside the same TEXT...ENDTEXT structure.

TIP: Consider parameterization for even more flexibility. Using Olaf's example:

liMtlType = 1
liYear = 2018
lcStockID = "M"
lcProdID = "M"

TEXT TO lcSQL TEXTMERGE NOSHOW
SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT 
FROM WC20.VCPRMTL 
WHERE MTLTYPE = <<liMtlType>> AND YEARNO >= <<liYear>> AND STOCKID = '<<lcStockID>>' AND PRODUNIT = '<<lcProdID>>'
ENDTEXT
SQLEXEC(m.lnConn, lcSQL,"crsOracleData")

 

[atlopes]

Just two clarification remarks, one a bit off-topic, since regards MSSQL, and the other not so much.

The use of double quotes in MSSQL as a string literal delimiter depends on the QUOTED_IDENTIFIER setting. When it is OFF, single and double quotes are interchangeable, but since this setting imposes a series of limitations, it's better to leave it ON (which is the ODBC default).

As for parametrization, it involves the use of an expression evaluated on the VFP side and passed as a parameter to the ODBC driver. The mark of the parameter is a question mark, followed by an expression, which can be anything, including a literal (I don't know if @vernpace intended to exemplify a parameterized statement, but the example is of a regular non-parameterized statement).

A parameterized statement would look something like

LOCAL LookingFor AS String

m.LookingFor = "M"

SQLEXEC(m.lnConn, 'SELECT * FROM WC20.VCPRMTL WHERE PRODUNIT = ?m.LookingFor')

But it could also be something like this:

SQLEXEC(m.lnConn, 'SELECT * FROM WC20.VCPRMTL WHERE PRODUNIT = ?"M"')

In both cases, VFP will wrap the parameter value nicely before delivering it to the ODBC driver.

 

[Olaf Doschke]

Yes, that's a good example of vernspace what TEXTMERGE is good for.

You can also parameterize "the real" way, without substituting in the values into the lcSQL string:

LOCAL lnConn, lcSQL, lcStockID
...your code despite SQLEXEC
TEXT TO lcSQL NOSHOW
SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT 
FROM WC20.VCPRMTL 
WHERE MTLTYPE=1 AND YEARNO>=2018 AND STOCKID=?lcStockID
ENDTEXT
lcStockID = 'M'
SQLEXEC(m.lnConn, lcSQL,"crsOracleData")
BROWSE
...

This time I actually removed the TEXTMERGE option. It's not necessary, the lcStockID variable value is passed in by SQLEXEC. This way you also don't need to know about what delimiters Oracle uses.
SQLEXEC passes that to the used ODBC driver as parameterized query in standard ODBC syntax and the ODBC driver turns that into the remote database syntax for parameterized queries, which can differ, in MySQL that simply ? for any parameter, in MSSQL you'd have @varname and in Oracle I don't know, but from the VFP side you can use ?varname or ?m.varname and even ?(VFP expression) with the expression bracketed, in that case even using VFP functions.

There's just one downside of it: If you have a general remote SQL database query class and a method for executing SQL, a wrapper around the SQLEXEC() function, which you then call by something like loMySQLClass.Exec(lcSql, lcResultaliasname) then that SQL can't contain local variable names, as, of course, they are not visible within the exec method of your class. So either you define SQL parameter variables private or use other "trickery", eg a property, array, or anything in scope within the class as THIS.

In your case you can use that concept, too, as you for now have all in one place anyway from connecting over building up the SQL and executing it and looking at the result. Within a single script you don't have any scoping considerations. And scope is a valuable topic in itself.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Allwolfpackfan, do you have access to the Oracle user interface? In other words, can you type commands directly into Oracle and immediately see the results on the screen?

If so, paste your SQL into the interface. And let us know what happens when you run it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Allwolfpackfan]

Hi Guys,
Treating the SQL statement as a memory variable worked. I could not get TEXT ENDTEXT to a memory variable to work so just did a normal store and it worked fine. Griff, Olaf, Mike, atlopes, vernpace YOU GUYS ARE AWESOME!!! Thank you much for the tips.

mserver, muser, mpass, msql, lnConn all memory variables.
Here is the final code:

Restore from c:\programs\MCONNECT.mem ADDITIVE
lnConn = SQLCONNECT((mserver), (muser), (mpass))
store "SELECT ARTCDPACK, COMPTS, MTLTS, MTLQTY, MTLTYPE, DAYNO, WEEKNO, YEARNO, STOCKID, PRODUNIT FROM WC20.VCPRMTL where MTLTYPE='01' AND YEARNO>=2018 AND PRODUNIT='M' AND STOCKID='M'" to msql
IF m.lnConn > 0 && Success
WAIT "Connected" WINDOW NOWAIT
SQLEXEC(m.lnConn, (mconnect)) && set the active database
SQLEXEC(m.lnConn, mSQL) && execute actual query
BROWSE
SQLDISCONNECT(m.lnConn)
ELSE
WAIT "Unable to connect to SQL Server" WINDOW
ENDIF
RETURN