Devart Forums

goethals.f

Hello,

Just to be sure about how it should work according to the devart docs. When returning to the pool, do you really compare against the time of creation (the time of first .open())

or

do you compare against the last time the connection is used (by a SQL-command etc...)

*) When using 'Pooling=True' (and all other default).

When closing the connection it returns to the pool --> it should be really closed directly, because time between create and close is always >0 and default-Lifetime==0 --> so the connection should be distroyed according the docs ?

Or does Lifetime==0 means, no destroying at all ?

*) when a connection sits in the pool, does dotconnect checks on a regular base is the lifetime is exceeded, or does it only happen when connection is returned to the pool and afterwards no more ?

So in this case, the connection will only be destroyed if lifetime is exceeded at moment of close, in all other cases it will be destroyed based on the server (default) settings. Am I correct ?

Reading some info from oracle: it looks like the pooling-services has a clean-up default interval of 3 minutes. (http://download.oracle.com/docs/cd/B193 ... ecting.htm)

If I now really understand everything correct, the concept of keep the oracle connection X-minutes open after the LAST usage (or the real time of .Close() called, independant of the original creation time is not really possible if lifetime-interval are calculated on the real creation-time.
Maybe simple not possible with Oracle (independant of usage of Dotconnect or not)

For now I will use the current situation - and the (server-default) pooling of 5 minutes. Which is fine for me, the only intention was to possible shorten this time (not to extend the interval)

Thx
Fred

goethals.f

Hello,

I have a question about using the pooling facilities from Oracle Dotconnect, when using in Direct==False mode (using OCI call's)

We should like to have the following situation:

A number of different C# DLL's are created, and within each DLL's there is the needed logic to connect to the Oracle DB and fetch the info. Each DLL's covers a different Business and connects to DB using different userID etc...
These DLL's could be seen as a library of functionalities towards the DB itself

When developing a specific application, we alwasy have an application specific connection to the DB + should use a number of those library DLL's which do the neede stuff towards the DB.

for creating those connections in the library DLL's we have the following options

*) use pooling=False: But performance could be problem, if repetitive access to same DLL is done in the application (open - close - open ...)

*) use pooling=True
--> I tried setting "ConnectionLifetime=10" (as property in OracleConnectionStringBuilder, and toString() for getting full ConnectionString)
--> But when diagnosing via DbMonitor, I see that when connection.close(), it is returned to the pool, but the real "Disconnect" in DBMonitor happens only after around 5 MINUTES (also session on DB side is only removed after those minutes)
--> I also tried the value 420 (7 minutes), but again after 5 minutes the connection was removed from the pool !

Could it be that the 'ConnectionLifetime' property is not taken into account, or do I need some more properties to solve my problem ? (all other of OracleConnectionStringBuilder are left default)

Does anyone have some ideas, and/or example where dotConnect pooling is used and a lifetime of 1 minute or so ?

Thx
Fred

PS: I also tried using the OCI-pooling parameters, but then diagnose is only possible on DB-side

PS2: I try to connect to Oracle 10.2.0.4, with Oracle10 client, and Dotconnect version 5.70.152.0

Edited: added "Dotconnect version 5.70.152.0"

goethals.f

StanislavK wrote:We've fixed the problem with the GetSysDate function. The fix is available in the latest 5.70.146 build of dotConnect for Oracle. The build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information on improvements and fixes available in the 5.70.146 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18424

Hello,

I have rechecked my test-project (which I have submitted to devart also)

The problem with the getsysDate is indeed solved, but I am still unable to use a timestamp-field in my custom oracle object/table in combination with the input-parameter....

I still receive the "OCI-invalid handle" error, exactly as in the first posts...

Is this problem really solved ?

PS: I have tested with the version 5.70.152. I did not tested it with the specific 5.70.146 !!!

PS2: concerning looping over an OracleTable object (which is output-parameter from a procedure) --> I still don't get it how we can easily cast the generic OracleObject to the wizard-generated specific-object...)

I thought we should be able to use the FromOracleObject(...) method, but the generic OracleObject from the table, can not be casted to the NativeOracleObject...

Code: Select all

 

            //Get manual output parameter (table)

            Devart.Data.Oracle.OracleTable myOracleTableOut = (Devart.Data.Oracle.OracleTable)tmpOracleParameter_OUT_p_Devart_Object_Table.Value;
                                   
            foreach (Devart.Data.Oracle.OracleObject myOracleObjectItem in myOracleTableOut)
            {
                myDEVART_OBJECT = new DEVART_OBJECT();

                //QUESTION::: FromOracleObject --> does not work: how can we easy convert each OracleObject to --> DEVART_OBJECT
                //myDEVART_OBJECT.FromOracleObject(myOracleObjectItem);
                
//Casting is not possible
//myDEVART_OBJECT.FromOracleObject((Devart.Data.Oracle.NativeOracleObject)myOracleObjectItem);
                               
                //--> SOLUTION: manual Reading each properties via myOracleObjectItem[...] as wizard generates in FromOracleObject
--> can not be the goal of wizard-code if we have to copy it manual ....

                ...

            }

goethals.f

Hello,

I have some problems (since the new Oradeveloper version v2.60.172) when I try to insert new row-data in VS.NET (both the 2008/2010 versions) after done the "retrieve data" command. I got the Ora-00933 (SQL command not properly ended)

Remark: the DB-table is created on a oracle 734 DB, and access with Oracle8 client. --> it throws errors
When I do the exact same on a oracle10g with oracle8 client (or with oracle10 client) --> It succeeded without errors.

So Is there something changed that tha oracle 734 DB is no longer fully supported.

Maybe some more Details

Table SQL:

CREATE TABLE TLG_SCHAARMESHOUDER (
SCHAARMESHOUDER_KEY VARCHAR2(20) NOT NULL,
DIAMETER NUMBER(38) DEFAULT 0 NOT NULL,
CILINDER_TONNAGE NUMBER(38) DEFAULT 0 NOT NULL,
VENTIEL_YN VARCHAR2(1) DEFAULT 'N' NOT NULL,
OPMERKINGEN VARCHAR2(240)
)

And when I try to insert a new (the first) row through oradeveloper in VS.NET with the following info

SCHAARMESHOUDER_KEY = 'S10'
DIAMETER = 350
CILINDER_TONNAGE = 50
VENTIEL_YN = 'Y'
OPMERKINGEN = 'TEST'

--> I get the Ora-00933 error. (Only when doing this on oracle 734)

Can you reproduce this behaviour ?

thx
Fred

PS: is there some way to view the exact INSERT-sql that is tried. I checked all possible logging-options in the oradeveloper-tab, but the "Devart-SQL Log output" does not show any INSERT-sql statements...

goethals.f

Shalex wrote:Sorry, but the "Unknown connection string parameter" fix was not included into the *.140 build. StanislavK meant another fix
with the membership provider (http://www.devart.com/forums/viewtopic.php?t=18028). Please use a workaround
with removing the corresponding entries from machine.config. Look forward to the next build of dotConnect for MySQL.

OK,

Thanks for the info. We will use the workaround for now

Remark: I assume you mean the next build of dotconnect for Oracle, I hope

goethals.f

Hello,

I just came accross the tool LinqPad (http://www.linqpad.net) and was wondering if this tool is compatible with Oracle and Dotconnect.

Because now I can only choose sql server...

Remark:
Or do I have to choose for the "Entity framework" option, and choose my assembly DLL-file + Provider type "Devart.data.oracle" ...

Is there somewher some more info concerning using dotconnect in LinqPad, or will I not be able to use the tool in combination with my oracle and dotconnect?

Thx
Fred

goethals.f

StanislavK wrote:We have released the new 5.70.140 build of dotConnect for Oracle. It can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains fix for the problem with membership provider. For more information on fixes and improvements available in version 5.70.140, please see
http://www.devart.com/forums/viewtopic.php?t=18028

Hello,

I have come accross the same problem, and I am already using the last dotconnect v5.70.140.0

I use the ASP.NET memberprovidership but connected to SQL (didn't know I could use oracle for that btw). So no oracle connection (not for roles, not for the data is used)

But in my machin.config I still have the following lines:

MACHINE.CONFIG code

Code: Select all

And when I acces the ASP.NET config site from within VS.NET 2008, and try to add new users, I got an error logged (see at the end of post) in the event log which points to an unknown connection parameter.

Remark: adding new roles through the admin-website works without errors !!!, it happens only when creating new users

Unknown connection string parameter . (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 163)

When I put the "AspNetOracleMembershipProvider" in comments in machine.config, everythings works fine again.

Is there something wrong in my machine.config, because I use version v5.70.140 already and there the problem should be solved ?

In my web.config I have the following info.
I have taken the code from an example, and It works. Maybe the problem is because I first "clear" the providers and then "add" a totally new one configured in web.config, and make no use of the machine.config settings...

do you have any tips ?

thx
Fred

WEB.CONFIG CODE

Code: Select all

EVENT LOG ERROR

Code: Select all

Type gebeurtenis:	Waarschuwing
Bron van gebeurtenis:	ASP.NET 2.0.50727.0
Categorie van gebeurtenis:	Webgebeurtenis 
Gebeurtenis-ID:	1310
Datum:		8/06/2010
Tijd:		8:39:03
Beschrijving:
Gebeurteniscode: 3008  Gebeurtenisbericht: Er is een configuratiefout opgetreden.  Gebeurtenistijd: 8/06/2010 8:39:03  Gebeurtenistijd (UTC): 8/06/2010 6:39:03  Gebeurtenis-id: 87d3c3b9fcbc40938f289e236e2157fb  Gebeurtenisreeks: 92  Gebeurtenisinstantie: 1  Gebeurtenisdetailcode: 0    Toepassingsinformatie:      Toepassingsdomein: c8e2c347-1-129204527198652015      Vertrouwensniveau: Full      Virtueel pad toepassing: /asp.netwebadminfiles      Toepassingspad: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\asp.netwebadminfiles\    Procesinformatie:      Proces-id: 6108      Procesnaam: WebDev.WebServer.EXE    Uitzonderingsinformatie:      Uitzonderingstype: ConfigurationErrorsException      Uitzonderingsbericht: Unknown connection string parameter . (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 163)    Aanvraaginformatie:      Aanvraag-URL: http://localhost:4779/asp.netwebadminfiles/security/users/addUser.aspx      Aanvraagpad: /asp.netwebadminfiles/security/users/addUser.aspx    Threadinformatie:      Thread-id: 8      Imiteert: True      Stacktracering:    bij System.Web.Configuration.ProvidersHelper.InstantiateProvider(ProviderSettings providerSettings, Type providerType)
   bij System.Web.Configuration.ProvidersHelper.InstantiateProviders(ProviderSettingsCollection configProviders, ProviderCollection providers, Type providerType)
   bij System.Web.Security.Membership.Initialize()
   bij System.Web.UI.WebControls.LoginUtil.GetProvider(String providerName)
   bij System.Web.UI.WebControls.CreateUserWizard.get_QuestionAndAnswerRequired()
   bij System.Web.UI.WebControls.CreateUserWizard.UpdateValidators()
   bij System.Web.UI.WebControls.CreateUserWizard.CreateChildControls()
   bij System.Web.UI.Control.EnsureChildControls()
   bij System.Web.UI.WebControls.Wizard.OnInit(EventArgs e)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Control.InitRecursive(Control namingContainer)
   bij System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   bij System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   bij System.Web.UI.Page.ProcessRequest()
   bij System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   bij System.Web.UI.Page.ProcessRequest(HttpContext context)
   bij ASP.security_users_adduser_aspx.ProcessRequest(HttpContext context) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\asp.netwebadminfiles\9d4caaa4\c2ae7372\App_Web_rzp4agwt.0.cs:regel 0
   bij System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   bij System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

goethals.f

StanislavK wrote:We've reproduced the problem with the GetSysDate function. We will inform you when it is fixed.

As for other problems, could you please send us a complete sample project with which they can be reproduced?

I have uploaded a test project.

I got the OCI-invalid error when trying to pass the Table to a procedure as input param.

If possible, please modify or give some hints on which is the best way to transfer custom table types of custom object types?.

And how the interaction works with the devart generated code-objects (maybe i'm doing it wrong)

goethals.f

Hello again,

I have tested again, and now explicitly used the devart Object-wizard and the package-wizard.

And when executing my insert procedure which takes a table as input parameter (table consist of objects which have a Timestamp member)

The "OCI invalid handle." occurs in the wizard created object on the bold line:

oracleObjectOci["TIMESTAMP_SYSTIMESTAMP"] = this.mTIMESTAMP_SYSTIMESTAMP;

Code: Select all

    public Devart.Data.Oracle.NativeOracleObject ToOracleObject(Devart.Data.Oracle.OracleConnection connection) {
      NativeOracleObject oracleObjectOci;
      oracleObjectOci = new NativeOracleObject("TESTING_ADMIN.WVLB_OBJECT", connection);
      if (this.isNull) {
        return oracleObjectOci;
      }
      oracleObjectOci["ID"] = this.mID;
      oracleObjectOci["VALUE1"] = this.mVALUE1;
      oracleObjectOci["VALUE2"] = this.mVALUE2;
      oracleObjectOci["NUMBER1"] = this.mNUMBER1;
      [b]oracleObjectOci["TIMESTAMP_SYSTIMESTAMP"] = this.mTIMESTAMP_SYSTIMESTAMP;[/b]
      oracleObjectOci["TIMESTAMP1"] = this.mTIMESTAMP1;
      return oracleObjectOci;
    }

The error/stack trace is this:

Code: Select all

   bij Devart.Data.Oracle.bp.b(Int32 A_0)
   bij Devart.Data.Oracle.NativeOracleObjectBase.a(Object A_0, OracleAttribute A_1, IntPtr A_2)
   bij Devart.Data.Oracle.NativeOracleObject.a(Object A_0, OracleAttribute A_1)
   bij Devart.Data.Oracle.NativeOracleObject.set_Item(OracleAttribute attribute, Object value)
   bij Devart.Data.Oracle.NativeOracleObject.SetItemValue(Object value, String name)
   bij Devart.Data.Oracle.NativeOracleObjectBase.set_Item(String Name, Object value)
   bij TestDotConnect_OracleTable_WVLB.WVLB_OBJECT.ToOracleObject(OracleConnection connection) in T:\goethals.f\TFS_Projects\SADEF_TEST\Main\Source\TestDotConnect_OracleTable_WVLB\TestDotConnect_OracleTable_WVLB\WVLB_OBJECT.cs:regel 155
   bij Devart.Data.Oracle.OracleArray.a(OracleConnection A_0, NativeOracleArray A_1)
   bij Devart.Data.Oracle.OracleArray.Devart.Data.Oracle.ICustomOracleArray.ToOracleArray(OracleConnection con)
   bij Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, String A_13, av A_14, Boolean& A_15)
   bij Devart.Data.Oracle.OracleParameter.a(aq& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, av A_5, Boolean& A_6)
   bij Devart.Data.Oracle.OracleCommand.a(y A_0, Int32 A_1, OracleParameterCollection A_2, av A_3, Boolean& A_4)
   bij Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   bij Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)

[/b]

So I really think there is somewhere something wrong with the TimeStamps (see the Timestamp.getsysdate which is also wrong...)

Do you have any thought on this ?

thx
Fred

goethals.f

I have been testing further, and have some additional remarks:

*) When I have the following code:

Code: Select all

Devart.Data.Oracle.OracleTimeStamp myOracleTimeStamp = Devart.Data.Oracle.OracleTimeStamp.GetSysDate();

--> there is no time-info in the TimeStamp, only the current Date. (Time is always 00:00:00)

*) I have tested calling a procedure with one input Timestamp parameter, and this procedure I can use in my code without oci-call error.

But the TimeStamp value still has no time-info. (even when I specify the correct Type for the parameter: OracleDbType.TimeStamp)

PS: when I execute the same insert procedure, but use the normal
System.DateTime.Now as value (instead of the devart-one) --> my timestamp value is correct in the DB.

So I think there is maybe a bug concerning the use of TimeStamps in Object and Tables....

goethals.f

Hello,

I think I have found a bug concerning the use of

*) custom Oracle Object Types which have a TimeStamp membertype
*) in combination with a custom Oracle Table Type of those types
*) used as an input parameter in a stored procedure.

