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

  1. #1
    Registered User
    Join Date
    Jul 2004
    Posts
    9

    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?

  2. #2
    ATH0 quzah's Avatar
    Join Date
    Oct 2001
    Posts
    14,826
    And this is a C question, how exactly?

    Quzah.
    Hope is the first step on the road to disappointment.

  3. #3
    .
    Join Date
    Nov 2003
    Posts
    307
    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
    Last edited by jim mcnamara; 07-13-2004 at 03:29 PM.

  4. #4
    Registered User
    Join Date
    Jul 2004
    Posts
    9

    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.
    Last edited by uffmoc; 07-14-2004 at 07:03 AM.

  5. #5
    Registered User hk_mp5kpdw's Avatar
    Join Date
    Jan 2002
    Location
    Northern Virginia/Washington DC Metropolitan Area
    Posts
    3,803
    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:

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

  6. #6
    Goscinny or Uderzo?
    Join Date
    Jun 2004
    Posts
    33
    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.

    Hope this helps.
    Last edited by Tankndozer; 07-16-2004 at 05:50 AM.
    Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?

    (How much wood would a wood chuck cut if a wood chuck could chuck wood?)

  7. #7
    Registered User
    Join Date
    Jul 2004
    Posts
    9

    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.

  8. #8
    Goscinny or Uderzo?
    Join Date
    Jun 2004
    Posts
    33
    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.
    Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?

    (How much wood would a wood chuck cut if a wood chuck could chuck wood?)

  9. #9
    Registered User
    Join Date
    Jul 2004
    Posts
    9
    I am using Windows XP and the tables reside on our AIX server. if you need an email address, yo may use:

    uffmoc@hotmail.com

    Thanx again.

  10. #10
    Goscinny or Uderzo?
    Join Date
    Jun 2004
    Posts
    33
    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:
    Code:
    #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!
    Code:
    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):
    Code:
    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.
    Last edited by Tankndozer; 07-19-2004 at 07:46 AM.
    Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?

    (How much wood would a wood chuck cut if a wood chuck could chuck wood?)

  11. #11
    Registered User
    Join Date
    Jul 2004
    Posts
    9
    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 )

  12. #12
    Goscinny or Uderzo?
    Join Date
    Jun 2004
    Posts
    33
    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!
    Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?

    (How much wood would a wood chuck cut if a wood chuck could chuck wood?)

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. problem with bootstrap
    By abachler in forum Linux Programming
    Replies: 0
    Last Post: 01-23-2009, 09:41 PM
  2. Using/Learning DTL for my DB connections
    By csonx_p in forum C++ Programming
    Replies: 2
    Last Post: 10-08-2008, 11:23 PM
  3. Replies: 10
    Last Post: 05-18-2006, 11:23 PM
  4. Help required in connecting to oracle db
    By Caughtyou in forum C Programming
    Replies: 2
    Last Post: 01-26-2006, 03:14 PM
  5. if is faster than switch?
    By skorman00 in forum C++ Programming
    Replies: 32
    Last Post: 03-06-2004, 12:15 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21