SQL Pass Thru 2

[KarenJB]

I am trying to gather information from a SQL Database using SQL pass thru. Here is the code that I am attempting to run using VFP 9.....

SQLEXEC(lnHandle,"SELECT dbffenderEvent.PersonId, dbo_EventInfo.Comment, dbo_EventInfo.StartDate,;
dbo_EventInfo.TypeDescription;
FROM dbffenderEvent INNER JOIN dbo_EventInfo ON dbffenderEvent.EventId = dbo_EventInfo.Id WHERE dbffenderEvent.PersonId=?180197","Results")
SELECT results
browse

I get a message that reults can not be found. What am I doing wrong? Thanks in advance.

 

[JRB-Bldr]

Have you attempted to run the same code directly within the SQL Server QL Query Analyser ?

If the SQL Query command won't run there, it won't run elsewhere.

All SQL Pass-thru commands should be validated by running them in this manner. It will check the field parameters, the query syntax and anything else.

Once you have done that and gotten everything to run correctly, then you can migrate it into your VFP application.

Another thing that I always do, although admittedly it just might be my own idiosyncrasy, is to write the command line(s) separately so that I might minimize the likelihood of introducing additional syntax errors.

cSQLCommand = <whatever>
nRet = SQLEXEC(lnHandle, cSQLCommand, 'Results')
IF nRet = 1
  < do whatever >
ENDIF

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Karen,

I suspect the problem is the ? in this line:

WHERE dbo_OffenderEvent.PersonId=[b]?[/b]180197

The ? precedes a variable that contains a parameter variable. Obviously, 180197 isn't a variable.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[KarenJB]

Mike, I took the ? out and still get the same results, Alias 'Results' is not found.

JRBBldr, I created a query in Access 2000 that show this code and works fine. I wasn't sure what to add to it to put the data in a table.

SELECT dbffenderEvent.PersonId, dbo_EventInfo.Comment, dbo_EventInfo.StartDate, dbo_EventInfo.TypeDescription
FROM dbffenderEvent INNER JOIN dbo_EventInfo ON dbffenderEvent.EventId = dbo_EventInfo.Id
WHERE (((dbffenderEvent.PersonId)=180197));

 

[KarenJB]

This code runs in SQL Query Analyzer just fine.....

SELECT OffenderEvent.PersonId, EventInfo.Comment, EventInfo.StartDate, EventInfo.TypeDescription
FROM OffenderEvent INNER JOIN EventInfo
ON OffenderEvent.EventId = EventInfo.Id WHERE OffenderEvent.PersonId=180197

How do I 'punctuate' it for SQL Pass thru and get it into a VFP table?

 

[Nigel Gomm]

Karen

What's the value of lnHandle?

i.e. are you sure the sqlconnect() worked?

 

[KarenJB]

The connect worked....

 

[Mike Lewis]

Karen,

I notice you've got semi-colons embedded in the statement. As youi know, semi-colons are continuation markers in VFP, but they're statement terminators in most other versions of SQL.

Try taking them out and run the code again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Nigel Gomm]

Also check the connection string (/dsn) and make sure you are defaulting to the correct database.


After that try removing all complexity from the sql and add it back one clause at a time till it stops working.

e.g.

=sqlexec(m.lnhandle,"select * from FROM dbffenderEvent","Results")



hth

n

 

[cbasoz]

1) Make it more readable say using text ... endtext. Using string literals it is both hard to write in a readable manner and would get you into trouble with slightly longer SQL (ie: you were near the string literal limit of 255).
Also text ... endtext approach have a benefit that you can simply copy/paste between SSMS query window and VFP to test code.

2) Use parameters

3) Check return value. If it is -1 then check why by querying with aError().

Here is a revised version of your query:

TEXT TO lcSQL noshow
SELECT 
   evt.PersonId, inf.Comment, inf.StartDate, inf.TypeDescription
FROM dbo_OffenderEvent evt 
INNER JOIN dbo_EventInfo inf 
  ON evt.EventId = inf.[Id] 
WHERE evt.PersonId=?m.personID
ENDTEXT

personID = 180197
IF SQLEXEC(m.lnHandle, m.lcSQL, "results") < 0
  AERROR(aWhy)
  MESSAGEBOX(aWhy[2])
ELSE
  SELECT results
  BROWSE
endif

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

One advantage of writing your SQL Query Command as a separate line, as I typically do, is that you can check the command string and its syntax prior to issuing the SQLEXEC().

m.PersonID = 180197  && established somewhere

< do whatever >

* --- Now, knowing that m.PersonID has been populated ---
* --- Build the Command String Inclusive of its value ---
cSQLCommand = "SELECT dbo_OffenderEvent.PersonId,";
  + " dbo_EventInfo.Comment, dbo_EventInfo.StartDate,";
  + " dbo_EventInfo.TypeDescription;";
  + " FROM dbo_OffenderEvent ";
  + " INNER JOIN dbo_EventInfo ON ";
  + " dbo_OffenderEvent.EventId = dbo_EventInfo.Id";
  + " WHERE dbo_OffenderEvent.PersonId=";
  + ALLTRIM(STR(m.PersonID))

?cSQLCommand  && For Debug ONLY

SET STEP ON && Debug Break

* --- If Command String Looks Good, Go On And Execute ---
nRet = SQLEXEC(lnHandle, cSQLCommand, 'Results')

If there are ANY differences between the Command string and the test string that you execute directly in Access 2000, then you can change the VFP syntax as necessary.

Note - what Cetin is suggesting above is a different approach to doing pretty much the same as my suggestion. By issuing the TEXT/ENDTEXT to lcSQL, you are also creating a SQL Query text string (m.lcSQL) which is then used within the SQLEXEC().

You can either have the SQL Command string dynamically use the value of a variable by including the ?m.PersonID or build its value into the Command string as needed prior to execution like I have shown.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Karen,

You've had a lot of good advice. Let me add a few more words:

1. First, check my point about semi-colons (see above). I think it could solve your immediate problem, but other issues might remain.

2. Whenever you call SQLEXEC(), it's a good idea to trap and test the returned value. If it's negative, use AERROR() to get an informative error message. In most cases, that's all you need to figure out what went wrong.

3. Whenever you post a question on Tek Tips about back-end databases, specify the database you are accessing. At one point, you mentioned Query Analyzer, which indicates SQL Server, but that wasn't evident from your original post. The point is that different databases have slightly different syntax, and what works in, say, Oracle might throw an error in, say, MySQL.

4. Most back-end databases have a console or "immediate mode", where you can type some SQL code and see the result straight away. (As you know, that's what Query Analyzer does in SQL Server.) If a command doesn't work in VFP, switch to the console to debug and experiment.

5. As others have said, using TEXT / ENDTEXT is a good way of coding your SQL code in VFP, as it allows you to use indentation and white space to make it readable. Also, it's easier to paste from the TEXT / ENDTEXT construct into Query Analyzer or whatever.

I hope the above will help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[KarenJB]

Text

Thank you one and all, I finally got this to work using Cetin's methodology. It was a great tip to use the Text, EndText. I could actually see what was really going on. I certainly appreciate everyone's input and timely response to this matter. I am sure I will need to call on the 'Experts' again soon.

EndText