[Solved] [Base] Macro error writing a table

Creating a macro - Writing a Script - Using the API

[Solved] [Base] Macro error writing a table

Postby charlie.it » Sat Jun 20, 2020 4:21 pm

Hi everyone,
I'm working on a database that reads the record number of a table and writes information about the next field.
For a reason I don't understand, the macro gives me the error:
Violation of unique constraint SYS_PK_47: duplicate value (s) for column (s) "ID"
always at record no. 42.
In the simplified example I am attaching, the problem always occurs at record no. 54.
How can I solve it? Thanks.

Screenshot.png
Attachments
Test.odb
(13.31 KiB) Downloaded 15 times
Last edited by charlie.it on Mon Jun 22, 2020 9:09 am, edited 1 time in total.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby Villeroy » Sat Jun 20, 2020 4:32 pm

The string evaluates to something like this:
Code: Select all   Expand viewCollapse view
INSERT INTO "Table1" ("ID", "Q") VALUES( '13', '99')

Avoid numeric strings unless you are actually working with numeric strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base] Macro error writing a table

Postby charlie.it » Sat Jun 20, 2020 4:41 pm

Tank you,
In the original file I use
Code: Select all   Expand viewCollapse view
stField2 = oForm.getString (3)

to read the fields and to copy them to the table.
Where i need a numeric field, I use
Code: Select all   Expand viewCollapse view
VAL(stField2)

to write it in the table.
But the issue is in "ID" field.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby JeJe » Sat Jun 20, 2020 4:44 pm

oForm.RowCount appears to give a rowcount less than the actual depending which row is selected

Code: Select all   Expand viewCollapse view
on error resume next


in the code appears to work
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby charlie.it » Sat Jun 20, 2020 5:07 pm

Thanks, but I don't select any lines.
In the new example file I only have the ID field and the problem is still there.
With
Code: Select all   Expand viewCollapse view
on error resume next

makes the error message disappear but the table is no longer increased by pressing the button.
Attachments
Test_2.odb
(13.49 KiB) Downloaded 13 times
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby JeJe » Sat Jun 20, 2020 5:15 pm

Works for me - I get a row added, that's in OO.
Put msgbox oForm.RowCount in there and play with the selection:

If you select a row before this unexplained magic number then the rowcount equals that number. If on or after it then the rowcount is correct.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby JeJe » Sat Jun 20, 2020 5:22 pm

don't just put the on error resume next after you've got the error and run it again - if that's all you're doing.

Add it and run it from fresh... without it I get an error after a small number of presses... with it everything was fine with a lot of presses.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby charlie.it » Sat Jun 20, 2020 5:25 pm

Please, can you try now with a new Table1, without records?
You can see that all works till record no. 56, then in doesn't work.
Thank you.
Attachments
Test_3.odb
(13.54 KiB) Downloaded 19 times
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby JeJe » Sat Jun 20, 2020 5:41 pm

It worked till 49 on mine.

With on error resume next it works if the button is pressed once at a time. But if repeatedly pressed quickly then there's only one addition, no error though.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby RoryOF » Sat Jun 20, 2020 5:44 pm

JeJe wrote:It worked till 49 on mine.

With on error resume next it works if the button is pressed once at a time. But if repeatedly pressed quickly then theirs only one addition, no error though.


If button pressed repeatedly, might you (JeJe) be a seeing a timing problem, where the database housekeeping was ignoring the next button press because the database was still thinking?
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Base] Macro error writing a table

Postby charlie.it » Sat Jun 20, 2020 6:13 pm

There is not a timing problem.
The insertion of new records works perfectly up to a certain number, exactly the same that appears together with the asterisk in navigation bar + 1. Then it starts not working.

Schermata 2020-06-20 alle 17.47.35.png

In both LO and OO:
Why?
Edit: Sometimes it starts working again if you scroll the table to the last record and highlight it.
But then it doesn't work again.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby JeJe » Sat Jun 20, 2020 6:24 pm

Correction to the above. What happens (with the on error resume next) is:

I press the button, scroll down and the row is added, press the button, scroll down and the row is added etc

Keep pressing the button without scrolling - and only one row is added however many times I press.

if you put msgbox oForm.RowCount at the end, it reverts to the magic number after the form is reloaded.

I don't know databases... but there are some clues...
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby RoryOF » Sat Jun 20, 2020 6:31 pm

I'm like JeJe - I don't use databases (since dBaseII); but programming experience suggests that when an operation stops consistently after a number of repeats, there is possibly a buffer that has filled and needs to be reset, or flushed to disk.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Base] Macro error writing a table

Postby Villeroy » Sat Jun 20, 2020 7:14 pm

Why do you get the string of a numeric field? The string is useless. Use any of the other methods, for instance getLong(n) and don't put these numbers in quotes when composing SQL because SQL does not accept any strings in numeric fields.

P.S: and if the "usual ID field" is a usual auto-incrementing ID field, you write NULL if anything at all. No, there is no guarantee that x+1 is an unused number in that field.

Even better: STAY AWAY FROM ALL THAT MACRO BULLSHIT UNLESS YOU ARE VERY, VERY SURE ABOUT WHAT YOU'RE DOING.
you did not even try to execute some SQL commands manually.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base] Macro error writing a table

Postby Villeroy » Sat Jun 20, 2020 7:32 pm

This is how an entry form for a new quantity value may look like
Attachments
NewQuantity.odb
(22.62 KiB) Downloaded 17 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base] Macro error writing a table

Postby Villeroy » Sat Jun 20, 2020 8:02 pm

The availlable options are very limited. You have a two column table. One of the columns is automatically maintaining itself. The other one is a simple integer.

ON DATABASE LEVEL
The range of an integer column is −2.147.483.648 to 2.147.483.647. You may want to use a smaller type for efficiency reasons.
--------------------
An integer field can be NULL or NOT NULL. Yours is nullable.
As a rule of thumb, ALL columns should be NOT NULLable (except some less important annotations may be null)
--------------------
An integer field can have a default value. Yours does not have any default value.
Possibly you want to set the default value of a "quantity" field =1.
--------------------
An integer field can be restricted by means of a CHECK constraint.

(in database context "field" is synonym with "column")
menu:Tools>SQL....
Code: Select all   Expand viewCollapse view
ALTER TABLE "Table1" ALTER COLUMN "Q" INTEGER DEFAULT 1 NOT NULL

Choose SMALLINTEGER for values up to 32.167 or TINYINT for values up to 127.
[Execute]
Code: Select all   Expand viewCollapse view
ALTER TABLE "Table1" ADD CONSTRAINT "Q_gt_0" CHECK ("Q">1)

[Execute]
menu:View>Refresh Table
Now you have an integer column where a new or a modified record will not be stored unless the value is greater than 0.
Any missing value will be replaced with default value 1, no matter if you store the record from a grid view, SQL statement, input form or silly macros.
The first command fails if there are blank Q values already. This command is availlable in the Base table designer.
The second command fails if there are values Q>0 already. This command is not availlable in the Base GUI.

ON INPUT FORM LEVEL
If you want to enter new records quickly, you can set the form's "new records only" property or simply use a new record button in order to navigate to the new record behind the last record.
You should not even display the row ID to the user. If you decide to display the row ID, it should be shown in a read-only form control.
An integer value representing some kind of quantity deserves a specialized form control, either a formatted field or a numeric field (in this context "field" is synonym with "control"). I used a numeric control with minimum value 1, and with increment/decrement buttons. Up/Down arrows work as well.
I added 2 buttons, one cancel button which cancels the creation of a new record and an OK button which jumps to the next new record and which is set as the form's default button so it reacts on the Enter key. I forgot to unset property "Take focus". If you unset "Take focus", the focus stays in the Q field after Enter key triggered the OK button. This way you just enter digits and hit Enter. Every Enter submits a new quantity together with a new row ID even if you don't see any row ID.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base] Macro error writing a table

Postby JeJe » Sun Jun 21, 2020 12:11 am

A fix is to put oForm.absolute(N +1) at the end and on error resume next.

Code: Select all   Expand viewCollapse view

Sub MacroTest
 
   Dim oForm As Object
   Dim oForm1 As Object
 
 
   Dim stField2 As String
   
   on error resume next
 
   oForm=thisComponent.drawpage.forms.getByName("MainForm")
     
   N = oForm.RowCount

   oStatement = oForm.ActiveConnection.CreateStatement()
   
   sSQL = "INSERT INTO ""Table1"" (""ID"") VALUES ('" & N + 1 &"')"
   
   oStatement.executeUpdate(sSQL)
   
   oForm.Reload
   
   oForm.absolute(N +1)
End Sub

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby JeJe » Sun Jun 21, 2020 1:27 am

This is better:

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****


Sub MacroTest
 
   Dim oForm As Object
   Dim oForm1 As Object
 
 
   Dim stField2 As String

   oForm=thisComponent.drawpage.forms.getByName("MainForm")
   
    oform.last
   N = oForm.RowCount

 

   oStatement = oForm.ActiveConnection.CreateStatement()
   
   sSQL = "INSERT INTO ""Table1"" (""ID"") VALUES ('" & N + 1 &"')"
   
 
   
   oStatement.executeUpdate(sSQL)

   oForm.Reload
  oform.last

End Sub

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby charlie.it » Sun Jun 21, 2020 9:40 am

Villeroy wrote: ...

Hi, Villeroy. I have followed with interest what you wrote and I find it very interesting like all the things you write in the forum.
I used getString (n) not knowing getLong (n), now onwards I will use that for numeric fields.
However the problem was not that, but in blocking the macro to a certain number of records. If you try it also happens in the simplified example Test_3.odb where there is only the ID field.
Why do I use a macro? Because in the real database I have to take values from one table and write a new record in another table.
The macro is probably defective and the latest changes suggested by @JeJe seem to be working.
Thanks anyway.
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby charlie.it » Sun Jun 21, 2020 9:45 am

JeJe wrote:This is better:

It works both very well, thanks.
JeJe wrote:I don't know databases... but there are some clues...

You do very well, I think! :D :super:
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby Villeroy » Sun Jun 21, 2020 6:58 pm

I used getString (n) not knowing getLong (n), now onwards I will use that for numeric fields.

How is it possible that you don't know getLong? Are you trying to use the most complex parts of this API without the help of an object inspector?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base] Macro error writing a table

Postby charlie.it » Sun Jun 21, 2020 8:38 pm

I'm learning a little bit at a time, also on this forum :D .
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Base] Macro error writing a table

Postby JeJe » Sun Jun 21, 2020 9:00 pm

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1088
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Base] Macro error writing a table

Postby charlie.it » Sun Jun 21, 2020 9:03 pm

I have it, but I don't use it :roll: .
Attachments
Schermata 2020-06-21 alle 21.01.59.png
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Solved][Base] Macro error writing a table

Postby Villeroy » Mon Jun 22, 2020 10:41 am

Now assign a form control event to macro MyMacros>MRILib>Mri for testing, say "Received Focus" and load the form.
When you trigger that event, a window pops up showing the properties of the passed over event struct. In most cases "Source" is the only property. Double-click on "Source". Now you see all properties and methods of the calling object.
Double--click property "Model" or method "getModel" and then property "Parent" or method "getParent". Now you see all the properties of the input form.
Hit Ctrl+H in order to unhide the code window where you get a readily written code snippet.
If you are done with that form control, remove the event pointing to the MRI macro.

From within your own StarBasic code, there are 2 equivalent methods to inspect some UNO object:
Code: Select all   Expand viewCollapse view
GlobalScope.BasicLibraries.loadLibrary("MRILib")
Mri myObject

What the Basic routine Mri actually does is nothing more than this:
Code: Select all   Expand viewCollapse view
oMRI = CreateUnoService( "mytools.Mri" )
oMRI.inspect( myObject )

The MRI extension provides a service "mytools.Mri" with method inspect(obj).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved][Base] Macro error writing a table

Postby robleyd » Mon Jun 22, 2020 11:30 am

You might also find [Tutorial] Introduction into object inspection with MRI a useful reference.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] [Base] Macro error writing a table

Postby charlie.it » Tue Jun 23, 2020 8:35 am

Thanks everyone for your help :super: .
charlie
macOS 10.12 Sierra: Open Office 4.1.7 - LibreOffice 6.2.8

http://www.charlieopenoffice.altervista.org
User avatar
charlie.it
Volunteer
 
Posts: 366
Joined: Wed Aug 21, 2013 2:12 pm
Location: Italy

Re: [Solved] [Base] Macro error writing a table

Postby Villeroy » Tue Jun 23, 2020 11:00 am

Most of my database forms used by me and my co-workers do not rely on a single line of macro code.
I have some rather advanced input forms that rely on very few lines of Basic or Python code. The choice of programming langague is arbitrary because the macros are really simple. They only save one or two annoying extra clicks.
The macro path of tears and sorrow starts when you try to solve things with macro code just because you don't know how forms actually are intended to work. Open/LibreOffice form design is a development tool. Just like macro development, form design is not meant to be done by a form user. Compared to the tool sets provided by MS Access, this tool set is very limited but also very well thought. It is very well thought because there is at least one specialized form control for most column types that may occur in a database. A hierarchy of subforms and list boxes allow you to resemble all possible kinds of database relations: one-to-one, one-to-many and many-to-many. [Example] Relations reflected by list boxes in forms

At the same time, it is very limited because the toolset does not more than the absolute necessary. However, it is possible to know and understand each and every aspect of this limited but powerful toolset and how it is related to the underlying database. The foundation is always the database with its tables, strictly typed columns and relations. On top of a sloppy database design you won't get usable forms. With MS Access (a massive application as big as our whole office suite) you can build fairly operational input forms even on top of badly designed databases.

A rather advanced database example which is operational without a single line of macro code: viewtopic.php?f=100&t=70259

A slighly advanced real-word example where 90% of the functionality is covered by SQL and form design, 10% is the macro code: viewtopic.php?f=100&t=100489

The macro code conditionally performs the following 4 steps to a form object (if conditions stripped):
Code: Select all   Expand viewCollapse view
  bm = frm.getBookmark()
  frm.isModified = True
  frm.insertRow()
  frm.reload()
  frm.moveToBookmark(bm)

The data to be inserted are determined by the user selection in the parent form. This automatically inserts the right data into the subform when the macro is triggered. After row insertion the row cursor goes back to the previous record position.

------
P.S. back on original topic: Macro code does not need to concatenate and execute SQL in order to insert/update/delete.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests