Issue 30988

Summary: Unable to use primary keys generated by database triggers
Product: Base Reporter: jlueters <jlueters>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: trivial    
Priority: P3 CC: christoph.lukasiak, haxwell, issues
Version: OOo 1.1.2Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation on: ---
Developer Difficulty: ---

Description jlueters 2004-07-01 15:31:05 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.
Comment 1 jlueters 2004-07-01 15:34:01 UTC
I have forgotten the environment:

OS Debian Gnu/Linux sarge
Oracle jdbc
Eayssoft ODBC for Unix
Oracle 9.2.0 on Linux
Comment 2 christoph.lukasiak 2004-09-14 10:41:58 UTC
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.
Comment 3 thackert 2004-11-01 14:10:10 UTC
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 ... ;)
Comment 4 jlueters 2004-12-07 14:23:49 UTC
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
Comment 5 jlueters 2004-12-07 14:32:49 UTC
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").  
Comment 6 haxwell 2005-02-09 16:01:53 UTC
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.
Comment 7 marc.neumann 2005-04-18 14:04:43 UTC
reassign to the right developer

msc -> oj: 
May be we can provide a solution when we add trigger support to the database.
Comment 8 jlueters 2005-06-09 17:00:55 UTC
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. 
Comment 9 Frank Schönheit 2005-06-28 16:34:26 UTC
http://codesnippets.services.openoffice.org/ might be interested in the code snippet