Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations

[<mod...@co...>]

Hi Vincent,

I'm following the "Array Interface Example" here, 

http://orclib.sourceforge.net/doc/html/group__g__bind.html

except the length of my array is not known at compile time. Rather, it is downloaded from the database when the program is run. I've created the following example code to see if I can get this working:

OCI_Connection *cn; 
OCI_Statement  *st;
char **array_person_name;
char person_name[101] = "";
...

/* create connection and statement */

OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);

/* binding */

OCI_Prepare(st, "INSERT INTO my_table values( :person_name )");
OCI_BindArraySetSize(st, 3);
OCI_BindArrayOfStrings(st, ":person_name", (char*) array_person_name, 100, 0 );

/* filling arrays */

strncpy(person_name, "John Smith", strlen("John Smith")+1 );
array_person_name = malloc(3 * sizeof(char*)); /* allocate memory for row pointers */
for(i=0;i<3;i++) /* try 3 rows */
{
  array_person_name[i] = malloc(101 * sizeof(char)); /* allocate memory for columns */
  strncpy(array_person_name[i], person_name, 100 );
}

if (!OCI_Execute(st)) {
   exit(41);
}

if (!OCI_Commit(cn)) {
   exit(100);
}
OCI_Cleanup();  
return EXIT_SUCCESS;


Running the code causes the program to exit 41 (the OCI_Execute statement fails). Any idea what I'm doing wrong? In your example you use one contiguous buffer to store the array, whereas in my code above, because the arrays are allocated at run time, the array contents are not stored in one contiguous buffer (is this a problem for your function?). I can't think of any other difference between our approaches. 

If I instead declare the array size at runtime as you do in your example code, everything works fine.

Note that the table my_table in the database only contains one column, which is named "person_name" and is varchar2(100). 

Thanks in advance for any comments.





----- Original Message ----- 
From: "vincent rogier" <vin...@ya...> 
To: mod...@co... 
Cc: orc...@li... 
Sent: Thursday, April 5, 2012 9:57:58 AM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

hi, 

yes, what you want to perform is shown on the example " Array interface Example" . That is the best way to do it. 

regards, 

vincent 


On Thu, Apr 5, 2012 at 6:18 PM, < mod...@co... > wrote: 


Thanks Vincent, 