I get the "OCI invalid handle" error when executing this procedure

I got the same error while executing the code generated by the Devart code generation tools for the object and the package.

When i use a object which have NO TimeStamp member --> everything runs smoothly (I have already several working examples, but all WITHOUT a Timestamp membertype).

I use Oracle 10.2.0.1 client (tested with oracle8 client but also error)
Server is Oracle 10.2.0.4
dotconnect 5.70.140.0

Can you reproduce this behaviour/BUG our point me otherwise to an example of this scenario.

Thx
Fred

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

TYPE TESTING_ADMIN.WVLB_OBJECT3 AS OBJECT (
ID NUMBER(38),
VALUE1 VARCHAR2(20),
VALUE2 VARCHAR2(20),
NUMBER1 NUMBER(38),
TIMESTAMP1 TIMESTAMP(6)
)

CREATE OR REPLACE TYPE TESTING_ADMIN.WVLB_OBJECT3_TABLE AS
TABLE OF WVLB_OBJECT3;

--> even this empty procedure gives error, but works when there is no timestamp involved !!!

PROCEDURE INSERT_DETAILS3 (
p_wvlb_Object3_Table IN TESTING_ADMIN.WVLB_OBJECT3_TABLE
)
AS

BEGIN
null;
END INSERT_DETAILS3;

goethals.f

StanislavK wrote:We have released the new 5.70.140 build of dotConnect for Oracle. It can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains fix for the problem with using Oracle Client 8. For more information on fixes and improvements available in version 5.70.140, please see
http://www.devart.com/forums/viewtopic.php?t=18028

Ok,

first tests with the newest version. And everything seems working again with the Oracle8 client.

Thx for fixing this issue !!!

goethals.f

As a extra remark:

To fully understand my app requirements:

The application itself should be able to :

acces data from Oracle10 (which now need oracle10 client + version 5.60.124)

+

acces still old data from oracle734 (which can not be accessed by oracle10 client, so we use oracle8 because this client is able to acces both DB servers). But version 5.60.124 is currently not compatible with oracle8 client.

so - in my opinion - the only way my app can work now and having 2 connections is:

*) using 5.60.124
*) Oracle10 client installed
*) Oracle8 client installed (is mandatory because of a bunch of other legacy tools)
*) connecting to oracle734 via Direct-mode using 5.60.124 --> which means we have to consider the direct-mode limitations...

Fred

goethals.f

StanislavK wrote:We've reproduced the problem with the 5.35.79 version of dotConnect for Oracle, but couldn't with the latest 5.60.124 version.

Please try using dotConnect for Oracle 5.60.124 and tell us if this helps.

