1 2 Previous Next 19 Replies Latest reply: Jun 28, 2013 10:53 AM by c13dcad9-77aa-484b-b11c-7757681d4cf3 RSS

    OLEDB and ODP.NET and C# connection

    936666
      1) I am trying to code with OLEDB for TimesTen database
      I have used OLEDB Connection to connect TimesTen database

      using System.Data.Oledb;
      connectionString = "Dsn=MYDatabase";

      For OLEDB we need to specify the Provider, the Provider for MS SQL Server is like Provider=SQLNCLI10; and for Oracle Provider is Provider=OraOLEDB.Oracle; like wise what is the provider we need to specify for TimesTen database.

      Whether OLEDB is supported by TimesTen or not.


      2) What is code with ODP.NET for TimesTen database?

      If i use ODP.NET then what is the connection string and what name space i need to specify can i specify the name as Oracle.DataAccess.Client
      using Oracle.DataAccess.Client;
      connectionString = "Dsn=MYDatabase";
      since it is for Oracle can i specify this name for TimesTen also.


      Thanks!
        • 1. Re: OLEDB and ODP.NET and C# connection
          ChrisJenkins
          OLEDB is not supported with TimesTen. You should use ODP .NET.

          If you have a TimesTen installation on Windows then in the QuickStart directory there is an example of an ODP .NET program that works with TimesTen in the directory sample_code\odp.net. I would recommend that you take a look at that to help get you started. For full information on ODP .NET you should consult the oracle ODP .NET documentation.

          Regards,

          Chris
          • 2. Re: OLEDB and ODP.NET and C# connection
            936666
            Hey Chris,

            I have tried using that help but i couldn't find any DSN specified here,
            since i have used DSN for TimesTen
            in the example it has set as

            connStr = "Data Source=" + db + ";user id=" + user + ";password=" + pwd + ";Statement Cache Size=0";
            where as for TimesTen we are creating database in DSN.
            how to modify this.

            If i give
            connStr = "Data Source= MYDatabase"

            ORA-12154: TNS:could not resolve the connect identifier specified

            It means that it is connecting to Oracle database and not the TimesTen database.It is searching in Oracle's TSN and not the TimesTen TSN.


            Thanks!
            • 3. Re: OLEDB and ODP.NET and C# connection
              ChrisJenkins
              If everything is setup properly, the value of the 'db' variable should be a TimesTen DSN (this value is specified on the program command line at run-time in the example):

              Can you please verify that you have:

              1. Installed the latest version of TimesTen, 11.2.2.3.0, for 32 or 64 bit Windows.

              2. Installed the corresponding (32 or 64 bit) Oracle ODP .NET (ODAC) 11.2.0.3 for Windows

              3. Configured things correctly as per the ODP .NET documentation.

              4. Compiled and successfully executed the quicjstart sample program as per the instructions contained in the same directory.

              Thanks,

              Chris
              • 4. Re: OLEDB and ODP.NET and C# connection
                936666
                Hi,

                Already Installed the TimesTen Release 11.2.2.2.0 (32 bit NT) (tt1122_32:53392) 2011-12-23T19:08:17Z
                Installed the corresponding (32 or 64 bit) Oracle ODP .NET (ODAC) 11.2.0.3 for Windows
                I have downloaded ODAC112012,if i tried to install the components i am getting the below error
                Oracle data Access Components for Oracle Server 11.2.0.1.1 can only be installed over an existing Oracle database server 11.2.

                I have downloaded ODTwithODAC112030,if i tried to install the components i am getting the below error
                Oracle data Access Components for Oracle Server 11.2.0.3.0 can only be installed over an existing Oracle database server 11.2.0.3.0

                What is correct or appropriate component for the above version
                • 5. Re: OLEDB and ODP.NET and C# connection
                  ChrisJenkins
                  Hi,

                  A few important things:

                  1. I misled you with my previous answer, sorry. The 'db' value you use with ODP .NET is not a DSN name it is a TNS service name. ODP .NET uses OCI to connect to TimesTen. You need to make sure that your SQLNet/OCI configuration is correct and that the TNS name you use is configured as a TimesTen connection. Please consult the TimesTen OCI documentation for more details.

                  2. The ODP .NET package that you want is the one found here:

                  http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html for 64 bit TimesTen

                  http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html for 32 bit TimesTen

                  It is best to use the XCopy deployment version.You should use the 11.2.0.3.0 release.

                  3. There is a bug in the TimesTen 11.2.2.3.0 release that causes issues when used with ODP .NET. This should be fixed in 11.2.2.4.0 but in the meantime you should use 11.2.2.2.0.

                  Chris
                  • 6. Re: OLEDB and ODP.NET and C# connection
                    936666
                    Hi,

                    I downloaded Xcopy and tried to install but i couldn't find any installation.


                    Usage:
                    install.bat component_name oracle_home_path oracle_home_name [install_dependents]

                    Example:
                    install.bat all c:\oracle myhome (install all components)
                    install.bat odp.net2 c:\oracle myhome true (install ODP.NET 2 and its dependent components)
                    install.bat odp.net4 c:\oracle myhome true (install ODP.NET 4 and its dependent components)
                    install.bat asp.net2 c:\oracle myhome true (install ASP.NET Providers 2 and its dependent compon
                    ents)
                    install.bat asp.net4 c:\oracle myhome true (install ASP.NET Providers 4 and its dependent compon
                    ents)
                    install.bat oledb c:\oracle myhome true (install OraOLEDB and its dependent components)
                    install.bat oo4o c:\oracle myhome true (install OO4O and its dependent components)
                    install.bat oramts c:\oracle myhome true (install ORAMTS and its dependent components)
                    install.bat basic c:\oracle myhome false (install Oracle Instant Client)



                    http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html

                    ODAC112030Xcopy_32bit is the Zip file

                    ------------------------------------

                    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/timesten/tt1121/3_running_dotnet/3_running_dotnet.htm

                    Configure ODP.NET to Use with TimesTen

                    ordermatching =
                    (DESCRIPTION =
                    (CONNECT_DATA =
                    (SERVICE_NAME = ordermatching) (SERVER = timesten_direct)))

                    the configuration is for Oracle TNSNAMES.ORA or for TimesTen TNSNAMES.ORA ?
                    • 7. Re: OLEDB and ODP.NET and C# connection
                      936666
                      since i am getting ORA-12154: TNS:could not resolve the connect identifier specified
                      which means that it is searching in my Oracle TNSNAMES.ORA and not at my TimesTen's TNSNAMES.ORA

                      Thanks!
                      • 8. Re: OLEDB and ODP.NET and C# connection
                        ChrisJenkins
                        With regard to your previous post, can you please clarify what you mean by 'tried to install but i couldn't find any installation'. I don't understand exactly what the problem is.

                        If you get the ORA-12154 error then thsi means that the SQLNet layer is not able to resolve the 'database name' via TNS lookup. Please check that:

                        1. You do not* have ORACLE_HOME environment variabel set.

                        2. You do* have TNS_ADMIN environment variable set and that it _points to the directory that contains your TNSNAMES.ORA file_

                        3. Your TNSNAMES.ORA file has the proper syntax.

                        Chris
                        • 9. Re: OLEDB and ODP.NET and C# connection
                          936666
                          I downloaded Xcopy and installed but there are no component being installed.

                          I'm having Oracle TNSNAMES.ORA and TimesTen's TNSNAMES.ORA where do i modify here in Oracle ORA file or in TimesTen ORA file.
                          You do* have TNS_ADMIN environment variable set and that it _points to the directory that contains your TNSNAMES.ORA file_
                          Where i can see this TNS_ADMIN environment variable set.


                          Thanks
                          • 10. Re: OLEDB and ODP.NET and C# connection
                            ChrisJenkins
                            933663 wrote:
                            I downloaded Xcopy and installed but there are no component being installed.
                            What exact install command did you use? Did the target directory already exist? Were any errors reported?

                            >
                            I'm having Oracle TNSNAMES.ORA and TimesTen's TNSNAMES.ORA where do i modify here in Oracle ORA file or in TimesTen ORA file.
                            The TimesTen one. You do not need to have separate TNSNAMES.ORA files as long as you use different TNS names for Oracle and TimesTen connections.

                            >
                            You do* have TNS_ADMIN environment variable set and that it _points to the directory that contains your TNSNAMES.ORA file_
                            Where i can see this TNS_ADMIN environment variable set.
                            You can view environment variables from a command prompt by using the SET command. Environment variables can also be set using the SET command or from Advanced System Settings (right click on My Computer, choose Properties then Advanced System Settings). Variables set from a command prompt affect only things launched from that command prompt. Variables set at the system or user level affect everything on the system or everything for that user.

                            >
                            >
                            Thanks
                            • 11. Re: OLEDB and ODP.NET and C# connection
                              936666
                              Finally i got it done Thanks Chirs, i have modified TNS_ADMIN in environment variable.

                              I am able to create the table and during the insertion i am having this problem in Insert

                              my code is something like
                              for (int i = 0; i < count; i++)
                              {
                              command.CommandText = "Insert into tablename (Col1, Col2, ...) Values(?, ?,...)"
                              command.Prepare();
                              command.Parameters.AddWithValue("?", 0);
                              command.Parameters.AddWithValue("?", 0);
                              .
                              .
                              .
                              command.ExecuteNonQuery();
                              command.Parameters.Clear();
                              }

                              but i am getting this error
                              [Oracle.DataAccess.Client.OracleException] = {"ORA-00900: TT0946: SQL command is no longer valid -- file \"cmdExec.c\", lineno 4103, procedure \"sbSqlCmdExec\""}

                              Thanks
                              • 12. Re: OLEDB and ODP.NET and C# connection
                                ChrisJenkins
                                TimesTen error 946 means that the prepared statement is no longer valid. There are various reasons why this may happen but in most of them this should be invisible to the application and the statement should be re-prepared under the covers. I have some questions:

                                1. Does your code do any other database operations other than execute inserts?

                                2. Is there any other concurrent activity on the TimsTen database while your test is running?

                                3. Why are you still preparing the INSERT statement inside the loop? Does the problem disappear of you move the prepare to outside the loop (like it should be)?

                                4. The 'Command' object that you are using must have been 'created' from some kind of connection object. How exactly does your code do that? What is the scope of the connection object?
                                It must remain in scope and not get closed/garbage collected while the Command objects are still needed.

                                Chris
                                • 13. Re: OLEDB and ODP.NET and C# connection
                                  936666
                                  1. Does your code do any other database operations other than execute inserts?
                                  I have individual .CS file for each operation once i finish my Insert i will do other operations it may be Select/Update/Delete it is all having in different .CS files
                                  2. Is there any other concurrent activity on the TimsTen database while your test is running?
                                  As of now there is no concurrent activity while i run my test.
                                  3. Why are you still preparing the INSERT statement inside the loop? Does the problem disappear of you move the prepare to outside the loop (like it should be)?
                                  As i was trying in different way one of the way of my code is like that we can have even outside no problem
                                  4. The 'Command' object that you are using must have been 'created' from some kind of connection object. How exactly does your code do that? What is the scope of the connection object?
                                  It must remain in scope and not get closed/garbage collected while the Command objects are still needed.

                                  I have created connection above my loop
                                  some thing like
                                  connectionString = "Data Source=MYDatabase;User ID=xxxxxx;Password=xxxxxxxxx";
                                  for (int i = 0; i < count; i++)
                                  {
                                  command2.CommandText = "Insert into Defecttest (Col1,     Col2) values (1,2)";
                                  }

                                  even this makes same problem for me.
                                  [Oracle.DataAccess.Client.OracleException] = {"ORA-00900: TT0946: SQL command is no longer valid -- file \"cmdExec.c\", lineno 4103, procedure \"sbSqlCmdExec\""}
                                  this occurs when i run this for the second time i.e i am giving the input( Assigning the count value) as 5,10,15,20,..... for 5 it is getting inserted while it comes for 10 i am getting the above error. for 5 it is getting inserted only when it runs for the second time i am getting the error. once i insert my 5 records i will disconnect my connection and will create new connection and then i am starting to insert the values but still error the input for this is read from xml file.

                                  Thanks!
                                  • 14. Re: OLEDB and ODP.NET and C# connection
                                    936666
                                    In order to check the consistence i have created the table and populating the data and writing the output to a text file and dropping the table again for the second cycle i am creating the table, populating the data and writing the output to a text file and dropping the table and so on.... till i read the input from my xml file.
                                    I am getting this Error when i am recreating the table for second time.
                                    NOTE: while insertion only i am getting the problem and not at creation of the table. i.e i have created the table and then while insertion it fails

                                    my code will look like something as

                                    xml file values like 5,10,15......
                                    reading the input from xml file

                                    using System;
                                    using System.Collections.Generic;
                                    using System.Linq;
                                    using Oracle.DataAccess.Client;
                                    using System.IO;

                                    private OracleConnection connection;
                                    private OracleCommand command;

                                    connectionString = "Data Source=MYDatabase;User ID=xxxxxx;Password=xxxxxxxxx";
                                    command = connection.CreateCommand();
                                    command.CommandText = "Create Table DefectTest (Col1     Char(1),Col2     Char(1))";
                                    command.ExecuteNonQuery();
                                    ------------- for the second time i am able to create the table and while insertion part comes--------------------
                                    for (int i = 0; i < count; i++)
                                    {
                                    command2.CommandText = "Insert into Defecttest (Col1, Col2) values ('A','B')";
                                    command.ExecuteNonQuery();
                                    }

                                    [Oracle.DataAccess.Client.OracleException] = {"ORA-00900: TT0946: SQL command is no longer valid -- file \"cmdExec.c\", lineno 4103, procedure \"sbSqlCmdExec\""}

                                    Thanks
                                    1 2 Previous Next