|
From: vincent r. <vin...@ya...> - 2012-04-08 17:22:55
|
Hi, the solution is easy !
#define COLSIZE 101
OCI_Connection *cn;
OCI_Statement *st;
char *array_person_name;
char person_name[COLSIZE] = "";
...
/* 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 * COLSIZE); /* allocate memory for row
pointers */
for(char *p =array_person_name; i=0;i<3;i++) /* try 3 rows */
{
strncpy(p, person_name, 100 );
p+=COLSIZE;
}
if (!OCI_Execute(st)) {
exit(41);
}
if (!OCI_Commit(cn)) {
exit(100);
}
OCI_Cleanup();
return EXIT_SUCCESS;
On Sun, Apr 8, 2012 at 6:59 PM, <mod...@co...> wrote:
> 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
>
--
Vincent Rogier
|
