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

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

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