C Oracle Db connections

[uffmoc]

Problem:

I have an issue in which I want to move some data from one table to two other tables. One table resides on one server, and the others on anoter. I guess my first question is how I need to know how to go about doing this, or if it is permissable to have two SQL connections open in one program? As I am using the SyLogInit(a,b,c,d) function to initialize an Oracle connection, I need to know if I can have multiple of these in order to transfer data from one table to another?

[quzah]

And this is a C question, how exactly?

Quzah.

[jim mcnamara]

Maybe because it's coded in Pro*C. It's not a C question. Please move it. Tech Board.

Stuff like this should use PL/SQL, sqlldr and run from scripts. If this is going to be production code, you have a serious design problem. Pro*C supports one connection at a time.

What you SHOULD do is get your DBA to create a database link, then you can connect to a single SID and play with tables across the link. Don't insert into a table on the other side of a link, read only from a link, insert locally.


If you insist on your current design, you will have to
1. connect to SID1
2. extract the table data either to a huge memory buffer or to a file
3. disconnect
4. reconnect to SID2
5. dump file or buffer into tables

[uffmoc]

It is a C problem in that a C script is used to update a table on one server based on records obtained from another server. I agree that this is a mess, unfortunately this was thrown at me and I do not have the power (yet) to suggest a redesign. In fact, the person who started this procedure kind of abandoned it and I am expected to simply pick up where he left off without being able to communicate with him. Anyhow, enough of the sob story...Thanks for the suggestion and any other recommendations will be greatly appreciated.

[hk_mp5kpdw]

Can you export a dump of the table on the one server and then import it onto the new server? This will create a table with the same name on the new server (careful if one already exists with the same name). Then to duplicate this table again into a different table on the new server you can execute a command in sqlplus/sqlworksheet like:

create table new_table_name unrecoverable as select * from old_table_name;

That will create a table called new_table_name that contains the same data as in table old_table_name. So you would have two tables with different names but holding the same data.

[Tankndozer]

I'm doing something similar myself atm working with Oracle 8i on 6 UNIX servers and a Windows 2000 workstation.

What platform are you using?

getting your DBA to set up a link between the databases as suggested is definitely the easiest way to go about doing this! If you were connected to "database1" you could then simply refer to the tables in "database2" using the alias name set up by your DBA e.g.

      SELECT t1.field1
          FROM table1 t1, table2 t2, table3@database2 t3

Then insert whatever conditional statements you need.

Alternatively, you could also do the following (reading info from one database and substituting the retrieve information into a query, update or insert for the second):

1. get Pro*C to connect to one database and perform the necessary queries
2. generate a file containing the SQL or preferably PL/SQL to perform any update/queries/inserts on the second database with the return data from Pro*C replacing the any sections where you would have directly queried the first database
3. Invoke (for example) sqlplus with whatever options you like giving the produced file as input (make sure to put an exit statement at the end of the file so that it will return control to the C program at the end).

Further still you could avoid using Pro*C at all and instead use the C programme to manage multiple PL\SQL queries and their output files to generate any necessary queries and invoke each of them using several different non-simultaneous connections to the database via invocations of sqlplus.

Hope this helps.

[uffmoc]

"Further still you could avoid using Pro*C at all and instead use the C programme to manage multiple PL\SQL queries and their output files to generate any necessary queries and invoke each of them using several different non-simultaneous connections to the database via invocations of sqlplus."

Thanks for the suggestion. This sounds like the way I may want to go on this. If possible could inform me of where I may be able to get detailed info on how to go about implementing this. Again, thank you.

[Tankndozer]

Sorry for the delay in my reply- I've been away for the weekend!

I can send you a couple of programs that demonstrate how it is done if you like, if you could just tell me what platform you're on. The way it which you would set this up is dependent on whether you're on a UNIX, Linux or Windows machine. Never tried it on a mac before. You probably won't find any resources on the internet describing how to do this. It's just something I started doing myself in work so that others with no knowledge of C programming could update scripts without having to recompile the program.

[uffmoc]

I am using Windows XP and the tables reside on our AIX server. if you need an email address, yo may use:

[email protected]

Thanx again.

[Tankndozer]

What you need to do goes like this:

1. Write out a rough structure of all the queries you'll need to do on either database

2. Write a C programme that will allow you to edit files (I prefer to use a large buffer myself) maybe something like this:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_NO_STR 1000
#define MAX_LGT_STR 200

int main(int argc, char *argv[]) {
   int i=0, j;
   char **buffer;
   FILE *fp, *fopen();

   buffer = malloc( MAX_NO_STR * sizeof(char *) );
   for(i=0; i < MAX_NO_STR; i++) {
      buffer[i] = malloc( MAX_LGT_STR * sizeof(char) );
   }
   if(buffer == NULL) {
      puts("Out of memory");
      exit(1);
   }

   if( (fp = fopen(argv[1], "r")) == NULL) {
      puts("File opening for reading failed");	 
      exit(1);
   }

   while( (fgets(buffer[i], MAX_NO_STR, fp) ) != NULL ) {
/*Enter conditional statements to test where to insert information here 
Use comments in the file you're going to edit to make finding these points easier 
e.g.
      if( strncmp(buffer[i], "//Insert new", 12) == 0 ){

      }
*/
      i++;
   }
   fclose(fp);

// Do any other editing here
   
   if( (fp = fopen(argv[1], "w")) == NULL) {
      puts("File opening for writing failed");     	 
      exit(1);
   }
   
   for(j=0; j<i; j++) fputs(buffer[j], fp);
 
   fclose(fp)
 
   for(i=0; i<MAX_NO_STR; i++) free(buffer[i]);
   free(buffer);
   
   return 0;
}

3. within your program use calls like this to run various sqlplus scripts USE PL/SQL IF YOU CAN!

system("sqlplus -s username/password @script.sql argument1 argument2 >file_to_save_output 2>&1");

The -s supresses server output to save you screens worth of info you don't need (DON'T USE DURING TESTING!). Use the following to display any input you require (requires PL/SQL):

DBMS_OUTPUT.PUT_LINE('Insert your message here' || variable_input || 'End of message');

the 2>&1 ensures that all error information is directed to the output file also, but if your system doesn't like it, it can be deleted.

4. Use the editing code from your C programming to get any info necessary from the output file generated by your script(I recommend strtok() for parsing, but that's just a personal preference http://www.rt.com/man/strtok.3.html)

5. Use the C program to insert any information retrieved from running the first script, into the second script.

6. Run the second script with the same syntax as the first.

7. Sit back and admire your handywork

NOTE: make sure to end your SQL or PL/SQL scripts with exit as the last line in the file- this ensures sqlplus will return control to the C program
If the sqlplus script appears to hang during testing, type control +C and then exit even if you don't see a prompt. That usually gets you out.

There's a little bit of work in this, but I hope it's of use to you. Just remember, as always, the key to writing a program that works without confusing yourself is to plan your structure before writing a single line of code!

Let me know if you need any more help or clarification.

[uffmoc]

That actually helps out immensely. I cannot thank you enough, but I can try. Thank you for taking time to assist me with this issue, you are a saint (a programming saint, I cannot vouch for anything else you do )

[Tankndozer]

You're welcome. I get enough help around here myself- it's only fair that on one of the few occasions I may be of use to someone else that I do what I can!