Solved

Embedded SQL problem for Unix/Sybase

Posted on 2002-05-24
7
975 Views
Last Modified: 2008-02-01
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
Comment
Question by:RSchafer
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:Triskelion
ID: 7034338
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
 

Author Comment

by:RSchafer
ID: 7035025
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
 
LVL 6

Expert Comment

by:Triskelion
ID: 7035179
You do have permission capable of creating cursors, right?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:RSchafer
ID: 7035493
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
 
LVL 6

Expert Comment

by:Triskelion
ID: 7035693
Have you tried something silly like modifying the query so that it's ultra simple?
0
 

Author Comment

by:RSchafer
ID: 7045127
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 7045196
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now