9 Replies Latest reply on Jan 20, 2006 8:54 AM by Billy~Verreynne

    oraclient9i.dll error in multi threaded delphi server application

    482182
      I created a multi threaded server application in delphi using oracle9i and indy server components. When I run my application, I am getting an error "oraclient9i.dll" error when executing my SQL statements. I have check communication between my server application and the client application without using oracle and its working fine, its only when I started executing SQL statements when I got this error.
      Can anybody help me with this problem or point me to the right direction on how to resolve this issue.

      thanks
        • 1. Re: oraclient9i.dll error in multi threaded delphi server application
          Kamal Kishore
          When I run my application, I am getting an error "oraclient9i.dll" error when executing my SQL statements.
          That is not a descriptive error message you have there. Are you sure that is how the error message gets reported?

          Please post a more descriptive error message and if possible the more detailed outline (with some sample code) of how the application connects to Oracle and how it executes the statements.
          • 2. Re: oraclient9i.dll error in multi threaded delphi server application
            Billy~Verreynne
            How do you access Oracle? Via BDE, dbExpress or something else (e.g. ODBC, ADO, OLEDB, .NET etc.)

            Does the the Oracle connectivity work when not using threading? The DLL that should be configured for BDE for example, should be something like OCIxx.DLL if I recall correctly.

            As for multithreading - you cannot execute multiple TQuery/TTable/TStoredProc methods at the same time (via different threads) on the same TDatabase and TSession (TSession? - I think it is called TSession - been some time since I last used Delphi as a database client).

            Reason: an Oracle session itself is not multithreaded. It can only handle a single request at a time.

            Thus you will need to dynamically create a TSession and TDatabase in each thread, for that thread to use. Note that names must be unique, so you need to ensure a unique name each for the TSession and TDatabase objects in each thread (e.g. use the thread id as part of the name).

            Also suggest that you reconfigure the TNS alias you use on the Delphi client (usually the TNSNAMES.ORA file) to include "(SERVER=SHARED)". This will request a shared server connection on the Oracle Server side - having multiple sessions (1 per thread) per client can have a very large Oracle server process footprint on the Oracle server. Shared server reduces the footprint and scales very well, which will enable Oracle to better support that many sessions from your Delphi clients.
            • 3. Re: oraclient9i.dll error in multi threaded delphi server application
              482182
              This is the exact error message that I am getting.

              Project INBOUND.exe raised exception class EAccessViolation with Message ‘Access violation at address 60667232 in module ‘Oraclient9.dll. Read of address 0000000C’

              As I understand that when I use the indy server component that when a connection is made by the client application, it creates a seperate thread for this connection and process the data. Any other connection will be on different threads and this uses blocking calls. I am only using one oracle session for this application and set the session property to be threadsafe.
              • 4. Re: oraclient9i.dll error in multi threaded delphi server application
                482182
                I am using DOA to access oracle and the connectivity is working fine when not using threading. There is a threadsafe property on DOA that I have set to True and all the datasets and query are also set the same way.
                Indy server components works differently, it uses blocking call. when a connection is made by the client, a thread is created for that connection. I have tried this without connecting to oracle and simply receiving and sending back data and it works fine, its only when I connected with oracle and use queries to fetch and update data on oracle.

                If I am going to create a different session for each thread, How would I do that in oracle? Can you give me some samples if you have any?

                thanks


                ERROR Message that I got.

                Project INBOUND.exe raised exception class EAccessViolation with Message ‘Access violation at address 60667232 in module ‘Oraclient9.dll. Read of address 0000000C’
                • 5. Re: oraclient9i.dll error in multi threaded delphi server application
                  Billy~Verreynne
                  The Indy components cannot turn an Oracle server session into a multi-threaded capable session. An Oracle session is handled by a serialised server process - i.e. a single Unix processs or a single Windows thread. That process is not capable of multi-processing. (the Oracle® Database Concepts explains this Chapter 9, Process Architecture).

                  An Oracle session can only execute a single client instruction at a time. E.g. parse a SQL, fetch a row, create a table, run PL/SQL, etc.

                  You cannot (from any client language/tool) use threads and fire off multiple instructions to that single Oracle session. It simply will not work.

                  Each thread must open its own Oracle sever session. In Delphi it means that each thread (or Indy Thread), must have its own TSession and TDatabase objects.

                  The DDL exception is likely because of that. (multiple threads attempting to write and overwrite at the same time, the same piece of memory allocated by the DLL for that single Oracle session)
                  • 6. Re: oraclient9i.dll error in multi threaded delphi server application
                    482182
                    I have tried what you suggested. I have created a seperate TOracleSession on each thread that I create on the OnConnect event however I am having Problems using the oraclesession created on the OnExecute event. Somehow it is still executing the SQL that I have created on the main form where I first opened an oraclesession component created on the main form.
                    Do you think that It would work if I create an instance of the TOracleDatasets and TOracleQuery on the OnExecute event and also at the same time create my TOracleSession on this event and continue processing the data receive from the client server.

                    thanks
                    • 7. Re: oraclient9i.dll error in multi threaded delphi server application
                      Billy~Verreynne
                      > I have tried what you suggested. I have created a
                      seperate TOracleSession on each thread that I create
                      on the OnConnect event however I am having Problems
                      using the oraclesession created on the OnExecute
                      event. Somehow it is still executing the SQL that I
                      have created on the main form where I first opened an
                      oraclesession component created on the main form.

                      It sounds then like the TOracleSession object in the thread is a copy of the one in the main thread/form.

                      > Do you think that It would work if I create an
                      instance of the TOracleDatasets and TOracleQuery on
                      the OnExecute event and also at the same time create
                      my TOracleSession on this event and continue
                      processing the data receive from the client server.

                      I've never used the Indy components for threading. The default TThread class worked just fine for me.

                      What I used to do is define the session and database objects as privates in my new thread class (let's call it TThreadSQL) - which was subclassed from TThread.

                      The constructor of this new TThreadSQL class did the following (writing here purely from memory - have not done Delphi for some time now):
                      constructor TThreadSQL.Create( TNSalias, username, password : string );
                      // constructor is called with the Oracle session connection details
                      begin
                        inherited Create; // call the parent class constructor
                        CreateOracleSession; // call own private method to create an Oracle connection
                      end;


                      The CreateOracleSession method would then:
                      - create a BDE Session (TSession) object
                      - create a BDE Database (TDatabase) object, using the BDE Oracle native driver and an Oracle TNS alias plus username and password for connection

                      The destructor would close the connection. The Execute method which is used to fire up the thread, would use a TQuery object (or whatever) to execute a SQL using it owns connection.
                      • 8. Re: oraclient9i.dll error in multi threaded delphi server application
                        482182
                        I have done what you have told me and it all work fine. I appreaciate the help that you gave me.

                        Thanks a lot.
                        • 9. Re: oraclient9i.dll error in multi threaded delphi server application
                          Billy~Verreynne
                          Good stuff that you've it working.

                          One issue though - terminating threads. That is a problem as when the thread has made an call to Oracle, it is blocked. I.e. the call (such as making a TQuery object active) goes to the Oracle OCI driver on the client that passes it to the database server process. You only get control back on the thread side once that call is completed.

                          So should the thread fire off a complex SQL and after 10 minutes you want to kill that thread (which is still waiting for the TQuery active to complete), it is a problem.

                          You cannot tell the thread to terminate - it is busy waiting on a blocking call. You can forcible destroy it.. but that means a mess. Not only can you crash your application (the thread runs in your main thread's address space), but even if successul the Oracle server will not know that the thread is dead. It will happily continue processing that SQL to completion. Only when it wants to return the result of that to the client, it will discover the client is no longer there.

                          The Oracle Call Interface supports interrupting an existing call (like SQL*Plus does - allowing to press CTRL-BREAK when it is busy running a query). However, the standard Delphi database classes do not support it.

                          There are work arounds this - but none of them really seamless...

                          Anyway, this was true some time ago - maybe there are now subclasses specifically for Oracle that address this. I knew of some commercialware ones back then, but these were rather expensive.