Extraction error foxpro connecting to oracle database

[TheLazyPig]

Hi!

I'm creating a form where tables used are from oracle.
The Select query suppose to run in oracle but I wanted to add it to my project so the user will extract it instead of me.

This is my code for OK button

SET CENTURY OFF
SET CENTURY ON
SET DELETED ON
SET SAFETY OFF
SET UDFPARMS TO REFERENCE

CLOSE ALL
CLEAR

Public gnHandle, gnConnStr
LOCAL lcTrnsName, lcDir

SQLDisconnect(0)

**LIVE
lcTnsName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ELIFE)))"
gnConnStr = "Driver={Microsoft ODBC for Oracle};Server="+ lcTnsName +";Uid=elifedba;Pwd=elifedba;"

gnHandle = SQLSTRINGCONNECT(gnConnStr)

lcDir	 = "'C:\RECOMPUTE\RECOMPUTE2\'"
lcMonth	 = ALLTRIM(THISFORM.cboMonth.VALUE)
lcMm     = PADL(THISFORM.cboMonth.LISTINDEX, 2, "0")
lcYear   = ALLTRIM(THISFORM.txtYear.VALUE)
FrDate   = CTOD(lcMm+"/01/"+lcYear)
ToDate   = GoMonth(FrDate,1)-1 
dDate    = LEFT(DTOS(FrDate),6)

LOCAL lcSQL
TEXT TO lcSQL TEXTMERGE NOSHOW

SELECT REPLACE(parsename(b.namestr,'LFM','LFM'),';','') AS PAYEE      
  ,a.clntid      
  ,trim(d.tin) AS TIN 
  ,c.atc      
  ,CASE reftype        
    WHEN 60007           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
    WHEN 60002           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
    WHEN 60006           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))      
  END AS trantype     
  ,CASE reftype         
    WHEN 60006           
      THEN (SELECT jvno FROM xac_jvmst WHERE jvseqno = a.refno)       
    ELSE a.refno                                                                                                 
  END AS reference     
  ,a.currstatdate AS transaction_date      
  ,a.baseamt 
  ,c.rate AS percentage      
  ,a.taxamt
  ,c.dsc AS description      
  ,decode(a.reftype,60002,(SELECT purpose FROM xac_opmst WHERE opno = to_number(a.refno)),60006,(SELECT explanation FROM xac_jvmst WHERE jvseqno = to_number(a.refno)),NULL) AS particulars      
  FROM xac_taxes A      
  LEFT JOIN cnb_namelst_trn B        
    ON b.clntid = a.clntid      
  LEFT JOIN cxx_atcmst C        
    ON c.atcmst_seqno = a.atcdtl_seqno     
  LEFT JOIN cnb_clntmst D 
    ON d.clntid = a.clntid    
 WHERE 1=1      
   AND a.status = 60779       
   AND A.reftype IN (60002,60007,60006,72643)       
   AND A.currstatdate >= FrDate
   AND A.currstatdate <= ToDate + 1 - INTERVAL '1' SECOND   
   AND c.atc IN ('WI 070','WI 071')
 ORDER BY 1,2

ENDTEXT

IF MESSAGEBOX("Extract EWT for ["+lcMonth+" "+lcYear+"]?",36) = 6 
	   COPY TO lcDir+"EWT_"+dDate TYPE XLS
	   MESSAGEBOX("File [EWT_"+dDate+".xls] Created!")   
ELSE 
	   MESSAGEBOX('Extraction Cancelled')
ENDIF

I tried to run the form but it want to open a dbf file instead.

P.S I don't know why it want to open a table that's why I can't think of a proper name to my thread. Sorry

Thank you!

 

[Olaf Doschke]

You prepared the SQL query in lcSQL, but didn't SQLEXEC(). How should there be a cursor you can COPY TO? When you try to COPY TO from an empty workarea you will get a table prompt dialog. Or, as you SET TABLEPROMPT OFF, the error message you show: "No table is open in the current work area."

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

Something like this:

