9 Replies Latest reply: Jun 9, 2009 9:49 PM by Todd Little-Oracle RSS

    Tuxedo server and Oracle Database

    JoshTheFlame
      Hi All,

      I need to write a program from which I want to access my Oracle Server in my LAN environment , run a query and then get the result for comparison and send the response back to tuxedo client.

      Any idea how to talk to Oracle server or any other servers using Tuxedo Client and Server. or which sample application I should follow to test the example. thanks
        • 1. Re: Tuxedo server and Oracle Database
          699724
          HI Josh

          Even i was pondering on this same question i have tried out something well it has tons of errors but i will be happy if this could be of any help to you. i was also trying to check whether i could do multi threading but was not able to .

          #include <pthread.h>
          #include <stdio.h>
          #define NUM_THREADS 5
          #include <DbManager.h>
          #include <iostream>
          #include <string.h>
          #include <stdlib.h>
          #include <stdio.h>
          #include <ctype.h>
          #include <atmi.h>     /* TUXEDO Header File */
          #include <userlog.h>     /* TUXEDO Header File */

          using namespace std;

          using namespace oracle::occi;

          const string sqlString1("select data1,data2,data3 from Status_Table
          where status='data created'");

          const string sqlString2("update Status_Table set status ='calling tuxedo'
          where status='data created'");
          const string sqlString4("update Status_Table set status ='completed'
          where status='calling tuxedo'");



          int main(int argc, char **argv)

          {
               int a,c;
               char *b;
               a= argc;
               *b= argv[1];
               
               pthread_t threads[NUM_THREADS];
               int rc;
               long t;
               for(t=0; t<NUM_THREADS; t++)
               {
                    printf("In main: creating thread %ld\n", t);
                    rc = pthread_create(&threads[t], NULL, QueTuxedo, a,b);
                    if (rc)
                    {
                         printf("ERROR; return code from pthread_create() is %d\n", rc);
                         exit(-1);
                    }
               }
               pthread_exit(NULL);

          }




          QueTuxedo(int x, char *y)

          {

               tpsvrinit(int x, char *y)
               {
                    (void)userlog("Welcome to the simple server");

                    /* Connect to the database */
                    if (tpopen() == -1)
                    {
                         (void)userlog("Failed to open database due to %s \n", tpstrerror(tperrno));
                         return(-1);
                    }
                    return(0);
               }


               /**********************************************/
               /***************Service************************/
               /*********************************************/

               MYSERVICE(TPSVCINFO *rqst)

               {
                    // Initialize OracleServices

                    DbManager* dbm = NULL;
                    OracleServices* oras = NULL;

                    Statement *stmt1 = NULL;
               Statement *stmt2 = NULL;
               Statement *stmt3 = NULL;
               Statement *stmt4 = NULL;


                    ResultSet *resultSet = NULL;

                    try

                         {

                              // Obtain OracleServices object with the default args.
                              // The default args creates OracleServices with an environment of
                              // Environment::OBJECT|Environment::THREADED_MUTEXED

                              dbm = new DbManager(userName);
                              oras = dbm->getOracleServices();

                              // Obtain a cached connection

                              Connection * conn = oras->connection();
                    
                              check_transaction=tpgetlev(); /* check whether the connection is established or not */
                    
                              if(check_transaction==0)
                              {
                                   if(tpbegin(3,0)==-1)
                                   {
                                        (void)userlog("tpbegin: failed due to %s \n", tpstrerror(tperrno));
                                   goto end;

                                   }
                              }
                    


                              // Create a statement

                              stmt1 = conn->createStatement(sqlString1);
                              stmt2 = conn->createStatement(sqlString2);
                              stmt4 = conn->createStatement(sqlString4);


                              int data1,data2,data3;

                              string status;

                              // Execute query to get a resultset

          stmt2.executeUpdate (); //change status to calling tuxedo


                              resultSet = stmt1->executeQuery(); //Fetch the records
                              while (resultSet->next())
                              {
                                   status = resultSet->getString(1); // get the first      returned by the query;
                                   data1 = resultSet->getInt(2); // get the second column returned by the query
                                   data2 = resultSet->getInt(3); // get the third column returned by the query
                                   data3 = data1 + data2;
                                   stmt3 = conn->createStatement("update status_table set data3='data3' where status = 'calling tuxedo'");
                                   stmt3.executeUpdate ();
                              }

                              stmt4.executeUpdate ();

                              tpreturn(TPSUCCESS, 0, rqst->data3, 0L, 0);

                              if (transaction_started_by_me == 0)
                              {
                                   if (tpcommit(0) == -1)
                                   {
                                   (void)userlog("tpcommit: failed due to %s \n", tpstrerror(tperrno));
                              }
                              }
                              
                              // Close ResultSet and Statement

                              stmt->closeResultSet(resultSet);
                              conn->terminateStatement(stmt);
                    }

                    catch (SQLException& ex)
                    {
                         if (dbm != NULL)
                         {
                              dbm->rollbackActions(ex, stmt, resultSet); // free resources and rollback transaction

                         }

                    }
                    tpterm();
          pthread_exit(NULL);

          }

          Edited by: user11119905 on 11-May-2009 05:50
          • 2. Re: Tuxedo server and Oracle Database
            648139
            Hi,

            you can use oracle Pro*C to do this. Here is a simple example. You need to create a table test with column name db_name and compile this program using oracle proc compiler and the use buildserver command to build the server.

            #include <stdio.h>          /* UNIX */
            #include <atmi.h>          /* TUXEDO */
            #include <Uunix.h>          /* TUXEDO */
            #include <userlog.h>          /* TUXEDO */
            #include "sql.h"

            #define DESC_LEN           1000
            #define DB_LEN                128
            #define EROR_CODE_LEN           6

            EXEC SQL INCLUDE SQLCA;

            EXEC SQL BEGIN DECLARE SECTION;
                 char dbname[DB_LEN];
                 char errorCode[EROR_CODE_LEN];
                 char description[DESC_LEN];
            EXEC SQL END DECLARE SECTION;


            /* ARGSUSED */
            int
            #if defined(__STDC__) || defined(__cplusplus)
            tpsvrinit(int argc, char **argv)
            #else
            tpsvrinit(argc,argv)
            int argc;
            char **argv;
            #endif
            {
                 /* Open database */
                 if (tpopen() == -1) {
                      (void)userlog("tpsvrinit: failed to open database due to ");
                      switch (tperrno) {
                      case TPEOS:
                           (void)userlog("tpopen failed, Unix error %d\n",Uunixerr);
                           break;
                      case TPERELEASE:
                           (void)userlog("tpopen failed, Incompatible release\n");
                           break;
                      default:
                           (void)userlog("tpopen failed, tperrno: %s\n",tpstrerror(tperrno));
                      }
                      argv = argv; argc = argc;     /* unused */
                      return(-1);
                 }
                 (void)userlog("RAC Test Server Started Succsussfully");
                 return(0);
            }

            void tpsvrdone()
            {
                 /* Close database */

                 if (tpclose() == -1) {
                      (void)userlog("tpsrvdone: failed to close database due to ");
                      switch (tperrno) {
                      case TPEOS:
                           (void)userlog("tpclose failed, Unix error %d\n", Uunixerr);
                           break;
                      case TPERELEASE:
                           (void)userlog("tpclose failed, Incompatible release\n");
                           break;
                      default:
                           (void)userlog("tpclose failed, tperrno: %d\n",tperrno);
                      }
                 }
                 (void)userlog("RAC Test Server shutdown Succsussfully");
            }

            #ifdef __cplusplus
            extern "C"
            #endif
            void
            #if defined(__STDC__) || defined(__cplusplus)
            TEST(TPSVCINFO *rqst)
            #else
            TEST(rqst)
            TPSVCINFO *rqst;
            #endif
            {

                 EXEC SQL
                 ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

                 memset(dbname, '\0', sizeof(dbname));
                 memset(description, '\0', sizeof(description));
                 memset(errorCode, '\0', sizeof(errorCode));
                 
                 userlog("In Service TEST");
                 
                 EXEC SQL SELECT db_name
                      INTO :dbname
                      FROM test;
                 
                 if(SQLCODE != 0) {
                      userlog("Error TEST: - %s :[%s]:",SQLERRMSG, errorCode);
                 }
                 else {
                      userlog("TEST:Success:");
                      userlog("Database Name: %s", dbname);
                 }
                 
                 /* Return the transformed buffer to the requestor. */
                 tpreturn(TPSUCCESS, 0, rqst->data, 0L, 0);
            }
            • 3. Re: Tuxedo server and Oracle Database
              699724
              Hi Vijendra

              Have gone through the mentioned code but was little confused like here i was able to figure out how to open n close the DB but wasen't very clear how the connectivity is established like where should i be mentioning the port no n user name of the DB incase it is present on a differnt server.

              Thanks
              Chadaram

              Edited by: user11119905 on 03-Jun-2009 04:21
              • 4. Re: Tuxedo server and Oracle Database
                Todd Little-Oracle
                Hi Chadaram,

                You don't specify that in your code. Instead that information is contained in the OPENINFO string for the resource manager specific TMS process that you build. Each group of servers in Tuxedo is associated with one and only one resource manager. Associated with each group is a resource manager specific TMS process (for Oracle database this is usually called TMS_ORA, ORATMA, or something similar). The resource manager specific TMS is built using the buildtms command in Tuxedo. The TMS then uses the OPENINFO string to determine how to connect to the resource manager. Thus your application code if executed in the context of a transaction doesn't need to know or specify the connection information.

                Regards,
                Todd Little
                Oracle Tuxedo Chief Architect
                • 5. Re: Tuxedo server and Oracle Database
                  699724
                  Thanks a ton Todd i didnt have a idea of this but after your explaination i have checked out the Groups section of the Ubb file and have come to know about the OPENINFO. I will try to put these changes and try to execute the programm and will get back incase i have any doubts.

                  Thanks
                  Chadaram
                  • 6. Re: Tuxedo server and Oracle Database
                    699724
                    Hi Todd

                    Could you pls let me know where n how i could give the Login Id and password to connect to the DB. Should i be mentioning the Login id and password inside the OPENINFO

                    OPENINFO="<DB Interface>:<fsconfig>:<DB Name>:Mode"

                    Now iam confuse where to mention the login Name and Password her coz i have surfed out some sites for this and some of them mention that we have to mention it in the OPENINFO where as there are some where they have asked to mention that info in the SERVER section of the ubb file.

                    Here iam trying to access "Oracle 10g DB" with Tuxedo6.5 so what could be the possiable interface.

                    Thanks
                    Chadaram

                    Edited by: user11119905 on 04-Jun-2009 22:49
                    • 7. Re: Tuxedo server and Oracle Database
                      Todd Little-Oracle
                      Hi Chadram,

                      I'm not a database guy, so how any specific database (including Oracle DB) uses its OPENINFO string is not really my area of expertise. But, my understanding is that the only way to pass username/password info to Oracle DB is via the OPENINFO string. To pass the username/password to Oracle DB in the OPENINFO you add the +Acc=P/username/password as in:

                      [OPENINFO="Oracle_XA: Oracle_XA+Acc=P/Scott/*****+SesTm=30+LogDit=/tmp"|http://download.oracle.com/docs/cd/E13161_01/tuxedo/docs10gr3/rf5/rf5.html#wp1532123]

                      Regards,
                      Todd Little
                      Oracle Tuxedo Chief Architect
                      • 8. Re: Tuxedo server and Oracle Database
                        699724
                        Hi

                        i am using HP-UX aCC compiler and have my tuxedo6.5 here for contacting the oracle10g server i have included the below 3 header files

                        #include<sqlca.h>
                        #include<sqlda.h>
                        #include<sqlcode.h>


                        but still when am executing the code which has a sql quiry like

                        exec sql select <something> from <something>

                        iam getting an error which says:

                        error #2020: identifier "exec" is undifiened


                        Pls help me out.

                        Edited by: user11119905 on 09-Jun-2009 04:14
                        • 9. Re: Tuxedo server and Oracle Database
                          Todd Little-Oracle
                          Hi,

                          This is really a ProC (?) question and not a Tuxedo question, but I think you may need to put this in your source code instead of those includes:

                          EXEC SQL include sqlca;


                          You'll have better luck in the database forums getting this answered.

                          Regards,
                          Todd Little
                          Oracle Tuxedo Chief Architect