C Oracle Db connections

This is a discussion on C Oracle Db connections within the Tech Board forums, part of the Community Boards category; Problem:
I have an issue in which I want to move some data from one table to two other tables. ...

C Oracle Db connections

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?

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

Re

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.

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:

"Owners of dogs will have noticed that, if you provide them with food and water and shelter and affection, they will think you are god. Whereas owners of cats are compelled to realize that, if you provide them with food and water and shelter and affection, they draw the conclusion that they are gods."
-Christopher Hitchens

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.

Code:

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.

Re

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

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.

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!

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 )