I have a form, which triggers a macro after opening the document and before a record is changed. In earlier versions (4.0) it works fine, but now I get empty values for the 'CurrentValue' attribute of ListBoxes, so that the SQL query fails, which contains these values.
SELECT "Table_2"."Nr." FROM "Table_2" WHERE "Table_2"."Begriff: Nr." = '' AND "Table_2"."Kontext: Nr." = ''
To illustrate this, I have inserted a MsgBox instruction into the macro to show the wrong SQL statement.
To reproduce this, you must enable the processing of macros for the attached document. Reopen the database and after this open the form 'Table 2'.
Created attachment 116256 [details]
... I have forgotten the example.
The macro fires, if I hover the next record button with my mouse without to press it? This is strange ...
Confirming on OSX 10.10.3
Build ID: 55431a84c264a8bcca593b9207aae0ad81d10f30
Locale : fr-FR (fr.UTF-8)
After the MsgBpx, the IDE opens and I get a Basic error :
Erreur d'exécution BASIC.
Une exception s'est produite :
Message: Wrong data type: java.lang.NumberFormatException: For input string: "".
from the follwowing line of code :
Result = Statement.ExecuteQuery(Sql)
I get the same error with
Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72
Same error in 220.127.116.11
This also fails for me on
Version 18.104.22.168 (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)
Also fails on
Version 22.214.171.124 (Build ID: 53fd80e80f44edd735c18dbc5b6cde811e0a15c)
TinderBox: MacOSX TDF Release, Branch:libreoffice-4-0, Time: 2013-01-31_11:01:36
Also fails on
Version 126.96.36.199 (Build ID: e183d5b)
@christian : I can not confirm that this is a regression, at the moment, it looks like this never worked, at least on OSX
Same error in
so this was a problem from the beginning of the LO project, and probably inherited from OOo.
@christian : in which earlier versions of LO did it work, and which OS ?
Since LO 4.1 CurrentValue of a listbox changed to the value, which should be saved in the database. Older versions will show the text, which could be seen on the screen.
if you are using macros and do not know which version of LO should be use, you have to use a function like this:
DIM aSettings, aConfigProvider
DIM aParams2(0) AS NEW com.sun.star.beans.PropertyValue
DIM sProvider$, sAccess$
sProvider = "com.sun.star.configuration.ConfigurationProvider"
sAccess = "com.sun.star.configuration.ConfigurationAccess"
aConfigProvider = createUnoService(sProvider)
aParams2(0).Name = "nodepath"
aParams2(0).Value = "/org.openoffice.Setup/Product"
aSettings = aConfigProvider.createInstanceWithArguments(sAccess, aParams2())
OfficeVersion() = array(aSettings.ooName,aSettings.ooSetupVersionAboutBox)
Created attachment 116290 [details]
macro for LO 4.0.6
Sorry - I have not reported:
before I changed to LO 4.3 and the to LO 4.4.3, I have used LO 188.8.131.52
(Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24).
For this version, I have used a macro like in the attached text snippet, and here I get no error, if I open the form, because here I get empty strings and not missing numbers for the CurrentValue attribute of ListBoxes, so that no error occurs (probably this is wrong).
But in LO 4.0.6, the behaviour of the trigger is the same, if I uncomment the MsgBox("Test") like in the attached example, it popped up, if I open the form or if I hover again over the next-record-button with my mouse after I have changed the record. This implies, that the macro fires in these cases.
But I think, this should not happen, because the macro should only fire, before the record is changed (or if the ListBoxes are touched, because I have triggers here) and not after the form is opened.
I have used Windows 8.1.
An event could be started by different implementations. There are two implementations which would happen when changing from one row to another:
The implementation, which fires while opening the form, is ...ODatabaseForm. Loading of the content is changing the content for this implementation.
You could start your macro like this:
Sub Check_rl_term_context(oEvent AS OBJECT)
IF oEvent.source.ImplementationName = "org.openoffice.comp.svx.FormController" THEN
So only one implementation starts - the implementation, which doesn't start while opening the form.
You are German. Have a look to the Base-Handbuch. It is described at page 412 (Handbuch 4.4). Also the different behavior of ListBoxes is described: page 388.
Not the triggers for the ListBoxes are the problem, but only the form's trigger which shall trigger the macro before the record will be changed. I mean two things:
I have seen, that the macro fires two times, one event source is 'com.sun.star.comp.forms.ODatabaseForm' and another is 'org.openoffice.comp.svx.FormController'. And it is described in the Base-Handbuch for LO 4.4 (p. 141 - 142), that already the opening of the form is such a changing-the-record-event (ok, I think, I must accept this). But it is not so easy to understand, why the macro should fire, if the mouse pointer hovers over the change button after the record was changed, because the semantics of this event mean 'before changing record'.
If I use the event 'while loading' to trigger the macro, then the ListBoxes are correctly initialized and have content (CurrentValues are not empty), so that the SQL query of the example does not fail. But if I use the event 'before changing record' as trigger, it seems, that the ListBoxes are not yet initialized and the CurrentValue attributes are still empty, if the macro fires at first time. In the earlier version of Base (LO 4.0) this causes not an error, because the CurrentValue attribute of the ListBox contains only an empty string. Now (LO 4.4) it should contain a number, but it is empty and contains nothing.
I have used the If-then-contruct to filter the right event source and it works, but in my opinion this is a workaround, because the semantics of this event trigger are not clear for me.
So, the question remains ?
Bug or design feature ?
Or RFE ?
This bug description is about three different behaviors in the form:
- changed current value of listboxes
- different implementations for an event
- occuring of an event while moving with the mousepointer over a field in the navigationbar
One bug - one buggy behavior.
Please open a new bug-description for the mousepointer over the fields and add an example without all other "buggy" behavior.
I have opened bug 91879.
Christian, I don't understand. You say that in LibreOffice 184.108.40.206, you got an empty string instead of a "Variant/Empty" in the variables IDString, TermID and CtxtID, and you say that then the "Statement.ExecuteQuery(Sql)" does not make an error.
However, if these variables contain an empty string or they are Empty, the resulting content of variable Sql is the same, so I don't see how it can make a difference for your macro.
Anyway, if you want a string, you can easily force that by adding:
Dim IDString As String
Dim TermID As String
Dim CtxtID As String
While I read your macro, I notice that in the SQL command (query), you try to compare an integer column to a string literal. This is not guaranteed to work on all database management systems. For example, it does not on PostgreSQL:
ERROR: operator does not exist: integer = character
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
It happens to work with HSQLDB (or MySQL/MariaDB) because those will do automatic type conversions.
I suggest that you use integers when comparing to integer. For example
SELECT "Table_2"."Nr." FROM "Table_2"
WHERE "Table_2"."Begriff: Nr." = 0 AND "Table_2"."Kontext: Nr." = 2
SELECT "Table_2"."Nr." FROM "Table_2"
WHERE "Table_2"."Begriff: Nr." = '0' AND "Table_2"."Kontext: Nr." = '2'
This will be more portable.
In order to avoid trying to use invalid values you could do something like:
1) Leave variables IDString, TermID and CtxtID as Variant.
if IsEmpty(IDString) OR IsEmpty(TermID) OR IsEmpty(CtxtID) Then
before computing the value of "sql".
And actually, the most robust way to do that kind of things is not to construct SQL commands on the fly, but to use parametrised queries:
Global statement as Object
statement = ThisComponent.DrawPage.Forms.GetByName("frm_tbl2").activeConnection.prepareStatement("SELECT ""Table_2"".""Nr."" FROM ""Table_2"" WHERE ""Table_2"".""Begriff: Nr."" = ? AND ""Table_2"".""Kontext: Nr."" = ?")
Dim MainForm As Object
Dim Result As Object
Dim Term As Object
Dim IDControl As Object
Dim Context As Object
Dim Found As Boolean
Dim IDValue As Variant
Dim TermID As Variant
Dim CtxtID As Variant
Found = False
MainForm = ThisComponent.DrawPage.Forms.GetByName("frm_tbl2")
IDControl = MainForm.GetByName("Nr-Feld")
Term = MainForm.GetByName("Begriff-Feld")
Context = MainForm.GetByName("Kontext-Feld")
TermID = Term.CurrentValue
CtxtID = Context.CurrentValue
if IsEmpty(TermID) OR IsEmpty(CtxtID) Then
Result = statement.ExecuteQuery()
While (Not Found AND Result.Next)
If (StrComp(Result.GetString(1), IDValue) <> 0) Then
MsgBox("Der Begriff wurde in diesen Kontext schon eingetragen.")
Found = True
Where "make_query" is bound to the form's "when loading" event.
What I mean is: either the elements of the form should be correctly initialized before the macro will be executed by the trigger for the event 'before changing record' (I have not yet noticed for LO 4.0, that the contents of the form's elements are still empty, if the macro will be executed at first time), or the macro should not be executed by this event trigger after opening the form, because for this case the event trigger 'while loading' already exists and such events are handled by it.
Created attachment 116451 [details]
order of events
(In reply to Lionel Elie Mamane from comment #18)
Something is wrong with the order of the occuring events. The event 'when loading' should not occur after the event 'before changing record'. The workaround in your code for this is
> if IsEmpty(TermID) OR IsEmpty(CtxtID) Then
> exit sub
> end if
I have attached an example to show this.
Hi to all.
Well,I am not a developer ,but...
The error you have I think is an error in Java,and Java is used only in HSQLDB,which one are written in above mentioned language.
Then ,the error is :NumberFormatException.
If you will take a look to official Java definitions,
you can find the definition of this error:" the application has attempted to convert a string to one of the numeric types, but that the string does not have the appropriate format".
What I am trying to say:this is the bug of enabled database,but not a LibreOffice,Libre just gives a SQL command to database engine and attend the anwer.It is possible to write to authors of mentioned database,but I am afraid that the answer will be:"It's time to change version"(because the LO version database is about 10 years old,there are more recent versions.)
Hope it helps.
Status should be NEW.
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
Version: 220.127.116.11 (x64)
CPU-Threads: 1; BS: Windows 6.1; UI-Render: Standard;
Gebietsschema: de-DE (de_DE); Calc: group threaded