SET CENTURY OFF
SET CENTURY ON
SET DELETED ON
SET SAFETY OFF
SET UDFPARMS TO REFERENCE

CLOSE ALL
CLEAR

Public gnHandle, gnConnStr
LOCAL lcTrnsName, lcDir

SQLDisconnect(0)

**LIVE
lcTnsName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ELIFE)))"
gnConnStr = "Driver={Microsoft ODBC for Oracle};Server="+ lcTnsName +";Uid=elifedba;Pwd=elifedba;"

gnHandle = SQLSTRINGCONNECT(gnConnStr)

lcDir	 = "'C:\RECOMPUTE\RECOMPUTE2\'"
lcMonth	 = ALLTRIM(THISFORM.cboMonth.VALUE)
lcMm     = PADL(THISFORM.cboMonth.LISTINDEX, 2, "0")
lcYear   = ALLTRIM(THISFORM.txtYear.VALUE)
FrDate   = CTOD(lcMm+"/01/"+lcYear)
ToDate   = GoMonth(FrDate,1)-1 
dDate    = LEFT(DTOS(FrDate),6)

LOCAL lcSQL
TEXT TO lcSQL TEXTMERGE NOSHOW

SELECT REPLACE(parsename(b.namestr,'LFM','LFM'),';','') AS PAYEE      
  ,a.clntid      
  ,trim(d.tin) AS TIN 
  ,c.atc      
  ,CASE reftype        
    WHEN 60007           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
    WHEN 60002           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
    WHEN 60006           
      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))      
  END AS trantype     
  ,CASE reftype         
    WHEN 60006           
      THEN (SELECT jvno FROM xac_jvmst WHERE jvseqno = a.refno)       
    ELSE a.refno                                                                                                 
  END AS reference     
  ,a.currstatdate AS transaction_date      
  ,a.baseamt 
  ,c.rate AS percentage      
  ,a.taxamt
  ,c.dsc AS description      
  ,decode(a.reftype,60002,(SELECT purpose FROM xac_opmst WHERE opno = to_number(a.refno)),60006,(SELECT explanation FROM xac_jvmst WHERE jvseqno = to_number(a.refno)),NULL) AS particulars      
  FROM xac_taxes A      
  LEFT JOIN cnb_namelst_trn B        
    ON b.clntid = a.clntid      
  LEFT JOIN cxx_atcmst C        
    ON c.atcmst_seqno = a.atcdtl_seqno     
  LEFT JOIN cnb_clntmst D 
    ON d.clntid = a.clntid    
 WHERE 1=1      
   AND a.status = 60779       
   AND A.reftype IN (60002,60007,60006,72643)       
   AND A.currstatdate >= FrDate
   AND A.currstatdate <= ToDate + 1 - INTERVAL '1' SECOND   
   AND c.atc IN ('WI 070','WI 071')
 ORDER BY 1,2

ENDTEXT

IF MESSAGEBOX("Extract EWT for ["+lcMonth+" "+lcYear+"]?",36) = 6 
	   SQLEXEC(gnHandle, lcSQL , 'MyCursor')
	   COPY TO lcDir+"EWT_"+dDate TYPE XLS
	   MESSAGEBOX("File [EWT_"+dDate+".xls] Created!")   
ELSE 
	   MESSAGEBOX('Extraction Cancelled')
ENDIF

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]

I would take it a little bit further:

IF SQLEXEC(gnHandle, lcSQL , 'MyCursor') > 0
  COPY TO lcDir+"EWT_"+dDate TYPE XLS
  MESSAGEBOX("File [EWT_"+dDate+".xls] Created!")
ELSE
  MESSAGEBOX("Could not retrieve data")
    && or, better, call your error-handler
ENDIF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I thought you just forgot the SQLEXEC call, but there you have that example from Mike and Griff.

