Ask Your Question
1

The form to add a record works correctly 6.2 LibreOffice Base

asked 2019-03-09 13:49:51 +0200

Kadet gravatar image

updated 2019-03-10 06:47:44 +0200

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.

edit retag flag offensive close merge delete

Comments

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?

Ratslinger gravatar imageRatslinger ( 2019-03-09 19:46:40 +0200 )edit

@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

Ratslinger gravatar imageRatslinger ( 2019-03-10 16:11:40 +0200 )edit
1

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

Kadet gravatar imageKadet ( 2019-03-10 17:22:26 +0200 )edit

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

Kadet gravatar imageKadet ( 2019-03-10 17:24:27 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-03-10 18:59:04 +0200 )edit
1

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 gravatar imageKadet ( 2019-03-11 18:34:03 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-03-11 18:55:25 +0200 )edit

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 gravatar imageKadet ( 2019-03-11 21:48:21 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-03-12 22:29:10 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-13 17:39:50 +0200

Ratslinger gravatar image

@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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-09 13:49:51 +0200

Seen: 94 times

Last updated: Mar 13