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

[vincent r. <vin...@ya...>]

hi,

all string and characters columns are mapped to C null terminated strings.
That's why your variable 'a' must be 2 bytes long.

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

> 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
>



-- 
Vincent Rogier