blank truncation on INSERTs

  I'm having a problem INSERTing fields using Pro*C 1.3 and dynamic sql,  method 4. If I try to insert a field with 1 or more blanks at the  end, ORACLE seems to truncate the blanks.

  For example, in sql*plus,

      INSERT INTO CRAP(CRAP1, CRAP2) VALUES('XXXX ', 'XXX ');   yields via SELECT LENGTH(CRAP1), CRAP1, LENGTH(CRAP2), CRAP2 FROM CRAP;

          5 XXXX 5 XXX   so the blanks are there!

 But, if I mimic the same INSERT in Pro*C, dynamic SQL method 4, I get the  follow after the dynamic INSERT (using via the same SELECT above):

          4 XXXX 3 XXX  And if I code the same INSERT in regular Pro*C, I get the same results  as using sql*plus. So it seems like the problem lies in dynamic sql.

 I used the following driver to prove the dynamic SQL problem:

#include <stdio.h>
#define MAXMSG 200

EXEC SQL BEGIN DECLARE SECTION;
   static char auto_log = '/'; /* Oracle automatic logon */    VARCHAR statement[240];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
SQLDA *bind_dp;
SQLDA *select_dp;
extern SQLDA *sqlald();

int main()
{

   char msg_buf[MAXMSG];                  /* contains error message   */
   int  msg_buf_size = MAXMSG;            /* size of error msg buffer */
   int  err_msg_len;                      /* actual size of error msg */

   EXEC SQL WHENEVER SQLERROR GOTO errexit;    EXEC SQL WHENEVER SQLWARNING GOTO errexit;    EXEC SQL WHENEVER NOT FOUND continue;    EXEC SQL CONNECT :auto_log;

   bind_dp = sqlald(40,30,30);
   bind_dp->N=40;
   select_dp = sqlald(40,30,30);
   select_dp->N=40;

   strcpy(statement.arr,"INSERT INTO CRAP(CRAP1, CRAP2) VALUES(:B0,:B1)");    statement.len = strlen(statement.arr);    fprintf(stderr, "%s\n", statement.arr);    fprintf(stderr, "%d\n", statement.len);

   EXEC SQL PREPARE S FROM :statement;
   EXEC SQL DECLARE C CURSOR FOR S;
   EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;

   bind_dp->N=bind_dp->F;
   bind_dp->L[0]=4;
   bind_dp->L[1]=5;
   bind_dp->V[0]="XXXX ";
   bind_dp->V[1]="XXX  ";
   bind_dp->I[0]=0;
   bind_dp->I[1]=0;
   bind_dp->T[0]=1;
   bind_dp->T[1]=1;

   EXEC SQL OPEN C USING DESCRIPTOR bind_dp;    EXEC SQL CLOSE C;
   EXEC SQL COMMIT WORK RELEASE;
   exit(0);

errexit:

   sqlglm(msg_buf, &msg_buf_size, &err_msg_len);    fprintf(stderr,"\n %.*s \n", err_msg_len, msg_buf);    exit(-1);
}

   This problem occurs in some very nasty application code where we  were splitting up long fields in a table where a LONG already existed.  The split occurred at a blank and the blank was not written to the  database. On retrieval, everything was messed up.

 help!

 ron....

Ron McCamish
BITNET: rwm26_at_cas.bitnet
UUCP: ...!osu-cis!chemabs!rwm26
INET: rwm26_at_cas.org Received on Thu Oct 08 1992 - 21:09:41 CET