Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQLEXEC problem

Status
Not open for further replies.

MMund

Programmer
Oct 9, 2007
57
CA
Having a problem getting data from a DB2 database to a cursor using VFP6. Here's my code:
Code:
c_query = " SELECT Associate.ASSOC_NUM, Associate.STORE, Associate.DEPT,Associate.Norm_hours, "+;
  " Associate.LAST_NAME, Associate.FIRST_NAME, Associate.MIDDLE_INIT, "+;
  " Associate.HIRE_DATE, Associate.FULL_PART_TIME, Accumulator.ACCUM_AMT , "+;
  " CASE " +;
  "    WHEN Associate.STORE IN ('0012','0029','0031','0068') Then "+;
  "	   		DECIMAL(ROUND(QPY.PAID_TIME_OFF.HOL_ELIG/7,2),3,2) "+;
  "	   ELSE "+;
  "			DECIMAL(ROUND(QPY.PAID_TIME_OFF.HOL_ELIG/6,2),3,2) "+;
  "	   END AS Hours"+;  
  " FROM {OJ QPY.ASSOCIATE Associate LEFT OUTER JOIN QPY.ACCUMULATOR Accumulator ON "+;
  "   		(Accumulator.ASSOC_NUM = Associate.ASSOC_NUM "+;
       " AND Accumulator.LEVEL2 = Associate.LEVEL2 "+;
       " AND Accumulator.ACCUM_YEAR = 2009 "+;
       " AND Accumulator.ACCUM_ID = '001')} , QPY.PAID_TIME_OFF" +;
  "  WHERE Associate.LEVEL2 = 'ST' "+;
  "  AND Associate.UNION IS NULL "+;
  "  AND Associate.STATUS_1 NOT IN ('T','P') "+;
  "  AND Associate.FULL_PART_TIME IN ('1','3') "+ STR_SELECT + ;
  "  AND Associate.HIRE_DATE <= " + cur_date + ;
  "  AND ASSOCIATE.ASSOC_NUM = QPY.PAID_TIME_OFF.ASSOC_NUM "

c_connect = SQLCONNECT('RPYPROD')
n_exec = SQLEXEC(C_CONNECT,c_query,'msa_inf')
= SQLDISCONNECT(c_connect)

SELECT msa_inf.* ;
FROM msa_inf ;
WHERE full_part_time = '1' ;
	OR (full_part_time = '3') ;
ORDER BY store, full_part_time, dept ;
INTO TABLE (f_elig1)
I'm getting an error stating "Table MSA_INF does not exist."
When I run from the command box, it appears to select records, but won't assign them to a query, cursor, or table.

Any help would be appreciated.

TIA,

MMund
 
When I run from the command box, it appears to select records, but won't assign them to a query, cursor, or table."

How do you know that??? If it selects then you should have a result cursor at hand, no?

What is the value of m.n_exec after SQLExec() and what is the error message (currently in this piece of code you are not checking either one of them).

Code:
n_exec = SQLEXEC(m.C_CONNECT,c_query,'msa_inf')
SQLDISCONNECT(m.c_connect)
if m.n_exec = -1
  aerror(aWhy)
  messagebox(aWhy[2]) && 2nd out of 7 is the most informative
endif

PS: You are not using parameters. That should ring bells about security.


Cetin Basoz
MS Foxpro MVP, MCP
 
MMund,

There are two things you can do to track down this type of problem:

First, after calling SQLEXEC(), look at its returned value (n_exec, in your case). If this is negative, it means that the back end has reported an error.

You can then use AERROR() to get the actual error message:

Code:
n_exec = SQLEXEC(C_CONNECT,c_query,'msa_inf')
if n_exec < 0
  AERROR(laError)
  MESSAGEBOX(laError(3))
endif

This will display an informative error message.

Another useful thing to do is to paste the SQL code into the "immediate mode" window within the database. I don't know DB2, but I assume it has an equivalent of VFP's command window. By running the code there, you eliminate any errors caused directly by your VFP code, plus you can take advantage of whatever diagnostics the database provides.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 
Try running the command directly in Management Studio if possible.

Also - check row count in SQL Server

Pass this "SELECT @@RowCount") into the SQLEXEC after your query.



Jim
 
Problem solved. Took the math operations out of the DB2 query and did them once I had the VFP table built. Thanks to all for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top