To be able to use Foxpro variables (FrDate, ToDate) in the query you will need to prepend a question mark in the query text, ie [tt]A.currstatdate >= [highlight #FCE94F]?[/highlight]FrDate[/tt]. Oracle has no access to the VFP variable, they are "passed through" to Oracle. But not merely when their name appears, it could be any Oracle field name, too.

The question mark also enables you to use VFP functions at that place, eg you can query for Orclaetable.datefield = ?DATE(). In the case of MSSQL backends, a VFP date or datetime variable comes through as a datetime type in the MSSQL server, it should work with Oracle. too.

If you already tried Griffs' and/or Mikes' suggestion you likely have frustratingly encountered that you don't get a result back. And there the retrieval of error information becomes important. Here AERROR() is the key. An error handler does not get triggered if Oracle can't execute your query, as no error is thrown in the VFP process, SQLEXEC just informs an error happened by returning -1 and AERROR() will retrieve the information the ODBC Driver got back from Oracle.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

And there the retrieval of error information becomes important. Here AERROR() is the key. An error handler does not get triggered if Oracle can't execute your query, as no error is thrown in the VFP process

Just to expand that point, here is how I deal with that situation:

lnReply = SQLEXEC(lnConnectionHandle, lcCommand, "csrResults")
IF lnReply < 0
  AERROR(laError)
  ERROR "Database error : " + laError(3)
ENDIF

That way, the error gets handled by my standard error-handler, which typically logs the error and notifies the developer.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TheLazyPig]

Thank you for the replies.

I re-construct my query.

 SET CENTURY OFF
SET CENTURY ON
SET DELETED ON
SET SAFETY OFF
SET UDFPARMS TO REFERENCE

CLOSE ALL
CLEAR

PUBLIC gnConnStr, lcSQL
LOCAL lcTrnsName, lcDir
*----------------------------------
lcDir	 = "'C:\RECOMPUTE\RECOMPUTE2\'"
lcMonth	 = ALLTRIM(THISFORM.cboMonth.VALUE) 
lcMm     = PADL(THISFORM.cboMonth.LISTINDEX, 2, "0")
lcYear   = ALLTRIM(THISFORM.txtYear.VALUE)
FrDate   = CTOD(lcMm+"/01/"+lcYear)
ToDate   = GoMonth(FrDate,1)-1 
dDate    = LEFT(DTOS(FrDate),6)
*----------------------------------

SQLDISCONNECT(0)

PRIVATE gcOraHost, gcOraSrvc, gcOraUser, gcOraPass, lcTnsName
gcOraHost = [192.168.0.154]
gcOraSrvc = [ELIFE]
gcOraUser = [elifedba]
gcOraPass = [elifedba]

lcTnsName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="+ gcOraHost +")(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME="+ gcOraSrvc +")))"
gnConnStr = "Driver={Microsoft ODBC for Oracle};Server="+ lcTnsName +";Uid="+ gcOraUser +";Pwd="+ gcOraPass +";"
gnHandle = SQLSTRINGCONNECT(gnConnStr)

IF  isBlank(lcMonth) OR ;
    isBlank(lcYear) 
    MESSAGEBOX("Invalid Input.")
ELSE

