SQLEXEC problem

[MMund]

Having a problem getting data from a DB2 database to a cursor using VFP6. Here's my 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

 

[cbasoz]

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).

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

 

[Mike Lewis]

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:

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

 

[jimoo]

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

 

[Mike Lewis]

Jim,

Your advice is good, but @@RowCount is specific to SQL Server. MMund said he is using DB2.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[MMund]

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.