Home » Developer & Programmer » Precompilers, OCI & OCCI » Variable Declarations in the DECLARE SECTION; (Oracle 10g2, Solaris)
Variable Declarations in the DECLARE SECTION; [message #446526] Tue, 09 March 2010 02:13 Go to next message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
Hi,

I am getting some odd results from a Database Insert Function. The function receives an Array of elements. The elements are a defined structure (containing around 21 data items). I need to insert these records into an Oracle table.

For each element of the Array the function reads the structure into a local c structure of the same type.

I then go through this local structure and get a copy of the data into local variables declared in the EXEC SQL BEGIN DECLARE SECTION of the function. I then use the local vars to do the insert, using null INDICATOR variable to handle those variables that could be empty. The local variables look like this....

    EXEC SQL BEGIN DECLARE SECTION;

        int     dbServiceTypeId;
        int     dbRecordType;
        char    dbRbmCustRef[MAX_CUST_REF_LEN];
        int     dbServiceSeq;
        int     dbServiceParentSeq;
    /*    char    dbCOS[MAX_COS_LEN]; */
        int     dbServiceTaxCode;
        char    dbServiceCodeId[MAX_PROD_NAME_LEN];
        char    dbServiceName[MAX_PROD_NAME_LEN];
        char    dbServiceDesc[MAX_PROD_DESC_LEN];
        char    dbServiceAddress[MAX_SERVICE_ADDR_LEN];
        char    dbFromDate[MAX_DATE_LEN];
        char    dbToDate[MAX_DATE_LEN];
        int     dbUnits;
        double  dbUnitRate;
        char    dbUnitType[UNIT_TYPE_LEN];
        double  dbServiceCharge;
        char    dbOtcTimeSegment[MAX_ATTRIBUTE_LEN];
        int     dbOtcMultiplier;
        char    dbServiceFamilyName[MAX_PROD_NAME_LEN];
        char    dbOraDateFmt[MAX_DATE_LEN];

        EXEC SQL VAR dbOraDateFmt IS STRING;
        EXEC SQL VAR dbRbmCustRef IS STRING;
        EXEC SQL VAR dbServiceCodeId IS STRING;
        EXEC SQL VAR dbServiceName IS STRING;
        EXEC SQL VAR dbServiceDesc IS STRING;
        EXEC SQL VAR dbServiceAddress IS STRING;
        EXEC SQL VAR dbFromDate IS STRING;
        EXEC SQL VAR dbToDate IS STRING;
        EXEC SQL VAR dbUnitType IS STRING;
        EXEC SQL VAR dbOtcTimeSegment IS STRING;
        EXEC SQL VAR dbServiceFamilyName IS STRING;

        short ind_dbServiceCodeId;
        short ind_dbRbmCustRef;
        short ind_dbServiceName;
        short ind_dbServiceDesc;
        short ind_dbServiceAddress;
        short ind_dbFromDate;
        short ind_dbToDate;
        short ind_dbUnits;
        short ind_dbUnitRate;
        short ind_dbUnitType;
        short ind_dbOtcTimeSegment;
        short ind_dbOtcMultiplier;
        short ind_dbServiceFamilyName;

    EXEC SQL END DECLARE SECTION;


Notice the dbCOS is commented out. Like this the function works fine... a portion of the table.. as follows..

ID      SERVICE CODE       R/T   RATE    MULTI
4	1325-SCODE-1084	    1	1200	    0
1	1326-SCODE-1086	    1	0	    0
1	1326-SCODE-1086	    2	21	    0
2	1327-SCODE-1087	    1	0	    0
2	1327-SCODE-1087	    2	126	    0
2	1327-SCODE-1087	    3	20	    0
2	1327-SCODE-1087	    3	20	    0  


BUT.. when I uncomment the dbCOS variable (even though I don't populate it or try to include it in the insert) I get the following in the table (The RATE value goes missing completely and the multiplier is wrong)...

ID  SERVICE CODE           R/T   RATE    MULTI
4	1325-SCODE-1084     1		    542139762
1	1326-SCODE-1086     1		    542139762
1	1326-SCODE-1086	    2		    542139762
2	1327-SCODE-1087	    1		    542139762
2	1327-SCODE-1087	    2		    542139762
2	1327-SCODE-1087	    3		    542139762
2	1327-SCODE-1087	    3		    542139762


However, a printf statement just before the insert based that returns the variables shows the following...

Service Type ID: '4'
Record Type ID: '1'
Service Cust Ref: '1325-SCODE-1084'
Service Unit Rate: '1200.00'


Indeed, for this record the Multiplier doesn't even get populated. The other odd thing is if I recomment the dbCOS but remove the dbOraDateFmt variable definitions, it corrupts the data again, though different fields.

I can't understand why individual local variables are behaving this way. Is this a problem with the way variables are declared in this section?

Thanks

Mike
Re: Variable Declarations in the DECLARE SECTION; [message #446530 is a reply to message #446526] Tue, 09 March 2010 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you overwrite some variables when you populate others.
Something like '\0' is missing at the end of a string, a length is not correct, and so on.

Regards
Michel
Re: Variable Declarations in the DECLARE SECTION; [message #446537 is a reply to message #446530] Tue, 09 March 2010 03:42 Go to previous messageGo to next message
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
Member
Hi,

Thanks Michael. I just don't understand why if I format an insert string using a printf statement on the local variables...

printf("INSERT INTO MYTABLE (fieldx, fieldy, ..... ) VALUES (:dbServiceTypeId, :dbRecordType, :dbUnitRate, ....);


... just prior to the insert and then run in in SQL it does the insert??? Looking at values in the printf output, they are correct. I guess the printf is formatting any char* values with missing '\0' in a different way?

Thanks again.

Mike
Re: Variable Declarations in the DECLARE SECTION; [message #446543 is a reply to message #446537] Tue, 09 March 2010 04:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a typical buffer/variable overflow case.
You add a printf, which add local variables, which allocates space you can use, which may luckily add a '\0' in a good place, or something like that.
You have to find where some length is wrong, or some variable is not fit correctly or the like.

Regards
Michel
Previous Topic: What is OCI_BATCH_ERRORS and BATCH ERROR Mode
Next Topic: How to view SQLCODE value in Debuggin' Time
Goto Forum:
  


Current Time: Thu Mar 28 16:23:37 CDT 2024