lcSql = ""
TEXT TO lcSQL TEXTMERGE NOSHOW ADDITIVE
	SELECT REPLACE(parsename(b.namestr,'LFM','LFM'),';','') AS PAYEE     
	  ,a.clntid      
	  ,trim(d.tin) AS TIN 
	  ,c.atc      
	  ,CASE reftype        
	    WHEN 60007           
	      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
	    WHEN 60002           
	      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))       
	    WHEN 60006           
	      THEN substr(fngetrefdesc(a.reftype),0,instr(fngetrefdesc(a.reftype),' -'))      
	  END AS trantype     
	  ,CASE reftype         
	    WHEN 60006           
	      THEN (SELECT jvno FROM xac_jvmst WHERE jvseqno = a.refno)       
	    ELSE a.refno                                                                                                 
	  END AS reference     
	  ,a.currstatdate AS transaction_date      
	  ,a.baseamt 
	  ,c.rate AS percentage      
	  ,a.taxamt
	  ,c.dsc AS description      
	  ,DECODE(a.reftype,60002,(SELECT purpose FROM xac_opmst WHERE opno = to_number(a.refno)),60006,(SELECT explanation FROM xac_jvmst WHERE jvseqno = to_number(a.refno)),NULL) AS particulars  
	  FROM xac_taxes A      
	  LEFT JOIN cnb_namelst_trn B        
	    ON b.clntid = a.clntid      
	  LEFT JOIN cxx_atcmst C        
	    ON c.atcmst_seqno = a.atcdtl_seqno     
	  LEFT JOIN cnb_clntmst D 
	    ON d.clntid = a.clntid    
	 WHERE 1=1      
	   AND a.status = 60779       
	   AND A.reftype IN (60002,60007,60006,72643)       
	   AND A.currstatdate >= TO_DATE('<<DTOS(FrDate)>>','YYYYMMDD')
	   AND A.currstatdate <= TO_DATE('<<DTOS(ToDate)>>','YYYYMMDD') + 1 - INTERVAL '1' SECOND   
	   AND c.atc IN ('WI 070','WI 071')
	 ORDER BY 1,2
ENDTEXT
	IF SQLEXEC(gnHandle, lcSQL , 'MyCursor') > 0
	   *COPY TO lcDir+"EWT_"+dDate TYPE XLS]COPY TO lcDir+"EWT_"+dDate TYPE XLS
	   COPY TO "C:\RECOMPUTE\RECOMPUTE2\EWT_"+dDate TYPE XLS
	   MESSAGEBOX("File [EWT_"+dDate+".xls] Created!")
	ELSE
	   MESSAGEBOX("Extraction Cancelled.")
	   =AERROR(arrErr)
	   ?arrErr(2)
	ENDIF 	
ENDIF

I got an output but some of the data didn't retreive like the first column.

Also,

 COPY TO lcDir+"EWT_"+dDate TYPE XLS

Does not work so I had to copy the whole path to make it work.

PS. Does function from Oracle can be use in vfp?

 

[Mike Lewis]

Does function from Oracle can be use in vfp?

No. At least, you cannot simply call an Oracle function within your VFP code. But there might be a VFP function that does the same thing.

That said, you can include Oracle functions in the command that you are sending via SQLEXEC(). In fact, that it is exactly what you are doing with your REPLACE() and TO_DATE() functions. This is perfectly normal.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

This bit of your code isn't quite right, for several reasons:

IF SQLEXEC(gnHandle, lcSQL , 'MyCursor') > 0
  *COPY TO lcDir+"EWT_"+dDate TYPE XLS]COPY TO lcDir+"EWT_"+dDate TYPE XLS
  COPY TO "C:\RECOMPUTE\RECOMPUTE2\EWT_"+dDate TYPE XLS
  MESSAGEBOX("File [EWT_"+dDate+".xls] Created!")
ELSE
  MESSAGEBOX("Extraction Cancelled.")
  =AERROR(arrErr)
  ?arrErr(2)
ENDIF

First, the "Extraction Cancelled." message does not apply here. The message will be triggered after an error, not after the user chooses to cancel the process, as per your original code.

[tt]?arrErr(2)[/tt] will output the error message to the active window. This is almost certainly not what you want. You will have no control over where the message appears within the window, and it might not even be visible to the user. Also, the program will continue to run following the error, which again is probably not what you want.

And in any case, [tt]arrErr(2)[/tt] will simply return the standard ODBC error message, which is something like "Connectivity error". It doesn't tell you what went wrong. For that, you need [tt]?arrErr(3)[/tt].

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

COPY TO lcDir+"EWT_"+dDate TYPE XLS

Does not work so I had to copy the whole path to make it work.

I think that's because you have included an extra set of delimiters in your directory name:
[tt]
"'C:\RECOMPUTE\RECOMPUTE2\'"[/tt]

So your output directory will be something like:

