Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations
Open source C and C++ library for accessing Oracle Databases
Brought to you by:
vince_del_paris
Menu
▾
▴
Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations
|
From: <mod...@co...> - 2012-04-06 15:36:22
|
Hi Vincent,
I think my code below is correct but for reasons not shown, there were some memory problems. I've got it working now, but in the process I made an interesting observation that I wanted to share with you. I've got both methods A and B below working.
Method A (sql statement)
OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "select dd1, 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);
i1[i] = OCI_GetInt(rs, 2);
i++; }
Method B (stored procedure)
OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "begin get_data( :in_report_name, 'a', :out_resultset ); end;");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_BindStatement(st, ":out_resultset", st2);
OCI_Execute(st);
rs = OCI_GetResultset(st2);
while (OCI_FetchNext(rs)) {
d1[i] = OCI_GetDouble(rs, 1);
i1[i] = OCI_GetInt(rs, 2);
i++; }
One of the ways I got my code working, by trial and error, was to use 'a' inside the OCI_Prepare statement. Originally, I attempted to bind an edge parameter ":in_edge" using (Method A):
char edge[1] = "";
...
strncpy(edge, "a", 1);
OCI_Prepare(st, "select dd1, ii1 from my_table where report_name = :in_report_name and edge = :in_edge");
OCI_BindString(st, ":in_edge", edge, 1);
or (Method B):
char edge[1] = "";
...
strncpy(edge, "a", 1);
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;");
OCI_BindString(st, ":in_edge", edge, 1);
However, this did not work, even though I could use a debugger and verify edge="a" at the OCI_Prepare statement. Interestingly, if I declared the edge variable as having a length of 2, the code worked. That is, declaring edge as:
char edge[2] = "";
made the code work. Note the database table's column for "edge" is declared as char (1 byte). I also tried varchar2 (1 byte) with the same result.
Anyway I thought that was interesting, but maybe you already knew. Looking at this link:
http://orclib.sourceforge.net/doc/html/group__g__bind.html#ga49988b9ca8f00b55cc3e62ca2385778b
in the "OCI_BindString" section, it suggests this string has a "zero null terminal character" that this second byte may be needed for. If true, perhaps this means when I declare the string length in C, I should add one additional byte (e.g. character) for OCILIB to use for this zero null terminal character.
Thanks again for all of your wonderful support. OCILIB has really been a joy to use!
----- Original Message -----
From: mod...@co...
To: "vincent rogier" <vin...@ya...>
Cc: orc...@li...
Sent: Thursday, April 5, 2012 2:52:37 PM
Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations
Hi Vincent, I tried to follow this link:
http://sourceforge.net/projects/orclib/forums/forum/470801/topic/3406215
to answer my question below. I *think* the required code (see previous question below) should look like:
OCI_Connection *cn;
OCI_Statement *st;
OCI_Statement *st2;
OCI_Resultset *rs;
...
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
...
cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
st2 = OCI_StatementCreate(cn);
OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_BindString(st, ":in_edge", edge, 1);
OCI_BindStatement(st, ":out_resultset", st2);
OCI_Execute(st);
rs = OCI_GetResultset(st2);
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++;
}
However, I run into some funny issues when I place it inside my main program to run it. Here's the structure of OCILIB calls in my program (I execute 3 processes below):
OCI_Connection *cn;
OCI_Statement *st;
OCI_Statement *st2;
OCI_Resultset *rs;
...
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("<db>", "myschema", "myuser", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
st2 = OCI_StatementCreate(cn);
OCI_Prepare(st, ...);
OCI_Bindxxxx(st, ...);
OCI_Execute(st, ...);
...
OCI_Prepare(st, ...); /* this is a different prepare stmt than used above */
OCI_Bindxxxx(st, ...);
OCI_Execute(st, ...);
...
OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_BindString(st, ":in_edge", edge, 1);
OCI_BindStatement(st, ":out_resultset", st2);
OCI_Execute(st);
rs = OCI_GetResultset(st2);
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++;
}
printf("\nd1[0] = %g ", d1[0]);
...
OCI_Cleanup();
return EXIT_SUCCESS;
OCI_Execute executes three times here.
If I run the program, the first OCI_Execute process runs fine, but the SECOND OCI_Execute process does not return the correct values from the database, and the THIRD OCI_Execute process appears not to run (the printf statement doesn't print anything).
However, if I comment out the line of code for "OCI_Statement *st2;", and also comment out the lines of code from "OCI_SetFetchSize(st, pts);" to "printf(...);" and then run the program, the first two OCI_Execute processes run fine.
At this point, if I simply remove the comment from the line "OCI_Statement *st2;" (keeping the THIRD OCI_Execute related lines of code commented out), and run the program, the FIRST OCI_Execute process runs fine but the SECOND OCI_Execute process does not return the correct values from the database. So, simply having that "OCI_Statement *st2;" line of code there seems to cause programs, even without any code related to the THIRD OCI_Execute process.
Am I arranging the calls correctly here? Do you see anything that looks wrong? Thanks for any comments.
----- Original Message -----
From: mod...@co...
To: "vincent rogier" <vin...@ya...>
Cc: orc...@li...
Sent: Thursday, April 5, 2012 10:53:16 AM
Subject: Re: [Orclib-users] [Bulk] newbie question on implementing bulk operations
Hi Vincent, if I want to re-write this successful code
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++;
}
to call a stored procedure in the OCI_Prepare statement, how would I do it? I've tried the following without success:
OCI_SetFetchSize(st, pts);
OCI_Prepare(st, "begin get_data( :in_report_name, :in_edge, :out_resultset ); end;");
OCI_BindString(st, ":in_report_name", report_name, 60);
OCI_BindString(st, ":in_edge", edge, 1);
OCI_BindRef(st, ":out_resultset", rs); /* I also tried OCI_BindObject, OCI_BindStatement, etc. */
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++;
}
Thanks for any comments!
----- 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
------------------------------------------------------------------------------
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
------------------------------------------------------------------------------
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
|