It works perfect now! I learned that I could remove the "into :out_d1, :out_d2, :out_d3, :out_i1" from the OCI_Prepare statement (since those binded variables aren't actually used anywhere). The code now looks like: 

OCI_SetFetchSize(st, pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 from my_table where report_name = :in_report_name and edge = 'a'"); 

OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_Execute(st); 

rs = OCI_GetResultset(st); 

while (OCI_FetchNext(rs)) 
{ 
d1[i] = OCI_GetDouble(rs, 1); 
d2[i] = OCI_GetDouble(rs, 2); 
d3[i] = OCI_GetDouble(rs, 3); 

i1[i] = OCI_GetInt(rs, 4); 
i++; 
} 

This downloads multiple rows at a time and stores them into C variables. How would one do the reverse? That is, how to INSERT multiple rows into a database table. For example, I have arrays (each array contains "pts" number of elements) for 

C_report_name 
C_edge 
d1 
d2 
d3 
i1 

corresponding to columns in "my_table" named 

report_name 
edge 
dd1 
dd2 
dd3 
ii1 

What would an example code look like? I didn't see an OCI_SetDouble statement exists to use a similar architecture but the other way around. Are bulk operations the only way to do this? -- as shown in the "Array Interface Example" section here: 

http://orclib.sourceforge.net/doc/html/group__g__bind.html 

(e.g. arrays for tab_int and tab_str). Is this the right approach? 

How would it change if I wanted to use a stored procedure (saved in the schema) instead of a SQL command in the OCI_Prepare statement? 

Whatever hints you can provide would be extremely useful. Thanks again 





----- Original Message ----- 
From: "vincent rogier" < vin...@ya... > 
To: mod...@co... 
Cc: orc...@li... 
Sent: Wednesday, April 4, 2012 11:41:54 PM 
Subject: Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations 

Hi, 

the syntax "select .... into ....." is wrong. 

Bulk operation are related to binds variables, not selected columns. 

What you want to do, is "array fetching". You want to fetch more than one row at a time. No problem.... That what OCILIB already does internally. 
When OCILIB fetches data (using OCI_FetchNext() for example), it fetches by default 20 rows from the DB. Next calls to COI_FetchNext() does not do servers round trips and local data is returned. Once you've called OCI_FechNext 20 times, then the next call fetches another 20 rows from DB and so on. 

The number of rows that OCILIB internally fetches is by default 20 but can be modified using OCI_SetFetchSize(). 

So, you don't have to provide arrays to OCILIB. You just call OCI_FetchNext() and OCI_Getxxxxx() to retrieve values. 

If you really want to copy values from OCILIB resultset to your own arrays you could do like this : 


OCI_SetFecthSize(pts); 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 into :out_d1, :out_d2, :out_d3, :out_i1 from my_table where report_name = :in_report_name and edge = 'a'"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_Execute(st); 

rs = OCI_GetResultset(st); 

while (OCI_FetchNext(rs)) 
{ 
out_d1[i] = OCI_GetDouble(rs, 1); 
out_d2[i] = OCI_GetDouble(rs, 2); 
out_d3[i] = OCI_GetDouble(rs, 3; 
out_i1[i] = OCI_GetInt(rs, 4); 

i++; 
} 




On Thu, Apr 5, 2012 at 6:48 AM, < mod...@co... > wrote: 


Hi, I've been successfully using OCILIB to access PL/SQL stored procedures in Oracle 11.2 database from C program. Now I need to download a lot of array data, so I thought I'd use bulk operations for a fast download. Since I know my connection is good, I must be getting the syntax wrong in my code. I've tried to follow the user manual and any examples I can find online, but there's nothing covering my exact situation. I was hoping someone could give me a sanity check on my OCILIB implementation below. I'm using gcc compiler with -std=C89 switch. 


OCI_Connection *cn; 
OCI_Statement *st; 

char report_name[60]; 
double * d1, * d2, * d3; 
int * i1; 
unsigned int pts = 9; /* pts is actually determined when the program executes, but I set it to 9 here to simply the code for example only */ 

... 

/* allocate array memory */ 
d1 = malloc( sizeof(double) * pts ); 
d2 = malloc( sizeof(double) * pts ); 
d3 = malloc( sizeof(double) * pts ); 
i1 = malloc( sizeof(int) * pts ); 

... 

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) 
return EXIT_FAILURE; 
cn = OCI_ConnectionCreate("<removed>", "myschema", "myuser", OCI_SESSION_DEFAULT); 
st = OCI_StatementCreate(cn); 

... 

/* database table 'my_table' has columns: 
dd1 is binary_double 
dd2 is binary_double 
dd3 is binary_double 
ii1 is number(8,0) 
report_name is varchar2(60 bytes) 
edge is char(1 byte) */ 
OCI_Prepare(st, "select dd1, dd2, dd3, ii1 into :out_d1, :out_d2, :out_d3, :out_i1 from my_table where report_name = :in_report_name and edge = 'a'"); 
OCI_BindString(st, ":in_report_name", report_name, 60); 
OCI_BindArraySetSize(st, pts); 
OCI_BindArrayOfDoubles(st, ":out_d1", (double*) d1, 0); 
OCI_BindArrayOfDoubles(st, ":out_d2", (double*) d2, 0); 
OCI_BindArrayOfDoubles(st, ":out_d3", (double*) d3, 0); 
OCI_BindArrayOfInts(st, ":out_apron_tie", (int*) i1, 0); 
OCI_Execute(st); 


/* TEST */ 
printf("\nd1[0] = %g ", d1[0]); 
printf("\nd1[1] = %g ", d1[1]); 


OCI_Cleanup(); 
return EXIT_SUCCESS; 

Thanks for any comments 

------------------------------------------------------------------------------ 
Better than sec? Nothing is better than sec when it comes to 
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free. 
http://p.sf.net/sfu/Boundary-dev2dev 
_______________________________________________ 
Orclib-users mailing list 
Orc...@li... 
https://lists.sourceforge.net/lists/listinfo/orclib-users 



-- 
Vincent Rogier 



-- 
Vincent Rogier