Contraint Issue

Contraint Issue

Postby benbahrenburg » Wed 25 Jun 2008 23:47

Hello,

I've got an issue when running a Select statement there the underlying table has primary key constraints.

For example if I have a primary key on Field1, Field2, and Field3 and I write a select statement that returns Field1 and Field2 it error with constrained to be unique. This due to the fact that Field1 and Field2 can have the same value when select if field3 is not included.

My issue is that I just want to select the data, it is ok for contraint not to apply. Is there anyway to turn it off?

The user can enter sql so I can not create a datatable and set all of the rows to unique = false

Is there anything I can do to turn off the constraints?

oCommand.CommandType = CommandType.Text
oCommand.CommandText = tSQL
oCommand.Connection = GProperties.DBSettings.Connection

_objDataTable = New OracleDataTable
With _objDataTable
.Connection = DBSettings.Connection
.ReturnProviderSpecificTypes = False
.SelectCommand = oCommand
.FetchAll = True
.ReadLobMode = ReadLobMode.DefferedCachedDirect

.CachedUpdates = True
.Active = False
.Fill()

End With
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby anton.connect » Thu 26 Jun 2008 14:37

We have examined your sample and tried to reproduce the problem.
It seems that the MySqlDataTable is filled several times.
Is it possible that this method is called more than one time (Fill() or Active = true are invoked twice)?
Last edited by anton.connect on Fri 27 Jun 2008 06:56, edited 1 time in total.
anton.connect
 
Posts: 43
Joined: Thu 19 Jun 2008 14:30

Postby benbahrenburg » Thu 26 Jun 2008 15:57

Hi,

Although processed on a different thread, this is only called once.
Even adding the following above the fill method doesn't help.

If Not (_objDataTable Is Nothing) Then
_objDataTable.Dispose()
_objDataTable.Clear()
End If

If I load an OracleDataset I don't seem to get this issue. Unfortunately I need the datatable object instead.
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby Alexey.mdr » Fri 27 Jun 2008 10:56

Hello,

Do you have this MySqlDataTable component on a Win Form?
If you do, could you please check that the Active check button in the component smart tag is not checked?
Please make a small test project to reproduce the problem and send it to mysqlnet*crlab*com.

Regards,
Alexey.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby benbahrenburg » Fri 04 Jul 2008 20:13

I just sent an email to you. Please note this is for the OraDirect provider.
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby benbahrenburg » Fri 04 Jul 2008 20:21

Using the basic below sample you can also re-create the issue.

Dim tConnectionString As String = "USER=SCOTT; PASSWORD=TIGER; SERVER=ORADB;"
Dim objConn As New CoreLab.Oracle.OracleConnection(tConnectionString)
Dim oCommand As New CoreLab.Oracle.OracleCommand
Dim objDataTable As New CoreLab.Oracle.OracleDataTable
Dim tSQL As String = "SELECT FIELD1, FIELD2 FROM FIELD_KEY_ISSUE"
objConn.Open()

oCommand.CommandType = CommandType.Text
oCommand.CommandText = tSQL
oCommand.Connection = objConn

objDataTable = New OracleDataTable

With objDataTable
.Connection = objConn
.ReturnProviderSpecificTypes = False
.SelectCommand = oCommand
.FetchAll = True
.ReadLobMode = ReadLobMode.DefferedCachedDirect

.CachedUpdates = True
.Active = False
.Fill()

End With


CREATE TABLE FIELD_KEY_ISSUE
(
FIELD1 INTEGER,
FIELD2 INTEGER,
FIELD3 INTEGER
)
/


CREATE UNIQUE INDEX FIELD_KEY_ISSUE_PK ON FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
/


ALTER TABLE FIELD_KEY_ISSUE ADD (
CONSTRAINT FIELD_KEY_ISSUE_PK
PRIMARY KEY
(FIELD1, FIELD2, FIELD3))
/
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby benbahrenburg » Fri 04 Jul 2008 20:22

Sorry forgot the sample data

SET DEFINE OFF;
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 3);
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 4);
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 5);
COMMIT;
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby benbahrenburg » Mon 07 Jul 2008 18:39

Just wondering if you had a chance to review the sample I sent.
benbahrenburg
 
Posts: 25
Joined: Mon 02 Oct 2006 19:15

Postby AndreyR » Tue 08 Jul 2008 08:44

Hello, Ben.

Thank you for the bug report. The issue is already fixed.
Look forward to the next build.

Reagrds, Andrey
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle