Contraint Issue

benbahrenburg · Post by **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

anton.connect · Post by **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)?

benbahrenburg · Post by **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.

Alexey.mdr · Post by **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.

benbahrenburg · Post by **benbahrenburg** » Fri 04 Jul 2008 20:13

I just sent an email to you. Please note this is for the OraDirect provider.

benbahrenburg · Post by **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 · Post by **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 · Post by **benbahrenburg** » Mon 07 Jul 2008 18:39

Just wondering if you had a chance to review the sample I sent.

AndreyR · Post by **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