SQL Pass Through

[risslsu]

How do I reference a remote table so that I can use a SELECT statement and put the results into a cursor? I have already established my connection with the server.(SQL Server)

This is my code using a local table:
SELECT TOP 1 * FROM table INTO CURSOR table_cur ;
WHERE ALLTRIM(table.field1) == m.field1 ;
ORDER BY table.field1

Should I use SQLEXEC, SQLPREPARE?

Thank you,
Marissa

 

[Mike Gagnon]

risslu

This is what I use to connect to an SQL database

csqlstr="SELECT ARTCP.CNTBTCH, ARTCP.CODEPAYM "+;
		"FROM ARTCP Artcp "+;
		"WHERE ARTCP.CODEPAYM='AD'  GROUP BY ARTCP.CNTBTCH,ARTCP.CODEPAYM "+;
		"ORDER BY ARTCP.CNTBTCH DESC"
	Store SQLConnect(LCACCPACDATA, 'sa') To GNCONNHANDLE
	If GNCONNHANDLE<=0
		Return
	Else
		= SQLSETPROP(GNCONNHANDLE, 'asynchronous', .F.)
		= SQLEXEC(GNCONNHANDLE, CSQLSTR, 'viewaccpacwo')
	Endif
	= SQLDISCONNECT(GNCONNHANDLE)

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Marissa,

The short answer is that you should use SQLEXEC(), not SQLPREPARE().

First, you need to get a connection handle, which you do with SQLCONNECT(). Then call SQLEXEC(), passing that connection handle and your SQL command. That will give you the results in a cursor, called SQLResult. Finally, call SQLDISCONNECT() to close the connection.

However, you will need to modify your SELECT statement, as you have used VFP SQL syntax, which SQL Server doesn't understand. Specifically:

- Remove the INTO CURSOR clause. This is redundant.

- Change ALLTRIM() to LTRIM(RTRIM()). SQL Server does not support ALLTRIM().

- Don't include the m. in front of Field1. Instead, put a ? in front of it, to tell SQL Server that it is a parameter. SQL Server cannot see VFP variables.

- Change the '==' to '='.

Actually, I'm not sure if you should be trimming the field at all. It depends on whether the target field on the server is a Char or a Varchar. You might want to experiment with this.

Hope this gives you a start.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[mcoupal]

I have spent 3 hours trying to figure this out. I can get my SQL command to run while in VFP 6 and VFP8, but when I run it as a compiled .EXE, it fails to populate the cursor.

I have a long SQL statement (longer than 255 characters) that must be broken up. I have it set to VFP7 compatibility, too using --> SYS(3099, 70)

----------------


cSQL = "SELECT Prpern.*, MNM25, MSSNO, MEECL, MA25A, MA25B, MCITY, MST, MZIP, MDTBR, MDTHR, MDTTE, MDTBG " +;
"from prpern, prpmst " +;
"where eryr=2004 and ercono=01 and ereeno=meeno AND ((erdino between 401 and 418) OR (erdino between 421 and 428) " && OR '

cSQL2 = "or (erdino=325) or (erdino=441) or (erdino=450) or (erdino=451) or (erdino=455) or (errccd='A') or " +;
"(errccd='F') or (erdino between 501 and 518))"

= SQLSETPROP(gnHandle, 'asynchronous', .F.)

ln = SQLEXEC(1, cSQL + cSQL2,'curresults')

--------------------

Any ideas?
Thanks,
Mike

 

[Mike Lewis]

Mike,

Couple of points off the top of my head:

- SYS(3099) won't make a scrap of difference. That command is used for the VFP database engine, not the remote server's.

- I can't quite figure out what that && is for. Can you clarify? Is it possible that VFP is seeing it as comment delimiter?

- As you have it running asynchronously, you need to test the reply from SQLEXEC() in a loop. Basically, you need to wait until it is no longer returning 0. Also, you could try taking out that line (the SQLSETPROP()) and seeing what difference that makes.

Finally, when you reply, it would be helpful to know what back-end you are using.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[denabates]

Ok I have my application running fine but I keep getting the blank foxpro cursor boxes with the SQLRESULT at the bottom. Is there any way to turn this off so the user will not have to close this box out? Thanks to anyone for their help.
Dena Bates
Jackson, MS

 

[Mike Lewis]

Dena,

You seem to have jumped into the middle of a thread. Is your question somehow related to the ones asked earlier by Rissisu and MCoupal?

I'm sure we will be able to help you, but you need to explain the context.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[denabates]

Sorry about not explaining everything. Yes it's related in that I am using a SQL pass through on a foxpro application I have developed. My problem is my application brings up a SQLRESULT box which is blank (does not need info in it) and I wanted to turn this off so my users will not have another box to close down. Any suggestions?
Thank you
Dena

 

[Mike Gagnon]

Dena

I think Mike is suggesting that if you have an issue, that is obviously diffenrent for the original poster's question "How do I reference a remote table so that I can use a SELECT statement and put the results into a cursor?", start an new thread with your question and it will get all the attention it deserves.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Dena,

OK, so the next question is: what do you mean by a "SQLRESULT box"? Do you mean a browse window? Or a data session window? Or what?

If you could describe this "box", we might be able to suggest a solution.

For what it's worth, SQLResult is the default name of the cursor which is returned by SQL pass-through.

Aa Mike Gagnon said, it would be a good idea to start a new thread for your question. When you do, if you could give as much background information as possible, that would be helpful.

Finally, you might consider posting the question in "Microsoft: VFP - Databases, SQL&VFP, and Reports" rather than this forum (although you'll still get an answer either way).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[denabates]

Sorry again for posting into an existing thread. I found my answer the box I was referring to was a browse box and what I had to do in my program was to put a USE after my last sqlexec to close that one. Here is my code.

nsqlresult = sqlexec(nSqlConnId,"msp_update_notes_with_seminars")

IF nSqlResult < 1

MESSAGEBOX('Cursor Failed, Result = '+STR(nSqlResult),16, 'Cursor Update Error')
*ELSE

*MESSAGEBOX('Cursor Update ok, Result = '+STR(nSqlResult), 16, 'Cursor Update Ok')

ENDIF
USE

 

[Mike Lewis]

Dena,

This is puzzling. Firstly, a Browse wouldn't open of its own accord after a SQLSelect(). You would have to explicitly execute a Browse command.

Secondly, if you immediately close the cursor, it will be lost. There would be no point in fetching it in the first place.

If you are happy that your code is working, that's fine, but I can't see how that can be.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)