The form to add a record works correctly 6.2 LibreOffice Base

After switching from the built-in database to the external form, adding new records began to work incorrectly. After filling in the form and the “save record” action, the record is saved in the table, but “0” appears in the associated form element “ID”, instead of the current ID appearing with the internal database.
In further work, because of this, there are errors in accessing the record.
LibreOffice Base 6.2, the external database is Firebird, connected through the driver Firebird_ODBC.

I use Windows 7 x86 and Windows 10x64. The result is the same.
Connect via the ODBC external driver Firebird_ODBC_2_0_5.
Libreoffice is configured as an ODBC database connection.

Hello,

Are you connecting to file using ODBC (rather than built in SDBC)? Have seen Firebird ODBC problems in recent versions. Also what OS are you using?

@Kadet Sorry, failed to realize that was your post on the other forum. Have deleted my answer as it is only a duplication of your experience posted here

Yeah, that’s my research. However, it is nice that my work was useful. Thank you!

However, no one responded except you. Probably nobody knows. I’ll have to find a solution to my problem again.

@Kadet Your research was very helpful to me. Using Linux and not previously finding a working method for ODBC with Firebird, this renewed my investigation. Be aware I have now, to some extent, gotten a connection apparently working (need a bit more testing). Will also continue to investigate your problem and hope one of us finds the answer.

The problem is not solved. Had to circumvent. Not the best solution, but it works.
Attached to the “save record” button.

   Sub ZAKnew_SAVE(oEvent)
    	Dim db As Object
    	Dim oSql As String
    	Dim oRowSet As Object
    	Dim oForm As Object
    	
    	db = connect_to_database ()  
    	oForm = oEvent.Source.getModel().getParent()
    	Wait 500
    	oSql = "SELECT ""ID"" FROM ""ZAKAZY"""
    	oRowSet = createUnoService("com.sun.star.sdb.RowSet")  
    	oRowSet.activeConnection = db  
    	oRowSet.Command = oSql  
    	oRowSet.execute
    	oRowSet.last
    	If oRowSet.RowCount<>0 Then 
    		oForm.getByName("fmtID").BoundField.Value = oRowSet.getInt(1)
    	End If
    End Sub

@Kadet Appreciate the info. Still having other problems in Linux ODBC. If I ever get any kind of solution, will certainly post here.

I do not know where in Linux but in Windows I can give a few recommendations I found.
In the ODBC driver settings (“data sources”) in the" role “must be set to"RDB$ADMIN”.
In the “advanced settings” Libreoffice I enable a check mark on “Ignore driver privileges”.
When creating users in the Firebird database itself (Firebird ISQL Tool), you need to give users not only the “ADMIN” role, but also the "RDB$ADMIN"role. And also create MAPPING for these roles.

CREATE USER user_name PASSWORD '12345' GRANT ADMIN ROLE;
GRANT RDB$ADMIN TO user_name;
CREATE GLOBAL MAPPING TRUSTED_AUTH USING PLUGINS srp FROM ANY USER TO USER;

I have no obvious problems with access and work with Firebird in Windows yet, except for the above, which I refer to as bag Libreoffice.
Maybe this will help you?

@Kadet Appreciate all the info. Although what you have passed on is not the problem I have, I can connect using ODBC in LO v6.2.x with some success. Do have some unusual problems only with certain DB’s and tables. As you have experienced, tracing down will take time but now have something to work with.

As to your original question, no specific answer yet but it is strange have recently answered nearly similar situation for PostgreSQL DB → Base PostgreSQL Autogenerate PKEY Problems. Not to say this is the same problem, just strangely similar.

@Kadet,

At this point it should be obvious that this is a bug and should be reported as such. The erroneous auto increment value returned does occur using either ODBC or JDBC with Firebird server. This returned value will cause even more problems if there is a subform involved. What you may not have yet encountered is that this not only affects the generated number but also other fields which may default to a value. My testing used a field defaulting to current_timestamp and that value was mangled.

In my testing, this macro attached to the form After record action event worked as a fix for now:

Sub NewRefresh
  Dim oForm As Object
  oForm = ThisComponent.getDrawPage().getForms().getByName("MainForm")
  If oForm.IsNew Then
    oForm.reload()
    oForm.moveToInsertRow()
  End if
end sub

This only reloads the form (and provides the correct data) and positions on a new record if the record entered is a new record. No further action by the macro if just editing an existing record.

Turns out that my initial problem using ODBC was simply the Advanced setting. It seemed to not work because it turns out, at least in Mint 18.3, that the are a number of other problems.

Here is one in particular you may want to be aware of. This may only be a Linux connector problem.

Mixed tables in a database. This is some with Primary key auto increment and some without. Note table name spelling here.

First situation:

TABLEA  -  WITH     no problem
TABLEB  -  WITH     no problem

Second situation:

TABLEA  -  WITH     no problem
TABLEB  -  WITHOUT     problem - states data is NULL even though entered

Third situation:

BTABLE  -  WITHOUT     no problem
TABLEA  -  WITH     no problem

Only difference between second & third situation is the table name which is how it is sequenced in the DB!

There are certainly many serious problems with ODBC and Firebird server with Linux including crashes if you try to create or edit a table. Many are not present using JDBC.