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

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

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