Visit Our UNIX and Linux User Community


Problem FETCHing Long data type using CURSOR


 
Thread Tools Search this Thread
Top Forums Programming Problem FETCHing Long data type using CURSOR
# 1  
Old 10-29-2009
Problem FETCHing Long data type using CURSOR

Currently my Pro*c program is fetching a cloumn which is defined as LONG in oracle db. The data in the column is around 65k. But when I am FETCHing it to a varchar[200kb] variable, I am only getting 22751 bytes of data using cursor.

Is there any limitation on the data which is fetched by a cursor in pro*C or do I need to do some thing more in coding to get all 65k of data present in the table column.

RVSN_DESC_LEN = 200*1024+1;
ETT_RVSN_TEXT.RVSN_DESC column contains 65k size data

Here is the code snippet:
Code:
VARCHAR rvsnLablName[RVSN_LABL_NAME_LEN];
    short rvsnLablName_ind = 0;
    VARCHAR rvsnLablText[RVSN_LABL_TEXT_LEN];
    short rvsnLablText_ind = 0;
    VARCHAR rvsnDesc[RVSN_DESC_LEN];
    short rvsnDesc_ind = 0;
 
:
:
 
EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO error;
  EXEC SQL DECLARE sbdvRvsnDescInfoCur CURSOR FOR
 SELECT 
  ETT_CELL_HDR.CELL_HDR_CODE, 
  ETT_CELL_HDR.CELL_HDR_TTL, 
  ETT_RVSN_TEXT.RVSN_DESC
 FROM
  ETT_CELL_HDR,
  ETT_RVSN_TEXT
 WHERE
  ETT_RVSN_TEXT.GENL_ORD_ID = :genlOrdID_i AND
  ETT_CELL_HDR.CELL_HDR_ID = ETT_RVSN_TEXT.CELL_HDR_ID(+)
 ORDER BY 
  ETT_CELL_HDR.PRNT_SEQ_NBR, ETT_RVSN_TEXT.PRNT_SEQ_NBR;
  EXEC SQL OPEN sbdvRvsnDescInfoCur;
  EXEC SQL WHENEVER NOT FOUND DO break;
  i = 0;
  *rvsnDescInfo_iop = NULL;
  for (;;)
  {
#ifdef DEBUG
    fprintf(outFile_gp, "reallocing, i = %d\n", i);
    fprintf(outFile_gp, "sizeof(RvsnDescInfoType) = %d\n", sizeof(RvsnDescInfoType));
    fflush(outFile_gp);
#endif /* DEBUG */
    *rvsnDescInfo_iop = (RvsnDescInfoType*) realloc(*rvsnDescInfo_iop, 
    (i+1)*sizeof(RvsnDescInfoType));
    if (NULL == *rvsnDescInfo_iop)
    {
      fprintf(stderr, "db_retrieveAddRvsnDescInfo: realloc() failed. errno = %d\n", errno);
      fflush(stderr);
      return FAIL;
    }
    memset(&rvsnDescInfo_iop[0][i], '\0', sizeof(RvsnDescInfoType));
    EXEC SQL FETCH sbdvRvsnDescInfoCur
    INTO
 :rvsnLablName:rvsnLablName_ind,
 :rvsnLablText:rvsnLablText_ind,
 :rvsnDesc:rvsnDesc_ind;
    rvsnLablName.arr[rvsnLablName.len] = '\0';
    rvsnLablText.arr[rvsnLablText.len] = '\0';
    if (rvsnDesc.len < RVSN_DESC_LEN)
    {
      rvsnDesc.arr[rvsnDesc.len] = '\0';
    }
    else
    {
      rvsnDesc.arr[RVSN_DESC_LEN - 1] = '\0';
    }

#ifdef DEBUG
    fprintf(outFile_gp, "rvsnLablName.len = %d\n", rvsnLablName.len);
    fprintf(outFile_gp, "rvsnLablText.len = %d\n", rvsnLablText.len);
    fprintf(outFile_gp, "rvsnDesc.len = %d\n", rvsnDesc.len);
    fflush(outFile_gp);
#endif /* DEBUG */
    if (-1 != rvsnLablName_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablName, rvsnLablName.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablName, " ");
    if (-1 != rvsnLablText_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablText, rvsnLablText.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnLablText, " ");
    if (-1 != rvsnDesc_ind)
      strcpy(rvsnDescInfo_iop[0][i].rvsnDesc, rvsnDesc.arr);
    else
      strcpy(rvsnDescInfo_iop[0][i].rvsnDesc, " ");
    i++;
  }
  EXEC SQL CLOSE sbdvRvsnDescInfoCur;
  return i;

