Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Embedded SQL problem for Unix/Sybase

Posted on 2002-05-24
7
Medium Priority
?
1,040 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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