Home » Developer & Programmer » Precompilers, OCI & OCCI » help!!!it's not the desired result of oci bulk update!!
|
|
| Re: help!!!please give me an example of oci bulk update in c!! [message #193907 is a reply to message #193712] |
Tue, 19 September 2006 21:25   |
xujyo
Messages: 4
Registered: September 2006
|
Junior Member |
|
|
here is demo_update of oci/samples cdemodr1.c,i modified a little,and the other of cdemodr1.c keeps unchanged:
1.
give the result:
after demo_insert
in4[i] = 400.555 + (float)i;//c4 value
get the result：
C1 C4
---- ----------
1 400.554993
2 401.554993
3 402.554993
4 403.554993
5 404.554993
6 405.554993
7 406.554993
8 407.554993
9 408.554993
10 409.554993
i expect to update c4 value to:
in4[i] = 1401.555 + (float)i;
and get the result:
C1 C4
---- ----------
1 1400.554993
2 1401.554993
3 1402.554993
4 1403.554993
5 1404.554993
6 1405.554993
7 1406.554993
8 1407.554993
9 1408.554993
10 1409.554993
but after my modified demo_update,
the result is not the expectation,
the resule is:
C1 C4
---- ----------
1 1403.55505
2 1403.55505
3 1403.55505
4 1403.55505
5 1403.55505
6 1403.55505
7 1403.55505
8 1403.55505
9 1403.55505
10 1403.55505
2.
table create script:
create table TAB1
(
c1 integer not null
c2 char(40),
c3 varchar2(40),
c4 float,
c5 decimal,
c6 decimal(8,3),
c7 numeric,
c8 numeric(7,2),
c9 date,
c10 raw(40),
constraint ind_tab1
)
organization index;
[Updated on: Tue, 19 September 2006 21:31] Report message to a moderator |
|
|
|
| Re: help!!!please give me an example of oci bulk update in c!! [message #193908 is a reply to message #193907] |
Tue, 19 September 2006 21:26 |
xujyo
Messages: 4
Registered: September 2006
|
Junior Member |
|
|
3.
here is the mofied demo_update:
static sword demo_update(OCISvcCtx *svchp, OCIStmt *stmthp,
OCIBind *bndhp[], OCIError *errhp)
{
int i, j;
int range_size = 3; /* iterations */
/*
* This function updates columns in table TAB1, for certain rows
* depending on the values of the :low and :high values in
* in the WHERE clause. It executes this statement 3 times, (3 iterations)
* each time with a different set of values for :low and :high
* Thus for each iteration, multiple rows are returned depending
* on the number of rows that matched the WHERE clause.
*
* The rows it updates here are the rows that were inserted by the
* cdemodr1.sql script.
*/
/* The Update Statement with RETURNING clause */
text *sqlstmt = (text *)
"UPDATE TAB1 SET C2 = :2, C3 = :3, \
C4 = :4, C5 = :5, C6 = :6, \
C7 = :7, C8 = :8, C9 = :9, C10 = :10 \
RETURNING C2, C3, C4, C5, C6, C7, C8, C9, C10 \
INTO :out2, :out3, :out4, :out5, :out6, \
:out7, :out8, :out9,:out10";
/* Prepare the statement */
if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtPrepare() update\n");
report_error(errhp);
return OCI_ERROR;
}
/* Initialise the buffers for insertion */
for (i = 0; i < MAXITER; i++)
{
//in1[i] = 300 + i;
memset((void *)in2[i], (int) 'a'+i%26, (size_t) 40);
memset((void *)in3[i], (int) 'A'+i%26, (size_t) 40);
in4[i] = 1401.555 + (float)i;
in5[i] = 500 + i;
in6[i] = 600.280 + (float)i;
in7[i] = 700 + i;
in8[i] = 800.620 + (float)i;
in9[i][0] = 119;
in9[i][1] = 185 - (ub1)i%10;
in9[i][2] = (ub1)i%12 + 1;
in9[i][3] = (ub1)i%25 + 1;
in9[i][4] = 0;
in9[i][5] = 0;
in9[i][6] = 0;
for (j = 0; j < 40; j++)
in10[i][j] = (ub1) (i%0x08);
rowsret[i] =0;
}
/* Bind all the input buffers to place holders (:1, :2. :3, etc ) */
//if (bind_input(stmthp, bndhp, errhp))
// return OCI_ERROR;
if (/*OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
(dvoid *) &in1[0], (sb4) sizeof(in1[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| */OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 1,
(dvoid *) in2[0], (sb4) sizeof(in2[0]), SQLT_AFC,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 2,
(dvoid *) in3[0], (sb4) sizeof(in3[0]), SQLT_CHR,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[3], errhp, (ub4) 3,
(dvoid *) &in4[0], (sb4) sizeof(in4[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[4], errhp, (ub4) 4,
(dvoid *) &in5[0], (sb4) sizeof(in5[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[5], errhp, (ub4) 5,
(dvoid *) &in6[0], (sb4) sizeof(in6[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[6], errhp, (ub4) 6,
(dvoid *) &in7[0], (sb4) sizeof(in7[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[7], errhp, (ub4) 7,
(dvoid *) &in8[0], (sb4) sizeof(in8[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[8], errhp, (ub4) 8,
(dvoid *) in9[0], (sb4) sizeof(in9[0]), SQLT_DAT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[9], errhp, (ub4) 9,
(dvoid *) in10[0], (sb4) sizeof(in10[0]), SQLT_BIN,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIBindByPos()\n");
report_error(errhp);
return OCI_ERROR;
}
if (/*OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0])
||*/ OCIBindArrayOfStruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1])
|| OCIBindArrayOfStruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2])
|| OCIBindArrayOfStruct(bndhp[3], errhp, s4, indsk[3], rlsk[3], rcsk[3])
|| OCIBindArrayOfStruct(bndhp[4], errhp, s5, indsk[4], rlsk[4], rcsk[4])
|| OCIBindArrayOfStruct(bndhp[5], errhp, s6, indsk[5], rlsk[5], rcsk[5])
|| OCIBindArrayOfStruct(bndhp[6], errhp, s7, indsk[6], rlsk[6], rcsk[6])
|| OCIBindArrayOfStruct(bndhp[7], errhp, s8, indsk[7], rlsk[7], rcsk[7])
|| OCIBindArrayOfStruct(bndhp[8], errhp, s9, indsk[8], rlsk[8], rcsk[8])
|| OCIBindArrayOfStruct(bndhp[9], errhp, s10, indsk[9], rlsk[9], rcsk[9]))
{
(void) printf("FAILED: OCIBindArrayOfStruct()\n");
report_error(errhp);
return OCI_ERROR;
}
/* Bind all the output buffers to place holders (:out1, :out2 etc */
//if (bind_output(stmthp, bndhp, errhp))
// return OCI_ERROR;
if (/*OCIBindByName(stmthp, &bndhp[10], errhp,
(text *) ":out1", (sb4) strlen((char *) ":out1"),
(dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
||*/ OCIBindByName(stmthp, &bndhp[11], errhp,
(text *) ":out2", (sb4) strlen((char *) ":out2"),
(dvoid *) 0, (sb4) MAXCOLLEN, SQLT_AFC,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[12], errhp,
(text *) ":out3", (sb4) strlen((char *) ":out3"),
(dvoid *) 0, (sb4) MAXCOLLEN, SQLT_CHR,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[13], errhp,
(text *) ":out4", (sb4) strlen((char *) ":out4"),
(dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[14], errhp,
(text *) ":out5", (sb4) strlen((char *) ":out5"),
(dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[15], errhp,
(text *) ":out6", (sb4) strlen((char *) ":out6"),
(dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[16], errhp,
(text *) ":out7", (sb4) strlen((char *) ":out7"),
(dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[17], errhp,
(text *) ":out8", (sb4) strlen((char *) ":out8"),
(dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[18], errhp,
(text *) ":out9", (sb4) strlen((char *) ":out9"),
(dvoid *) 0, (sb4) DATBUFLEN, SQLT_DAT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
|| OCIBindByName(stmthp, &bndhp[19], errhp,
(text *) ":out10", (sb4) strlen((char *) ":out10"),
(dvoid *) 0, (sb4) MAXCOLLEN, SQLT_BIN,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
{
(void) printf("FAILED: OCIBindByName()\n");
report_error(errhp);
return OCI_ERROR;
}
for (i = 0; i < MAXCOLS; i++)
pos[i] = i;
if (/*OCIBindDynamic(bndhp[10], errhp, (dvoid *) &pos[0], cbf_no_data,
(dvoid *) &pos[0], cbf_get_data)
||*/ OCIBindDynamic(bndhp[11], errhp, (dvoid *) &pos[1], cbf_no_data,
(dvoid *) &pos[1], cbf_get_data)
|| OCIBindDynamic(bndhp[12], errhp, (dvoid *) &pos[2], cbf_no_data,
(dvoid *) &pos[2], cbf_get_data)
|| OCIBindDynamic(bndhp[13], errhp, (dvoid *) &pos[3], cbf_no_data,
(dvoid *) &pos[3], cbf_get_data)
|| OCIBindDynamic(bndhp[14], errhp, (dvoid *) &pos[4], cbf_no_data,
(dvoid *) &pos[4], cbf_get_data)
|| OCIBindDynamic(bndhp[15], errhp, (dvoid *) &pos[5], cbf_no_data,
(dvoid *) &pos[5], cbf_get_data)
|| OCIBindDynamic(bndhp[16], errhp, (dvoid *) &pos[6], cbf_no_data,
(dvoid *) &pos[6], cbf_get_data)
|| OCIBindDynamic(bndhp[17], errhp, (dvoid *) &pos[7], cbf_no_data,
(dvoid *) &pos[7], cbf_get_data)
|| OCIBindDynamic(bndhp[18], errhp, (dvoid *) &pos[8], cbf_no_data,
(dvoid *) &pos[8], cbf_get_data)
|| OCIBindDynamic(bndhp[19], errhp, (dvoid *) &pos[9], cbf_no_data,
(dvoid *) &pos[9], cbf_get_data))
{
(void) printf("FAILED: OCIBindDynamic()\n");
report_error(errhp);
return OCI_ERROR;
}
/* bind row indicator low, high */
//if (bind_low_high(stmthp, bndhp, errhp))
// return OCI_ERROR;
/* update rows
between 101 and 103; -- expecting 3 rows returned (update 3 rows)
between 105 and 106; -- expecting 2 rows returned (update 2 rows)
between 109 and 113; -- expecting 5 rows returned (update 5 rows)
*/
lowc1[0] = 101;
highc1[0] = 103;
lowc1[1] = 105;
highc1[1] = 106;
lowc1[2] = 109;
highc1[2] = 113;
(void) printf("\n\n DEMONSTRATING UPDATE....RETURNING \n");
if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
(CONST OCISnapshot*) 0, (OCISnapshot*) 0,
(ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtExecute() update\n");
report_error(errhp);
return OCI_ERROR;
}
/* Commit the changes */
(void) OCITransCommit(svchp, errhp, (ub4) 0);
/* Print out the values in the return rows */
//(void) print_return_data(range_size);
return OCI_SUCCESS;
}
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 20:07:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