When I display rvsnDesc.arr, I get 22751 as size.

Any help in this issue will be highly appreciated.

Thanks!

Last edited by pludi; 10-29-2009 at 11:31 AM.. Reason: code tags, please...
# 2  
Old 10-29-2009
It may be a data problem - if there is an ASCII NUL ( ASC(0)) character embedded in the data, C will terminate the string at that point. You are using strcpy() which will do that for example.

FWIW: avoid LONG, it has numerous issues; use CLOB datatype instead.
Oracle LONG CLOB Convert
# 3  
Old 10-29-2009
Thanks Jim for replying.

This is an old application I am maintaining so people dont want to move it to CLOB.

Regarding the data problem, it does not seems to be it as the data which is getting cut is a single word.

eg. at 22751 bytes data there is this word "...DOWN RESPONSE" and I am getting till "...DOW". So it does not seem to be ascii(0) issue.

Cheers!

Previous Thread | Next Thread
Test Your Knowledge in Computers #326
Difficulty: Medium
Time on Computers is commonly referred to as GNU Time.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extracting LONG Data Type from DB via UNIX Script

Hi, I want to extract a XML file which is stored in the database having a data Type as "LONG" via UNIX Scripting. But when i am triggering the SQL via UNIX it is fetching only the first Line and not the complete XML. Can you please suggest if the parameters that i have used needs any... (2 Replies)
Discussion started by: Barbara1234
2 Replies

2. Shell Programming and Scripting

Fetching the required data out of a tabular form

Hello Gurus, I am trying to fetch a required number of lines from an output of a command which is in tabular form. Below is the command for reference along with how the result is being shown on UNIX shell. /usr/openv/volmgr/bin/vmquery -b -p 5 The result of the above command is as... (6 Replies)
Discussion started by: Ali Sarwar
6 Replies

3. Red Hat

Double data type C Red Hat platform problem

I'm converting a binary file to ASCII using c code. The folllowing block of code prints correct double value 00000.000000000 on HPUNIX platform. longi double; /* C79 - Price Per Minute */ memcpy(&longi,&rbuff,8); fprintf(wfp,"%015.9f ",longi); prints : 00000.000000000 ... (6 Replies)
Discussion started by: krk
6 Replies

4. Shell Programming and Scripting

Fetching data particular data from the file.

Hi All, I have one fine which contaning the record as below, I need to fetch the number which is in Bold and from second line only URL need to pick. :ABCD C7QyzBXIs58k 1 fdmlksdlkfn ... (4 Replies)
Discussion started by: Riverstone
4 Replies

5. Shell Programming and Scripting

Fetching data from file

Hi All, I'm facing issue while using script. Chk_Etl_Status=`cat /dstage/questnet/qnetdv/input/Etl_Status.dat|cut -d"," -f1` echo Chk_Etl_Status=$Chk_Etl_Status above result is giving me sometime value as 1 and sometime error message as Chk_Etl_Status= dsjoblaunch.sh: test: Specify a... (5 Replies)
Discussion started by: Amit786
5 Replies

6. Shell Programming and Scripting

Fetching data from .csv file

Hi Experts, I have created a table with columns as empname,empid,phone,shiftname. Now I am having a .csv file format which contains the shift datas of the employees. I have to fetch this file and compare with the table I created to send an alert to the specified user. (2 Replies)
Discussion started by: micky3112
2 Replies

7. Shell Programming and Scripting

Extracting LONG Data Type from DB via UNIX Script

Hi, I want to extract a XML file which is stored in the database having a data Type as "LONG" via UNIX Scripting. But when i am triggering the SQL via UNIX it is fetching only the first Line and not the complete XML. Can you please suggest if the parameters that i have used needs any... (0 Replies)
Discussion started by: dear_abhi2007
0 Replies

8. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies

9. Shell Programming and Scripting

fetching data from sybase using perl

How can I fetch and retreive data(of each result set) of multiple result set from sybase by calling a stored procedure using perl DBI module on an unix solaris environment????? Please help (3 Replies)
Discussion started by: wadhwa.pooja
3 Replies

10. Programming

enable 64bit long type for gcc

hey, I believe I once saw a post in this forum, about enable an GCC option to enable long types. I simply cannot find it any more. Can anybody give me a hint? I am on 32bit Ubuntu, and I would like my int be really long. Also I need malloc() take long int argument too. I found it is necessary to... (6 Replies)
Discussion started by: patiobarbecue
6 Replies

Featured Tech Videos