[tt][highlight #FCE94F]'[/highlight]C:\RECOMPUTE\RECOMPUTE2\[highlight #FCE94F]'[/highlight]20200[/tt]6

which is obviously not correct.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You execute the query within the Oracle database you can use Oracle functions there. Just like you can use T-SQL functions within a connection to MSSQL Server. And sorry, Mike, what you say about that topic and where what works is absolutely right.

Short answer: Yes. Indeed the VFP syntax for queries INTO CURSOR , INTO ARRAY, etc is NOT valid, [highlight #FCE94F]you can ONLY execute Oracle queries[/highlight].

And actually COPY TO something.xls works, you just need to change directory into the directory you want the output to go to. Then there is no need to specify the path. On the other side, if you want to be nice to whatever other code staying with its default current directory, you can easily specify file names in any expression you put in brackets: COPY TO (lcDir+"ENT_"+dDate) TYPE XLS. Unless dDate is a date, but I see you use DTOS() already.

So finally use

COPY TO (lcDir+"ENT_"+dDate) TYPE XLS

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TheLazyPig]

I have another question.

If I will create a function for my Oracle Connection so I'll just call it to some of my forms. Will I use SQLEXEC() to call my query for SELECT or it will be different?


Thank you!

PS. Will I be able to ask question to an existing thread with the same name/topic of my problem?

 

[TheLazyPig]

No. At least, you cannot simply call an Oracle function within your VFP code. But there might be a VFP function that does the same thing.

If I'll create a function will I be able to use that function to my SELECT query.

Like this...

 SELECT reftype,[b]FUNCTIONNAME[/b] FROM taxes

Thank you!

 

[Olaf Doschke]

It's okay to go on with a detail question in a thread. Indeed it would make it overly complicated to reestablish the context i a new thread.

Your question points out you still don't have an idea when you can use which language where and how.

Overall the simplest model of SQLEXEC is, it sends an SQL language string to a remote database, in your case Oracle, and that executes Oracle SQL dialect including Oracle functions only.

VFP comes into play, as I said, in parameters. To understand that, you have to realize of course VFP does no magic. It can't extend the remote database with VFP language capabilities, but I said ?DATE() is an example that would work, using VFPs DATE() function, which gives the current date.

Well, how can this be? It can be made because this happens as a preparation step before the query is sent over to Oracle.

And that means, you can't run things like SELECT foxprofunction(field) FROM Oracletable. The preparation happens once before the query runs. so you can only SELECT field and need to run this through foxprofunction(field) as second pass aftermath and postprocessing, when you have the result back from Oracle.

Also, ? is not acting as macrosubstitution or textmerging. The remote backend really gets a parameter in the statement sent over, so rules for where you're able to place parameters apply, What you cant do, for example is:

SELECT * FROM ?m.lcTable

Becauase if you put this in Oracle syntax where parameters are denoted with a : prefix, this is NOT working:

SELECT * FROM :lcTable

Why? What does VFP do? When lcTable is the string 'xac_taxes' - the Oracle table name from your example. VFP does not just put this name into the query before sending it over. It does change the query to SELECT * FROM :lcTable and sends over the value for ;kcTable separately, And as said that won't work, because no database I know of allows to dynamically change the tables in the FROM clause by query parameters.

You can do what your example did in your example here:

...
AND A.currstatdate >= ?FrDate
....

Because that will turn into

...
AND A.currstatdate >= :FrDate
,,,

for Oracle and the :FrDate parameter value also is automatically passed by SQLEXEC. So what actually runs in the end in Oracle is not just the query but parameters are declared and set to the values VFP passes in and then a prepared parameterized statement is executed.

How does VFP know all the different ways any database parameterized queries? It doesn't. VFP's side of using the ODBC driver is using ODBC syntax for parameterized queries and since the ODBC driver is database-specific it knows how to translate that into the remote server syntax. And this is also the moment, where the VFP specific data types are converted to ODBC types and then again towards the backend. But there can be transfer or translation problems, when there is no corresponding type in all three layers of VFP, ODBC, and the remote database. That's why I said ?FrDate using a VFP date likely works, but also might fail.

And the more general consequence of this all is, you can't just copy over your VFP queries as you used them with Foxpro functions and execute them in Oracle.

When up to now you did something like this:

SELECT STRTRAN(field,'bob','alice')

That does not translate to Oracle, neither as is nor putting this as a parameter, your parameter expressions are evaluated once in preprocessing and not per record of the query running. So to be able to do a similar query in Oracle you have to find the equivalent Oracle function for STRTRAN.

Even worse, when using functions you defined with FUNCTION myfunction and SET PROCEDURE TO or SET PATH/ SET DEFAULT TO to "extend" the VFP language. You're not bringing this into Oracle with the SQL connections. It's just a "phone line" to Oracle server.

And on the other end of it, of course, you can make use of user-defined functions of the Oracle database you're connected to. The query is executed by the Oracle server. It does know Oracle functions including user-defined ones. But also just in SQLEXEC, not outside of it. Oracle and VFP are still separate processes.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

I dom't know if you do if you do web programming and PHP, because there would be an easy to understand similar thing with Javascript used in the HTML PHP prepares in its response: Javascript is not executed when the PHP script runs, it's just returned to the Browser and executed there.

And even more similar: PHP does not include MySQL SQL in its language. If you ever did a PHP script executing a MySQL query, it does very much the same as VFPs SQLEXEC is with mysqli_execute() and the only reason you can say PHP is more tightly integrated with MySQL is that mysqli_execute() itself is a PHP function. But it's not executing MySQL queries in the PHP process, it's just an SQLEXEC specialized on MySQL connections. PHP was extended with PDO which does what VFPs passthrough functions can do, it can use ODBC drivers to connect to any database and execute queries in them by forwarding them through these drivers.

And last not least connecting to Oracle means neither Oracle is integrated into VFP nor vice versa, you can't run Oracle queries in VFP code nor VFP queries in Oracle server. REalize that TEXT..ENDTEXT is not executing a query it's just the assignment of text into a variable - here lcSQL. It only is a string. It's just a more comfortable way instead of lcSQL = 'querytext' and TEXT..ENDTEXT can apply text merging functionalities when assigning the query text into lcSQL. This step just makes it more comfortable to write muilti-line strings as queries usually are when they become a little bit more involved than just SELECT * FROM Table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Last not least, I don't know whether you manged to get data already. I didn't see you screen "success".

So here's a starting point, that should work based on the assumption the connection works. So just the code following that line making the connection and setting gnHandle:

* code shortened
* ...
gnHandle = Sqlstringconnect(gnConnStr)

* start simple, expand once it works
lcSql = ""
Text TO lcSQL Textnerge NoShow Additive
   SELECT TOP 10 *
      FROM xac_taxes A
      ORDER BY A.currstatdate DESCENDING
EndText

If SQLExec(gnHandle, lcSql , 'MyCursor') > 0
   Local lcFiledate
   lcFiledate = Dtos(Date())
   Select MyCursor
   Copy To ("C:\RECOMPUTE\RECOMPUTE2\EWT_"+lcFiledate) Type Xls
   Messagebox("File [EWT_"+lcFiledate+".xls] Created!")
Else
   Messagebox("Extraction Failed.")
   =Aerror(arrErr)
   lcMessage=''
   For Each vItem In arrErr
      lcMessage = lcMessage + Transform(vItem) + Chr(13)+Chr(10)
   Endfor
   ? lcMessage
Endif
SQLDisconnect(gnHandle)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

One more final thought: If the connection doesn't work you'd get an invalid handle (gnHandle<1), then you also get error information from AERROR after the SQLStringConenct call.

And it might be easier to use the (32bit!) ODBC manager to establish a DSN and use that in VFP with gnHandle=SQLCONNECT("DSNName").
You might want to suppress a dialog to prompt for credentials: SQLSetProp(0,"DispLogin",[highlight #FCE94F]3[/highlight]) before making the connection.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name