Apache OpenOffice (AOO) Bugzilla – Issue 30988
Unable to use primary keys generated by database triggers
Last modified: 2013-08-07 15:45:41 UTC
This report is closely connected to bug 3872 (could not reopen it). The problem is biting me using OO as frontend and oracle as database. I have set up a master detail relation with a customer and a customerdetails table. The primary key for customerdetails is generated using a sequence. In order to make sure that all applications using this table (OO and ms access ) the primary key is inserted via a before-insert-trigger. In this situation it is ok to insert a row with a undefined primary key. Using both odbc and jdbc drivers there a OO complains about cursor consistencies. Moving to the next master record and returning to the previous record reinitialises the details view and shows that the insert has been done successfully. This leads to the conclusion that the cursor data are beeing cached by OO and the (cached) cursor is running in a inconsistent state (primary key = NULL ?). I would suggest that the cursor is beeing reread/reinitialised after performing an insert. This has several benefits. It goes well with database triggers. Too aggressive caching is harmful anyway in relational database and mostly hihgly concurrent enviromentes where tens, hundreds or thousands of users operating against a common database/tables. To counterbalance the growing workload it might be reasonable to update less often, e.g. only update/insert when leaving a row or on user request.
I have forgotten the environment: OS Debian Gnu/Linux sarge Oracle jdbc Eayssoft ODBC for Unix Oracle 9.2.0 on Linux
clu->jlueters: i am not shure, if i understood you right, but have you tried to switch of the connection pooling (tools/options/data sources)? after reconnecting you have a 'real' reconnect.
Is this issue solved with a newer version of OOo (1.1.3 or 1.9.5x)? If so: could the OP close this issue, please? You could reopen it everytime the problem appears again ... ;)
The problem does still exist. I have disabled connection pooling, no changing. Again i believe the problem is more architectural. The main problem, from my point of view, is that OO does not permit to insert a record without a primary key, even if it is perfecly legal ( in terms of database operation) and does make sense. It does make sense to insert a record without a primary key because databases are updated from many different sources, e.g. dialog applications (like OO or MS access) or batch jobs, concurrently. In order to assure consistent primary keys for a given table, database triggers can be used to generate primary keys across different applications, and frontends. Usually database triggers uses seqences to get the next key. If no primary key has been generated (because there is no database trigger) you can return the database error code which explains what was wrong. This is the same problem as has been discussed in bug 3872. The best solution would be to drop the restriction the the primary key has to be provided while inserting the record. This restriction causes problems only and does no good (as has been outlined by various folks discussing bug 3872. Juergen
Please consider a solution like the one wihr has been provided for the user rights problem. In that case the DataSource has been provide with a property which instructs OO to ignore the user rights returned by the database driver. Maybe it is possible to provide a datasource attribute switch ("do not enforce primary key != null").
I'm confirming this issue because it is very similar to issue 3872. 3872 is currently marked closed fixed, and applies to Postres DB rather than Oracle. The underlying issues of autoincrement columns not being handled correctly is the same, however. I will leave the question of whether this is actually a duplicate of issue 3872 and that it should be marked that way to another more knowledgable QA person.
reassign to the right developer msc -> oj: May be we can provide a solution when we add trigger support to the database.
I have developed this workaround : Function generateId ( DatenquellenName as String) as Long ' Variable declaration Dim DatenbankKontext as Object Dim Datenquelle as Object Dim Verbindung as Object Dim Statement as Object Dim ResultSet as Object Dim newpk as Long ' Datenbank-Kontext holen DatenbankKontext = createUnoService _ ( "com.sun.star.sdb.DatabaseContext" ) ' Datenquelle holen Datenquelle = DatenbankKontext.getByName( DatenquellenName ) ' Verbindung erstellen Verbindung = Datenquelle.getConnection( "", "" ) ' Statement erzeugen Statement = Verbindung.createStatement() ' Abfrage absetzen 'oracle way of retrieving a primary key ' postgresql works smilar ResultSet = Statement.executeQuery( "select probe_id_seq.nextval from dual") ResultSet.next() newpk = ResultSet.getInt( 1 ) ' ResultSet, Verbindung und Statement brav schliessen ResultSet.close() Statement.close() Verbindung.close() generateId = newpk End Function Sub approveRowChange(Event as Object ) Dim Form as Object Dim SubForm as Object Dim PosId as Integer Dim NewId as Long Dim CurId as Long DIM Doc as Object Dim ret as boolean Dim StatusColId as Integer Dim tmpStatus as Integer Dim ElongLegal as boolean Dim GageLegal as boolean Dim CrossLegal as boolean dim FormDbId as integer dim ElongationDbId as integer Dim ElongColId as Integer Dim CrossSectionColId as Integer Dim GageLengthColId as Integer ' Keine Fehler tmpStatus = 0 'MsgBox Event.DBG_Properties 'MsgBox Event.Action if ( not Event.Action = com.sun.star.sdb.RowChangeAction.INSERT ) then Exit Sub end if Doc = ThisComponent Form = Doc.DrawPage.Forms.GetByName("Standard") StatusColId = Form.findColumn("STATUS") if ( Not Form.supportsService( "com.sun.star.form.component.DataForm" ) ) Then msgBox "No DataForm fired" Exit Sub End If ' Wert der Listbox Form ermitteln FormDbId=getListboxDatabaseId(Form,"ListBoxForm") ' Wert der Listbox Elongation ElongationDbId=getListboxDatabaseId(Form,"ListBoxElongation") ' prufen ob elongation listbox auf richtigem wert checkElong tmpStatus=getCrossSection tmpStatus=getGageLength PosId = Form.findColumn("PROBE_ID") CurId = Form.getInt(PosId) Form.updateInt(StatusColId,tmpStatus) if tmpStatus < 0 Then setListboxBgColor(Doc,Form,"STATUS",COLOR_RED) End If if ( CurId = PK_DEFAULT or CurId = 0 ) Then 'generate a new id NewId = generateID(Form.DataSourceName) Form.updateInt(PosId,NewId) End If End Sub If you connect the function approveRowChange to the form event "before record change" you can retrieve the primary key from the database and update the primary key field. I think we shall close this issue and produce a HowTo ("how do i get database primary key into a form") instead. So we have a workaround and a way to use OO with RDBMS like Oralce and Postgres.
http://codesnippets.services.openoffice.org/ might be interested in the code snippet