• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

Embedded SQL problem for Unix/Sybase

Have been working on this same stupid statement now for more than two days!  What is wrong with the
SQL below?  I am getting the following error:

** SQLCODE=(-5702)
** SQL Server Error
** The SQL Server is terminating this process.

This is the embedded SQL statement that is executed in a 'C' program.

EXEC SQL DECLARE e_cur CURSOR FOR
SELECT isnull(part_nbr,:company_id_nbr_var),isnull(splr_conv_fctr,0),isnull(invt_conv_fctr,0),splr_nbr
FROM supplier_table
WHERE company_id_nbr_tbl = :company_id_nbr_var AND
      company_facility_cd = :fclt_cd_var AND
      prim_alt_splr_cd = 'P';
EXEC SQL OPEN e_cur;
EXEC SQL FETCH e_cur INTO
      :e_company_id_nbr,:e_splr_conv_fctr,:e_invt_conv_fctr,:e_splr_nbr;
EXEC SQL CLOSE e_cur;

The variables were initialized earlier in the program as follows:

EXEC SQL BEGIN DECLARE SECTION;
char company_id_nbr_var[12];
EXEC SQL END DECLARE SECTION;

memset(company_id_nbr_var,'\0',strlen(company_id_nbr_var));
memset(e_company_id_nbr,'\0',strlen(e_company_id_nbr));
memset(e_splr_nbr,'\0',strlen(e_splr_nbr));
e_splr_conv_fctr = 0;
e_invt_conv_fctr = 0;
strcpy(company_id_nbr_var,company_id_nbr1);

(where company_id_nbr1 is simply a variable that is passed into the paragraph that is being executed)

PLEASE NOTE: For this particular select statement I know that my result set is NULL because I have tested
it directly against the database table, nevertheless, why would I be getting this error message just
because it is a NULL result set.    
Later,  I tested the statement by taking out the WHERE clause, recompiing and retesting because the
database table is quite small in the test area.  When I take out the where clause,  I get the following
error:

** SQLCODE=(-33620273)
** SQL Server Error
** cs_objects: cslib user api layer: external error: 34000 Invalid cursor name.

Modifying the cursor name does not change anything.


Will be glad to provide any other information you might need to help me resolve this.  Such as statements
used to precompile then compile and bundle.

-Roger

P.S. I don't know whether this is legal or not but this question was also posted in the 'C' programming area.  Any solution accepted will also win those points there as well as long as they post the same response in both areas.  Problem is I don't really know whether the problem is 'C' or the SQL, or incorrect compile options or what.
0
RSchafer
Asked:
RSchafer
  • 3
  • 3
1 Solution
 
TriskelionCommented:
I'm examining this, but before I continue, you should not memset a string by strlen().  This can be
dangerous for strings declared on the stack and would do nothing for strings declared on the heap.
Use sizeof(company_id_nbr_var), etc.

Now to the PL/SQL.
When you remove the FETCH, does it complete without error?
I understand it won't pull the data, but have you tried removing a line like that stop the error?
0
 
RSchaferAuthor Commented:
Thanks Triskelion for the comments.

ADDITIONAL INFORMATION:
The offending statement the EXEC SQL OPEN e_cur;
I have placed displays after every SQL statement and it is the OPEN that is not executing successfully.
0
 
TriskelionCommented:
You do have permission capable of creating cursors, right?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
RSchaferAuthor Commented:
Yes,  I have permissions for creating a cursor because I create a cursor for a different table  (same database) earlier in the code without encountering any problems.

-Roger
0
 
TriskelionCommented:
Have you tried something silly like modifying the query so that it's ultra simple?
0
 
RSchaferAuthor Commented:
Triskelion,
Thank you for your responses and effort to answer this question.   Although I still do not know the true cause of this error,  I have gotten around the problem by simply making a copy of the database (it is quite small) and placing it on the same server that is used to execute the code.  This has avoided the problem for the time being.

I intend to ask the community support to delete this question as it appears that the problem is environment related and may remain a mystery.   I considered giving you a grade for this question but this website suggests that I just ask them to delete a question that was not answered completely rather than awarding points and a lower grade which might lower your rating.

R Schafer
0
 
MindphaserCommented:
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now