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

OLEDB and ODP.NET and C# connection

936666 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points