Thread: Embedded SQL

  1. #1
    Registered User
    Join Date
    May 2006
    Posts
    2

    Embedded SQL

    Can anyone help?
    I have embedded SQL in the ProC program.
    I am trying to retrieve data via a cursor then use this information to get data from another cursor.

    Goes like this
    Code:
     EXEC SQL AT :dbname 
          DECLARE cur1 CURSOR FOR
             SELECT details from 1 table using :host varable/bind variable
       
    
       EXEC SQL AT :dbname 
          DECLARE cur2 CURSOR FOR
             SELECT select details from 2 tables via join using host variable
    
      
       EXEC SQL WHENEVER SQLERROR DO SPOTS_oracle_error ( ) ;
    
       EXEC SQL OPEN cur1;
    
       for ( ; ; )
       {
    
          EXEC SQL WHENEVER NOT FOUND DO break ;
    
      
          EXEC SQL FETCH cur1
             INTO :hv_var1 etc,
    
            
            EXEC SQL OPEN cur2 ;
              if(sqlca.sqlcode == 0) {    
    	EXEC SQL FETCH cur2
    	                  INTO :var2;
               }
    	
       EXEC SQL CLOSE cur2;
     EXEC SQL CLOSE cur1;
    However the above does not work - I have it logging into a log file so I can see where it goes. It retrieves the first row from cur1 but then fails on the fetch of cur2 (text in italics) - no errors - not even a no data found error are reported and nothing else is logged. The job finishes in error but nothing is logged anywhere about this errror - I have removed logs I have to make it easier to read. Am I doing something wrong?
    I have also tried to move the declare of cur2 to after cur1 has been fetched but the samething happens - is there a problem with proc using nested cursors. Strangely it works if I use a straight select instead of cur2 ie
    Code:
    EXEC SQL AT :dbname 
    	        SELECT value INTO :value FROM table WHERE x = :hv_id

    Thanks

  2. #2
    Registered User
    Join Date
    May 2006
    Posts
    2
    The code above is run automatically by a batch process picking up jobs from the Oracle table.

    Have found that it works when we enter a single job but when we use a repeating job it fails. Might be something to do with table locking - will post on a DB forum.

    Thanks

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Help uderstanding a function with embedded SQL
    By cjohnman in forum C Programming
    Replies: 0
    Last Post: 04-29-2008, 01:13 PM
  2. Please help create a loop for embedded SQL
    By cjohnman in forum C Programming
    Replies: 4
    Last Post: 04-24-2008, 06:46 AM
  3. Embedded SQL Order By
    By cjohnman in forum C Programming
    Replies: 12
    Last Post: 04-15-2008, 03:45 PM
  4. Problem with embedded SQL in C/C++ (ECPG)
    By NeuralClone in forum C Programming
    Replies: 4
    Last Post: 10-21-2005, 05:16 PM
  5. Embedded SQL ... should be quick ...
    By MadGooseXP in forum C Programming
    Replies: 3
    Last Post: 11-05-2002, 08:34 PM