Hi,

I have retested my test-app with the new 5.60.124 release.

On my current PC I have two oracle clients (10.2 and 8.1 for acces to oracle 7.3.4 with OraHome81 as default home)

*) Testing the app in direct-mode --> still works (worked before with previous version)

*) Testing the app with oracle10 client --> I did not have the error (tried with a 100-for-loop). In older version after 5 times already crash. So it seems that version 5.60.124 works

*) Testing the app with Oracle 8 client (accessing the same oracle 10g server) --> I still receive the error in my other post (see: http://www.devart.com/forums/viewtopic. ... highlight= )

So my problem is indeed partially solved.

I can use my application now with the new 5.60.124 version --> but this means I have to install + configure on all our PC's the extra oracle 10 client (they only have oracle81 now)

It would be perfect, if the error in the post (http://www.devart.com/forums/viewtopic. ... highlight=) could be solved nearly in a new 5.60... build. So it still keeps working with the older oracle81 client.

Do you have already tackled the problem in the other post (because there would be a fix in several days you posted on the 5th of may?)

But at least, thx for pointing me out to the new version and to the info you gave about multiple homes in the other threads...

Fred

goethals.f

Hello hello,

I feel like I have to deal with some very strange behaviour in my app using dotconnect en Oracle client (10.2)

I have a simple stored prodecure with only one out parameter as type sys_refcursor (in the procedure, only a simple SQL statement is code there.

PROCEDURE SELECT_LIBRARY_GEOMETRY (
p_ResultSet OUT sys_refcursor
)
OPEN p_ResultSet FOR
SELECT ...

END SELECT_LIBRARY_GEOMETRY;

When I now execute this storedprocedure in my application using dotconnect en oracle client mode (direct == false)

I simply execute a stored-procedure with no parameters filled in (and parametercheck == true) I do not add the output-parameter myself, because the support-guys told me the output params are populated automatic.

But now, After some iterations of
oracleCommand.ExecuteNonQuery()

--> the parameter-value of the output-parameter is suddenly "null" instead of a real "Devart.data.oracle.oraclecursor".

Remarks: I only create the oracleCommand instance once, and tested it with a one-time prepare() call or with everytime a prepare() call before ExecuteNonQuery --> but problem stays the same.

And the really strange part is, if I change over to direct mode in connectionstring (and don't change any code) --> I never have the above problem ???

So, is there some strange behaviour possible or restrictions applied when using stored-procedures with cursor-output parameters and the oracle client itself.

A possible solution could be to switch to direct-mode (but then I have to deal with the direct-mode limitations) and I there must be a correct way to execute the same procedure several times (with the least performance hit of creating objects, parameters checks, hard/soft parsings, caching of statements, ...)

Does anyone have some clever tips or hints ?

thx
Fred

[/b]

Devart Forums

Search found 60 matches

BUG: ConnectionLifetime property ? (Does not work ?....)

BUG: Ora-00933: when inserting new row-data in VS.NET 2010

LinqPad compatibility ? Is it supported + How ?

BUG: OCI invalid handle: OracleObject with TIMESTAMP Type

Procedure with OUT-param as sys_refcursor fails after x time