|
From: <mod...@co...> - 2012-04-09 00:17:33
|
Hi Vincent, Thanks for the reply! I see your function prefers to have one flat array of characters in a contiguous memory block. I'm not able to get the code below working for some reason. It compiles fine, and executes without any errors, but nothing gets placed into the database table. However, if I run the program using gdb, then some garbage gets placed into the database (little question-mark symbols inside a diamond-shaped black background). Not sure what's going on, but I can compare the array "array_person_name" used here as an argument here,
OCI_BindArrayOfStrings(st, ":person_email", (char*) array_person_name, 100, 0 );
for working and non-working code:
The working code declares the array sizes for the compiler (e.g. char array_person_name[3][101];).
(gdb) p array_person_name
$1 = {"John Smith", '\000' <repeats 90 times>, "John Smith", '\000' <repeats 90 times>, "John Smith", '\000' <repeats 90 times>}
(gdb) p array_person_name[0]
$2 = "John Smith", '\000' <repeats 90 times>
(gdb) ptype array_person_name
type = char [3][101]
The non-working code (below), declares the array size at run time (e.g. char *array_person_name;).
(gdb) p array_person_name
$1 = 0x6cff10 "John Smith"
(gdb) p array_person_name[0]
$2 = 74 'J'
(gdb) ptype array_person_name
type = char *
(gdb) p *array_person_name@304
$3 = "John Smith", '\000' <repeats 91 times>, "John Smith", '\000' <repeats 91 times>, "John Smith", '\000' <repeats 91 times>
So it appears the argument "array_person_email" for OCI_BindArrayOfStrings for the working case (e.g. type=char [3][101]) is different than the non-working case (e.g. type = char *). I wonder if that may be causing a problem. If not, and the code looks good, then it must be something outside OCILIB.
Here's the code I'm using:
#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", COLSIZE);
array_person_name = malloc(3 * COLSIZE);
char *p = array_person_name; /* I get an error if this is inside the for() parenthesis.*/
for( i=0;i<3;i++ ) /* try 3 rows */
{
strncpy(p, person_name, 101 );
p+=COLSIZE;
}
if (!OCI_Execute(st)) {
exit(41);
}
if (!OCI_Commit(cn)) {
exit(100);
}
OCI_Cleanup();
return EXIT_SUCCESS;
----- Original Message -----
From: "vincent rogier" <vin...@ya...>
To: mod...@co...
Cc: orc...@li...
Sent: Sunday, April 8, 2012 10:22:26 AM
Subject: Re: [Bulk] Re: [Bulk] [Orclib-users] newbie question on implementing bulk operations
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
|
