[Solved] [Base] Macro error writing a table

[charlie.it]

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.

[Villeroy]

The string evaluates to something like this:

Code: Select all

INSERT INTO "Table1" ("ID", "Q") VALUES( '13', '99')

Avoid numeric strings unless you are actually working with numeric strings.

[charlie.it]

Tank you,
In the original file I use

Code: Select all

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

VAL(stField2)

to write it in the table.
But the issue is in "ID" field.

[JeJe]

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

Code: Select all

on error resume next

in the code appears to work

[charlie.it]

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

on error resume next

makes the error message disappear but the table is no longer increased by pressing the button.

[JeJe]

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.

[JeJe]

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.

[charlie.it]

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.

[JeJe]

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.

[RoryOF]

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?

[charlie.it]

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.

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.

[JeJe]

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

[RoryOF]

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.

[Villeroy]

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.

[Villeroy]

This is how an entry form for a new quantity value may look like

[Villeroy]

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

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

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.

[JeJe]

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

Code: Select all

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

[JeJe]

This is better:

Code: Select all

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

[charlie.it]

...

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.it]

This is better:

It works both very well, thanks.

I don't know databases... but there are some clues...

You do very well, I think!

[Villeroy]

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?

[charlie.it]

I'm learning a little bit at a time, also on this forum .

[JeJe]

MRI makes life a lot easier:

https://extensions.openoffice.org/en/project/MRI

[charlie.it]

I have it, but I don't use it .

[Villeroy]

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

GlobalScope.BasicLibraries.loadLibrary("MRILib")
Mri myObject

What the Basic routine Mri actually does is nothing more than this:

Code: Select all

oMRI = CreateUnoService( "mytools.Mri" )
oMRI.inspect( myObject )

The MRI extension provides a service "mytools.Mri" with method inspect(obj).

[robleyd]

You might also find [Tutorial] Introduction into object inspection with MRI a useful reference.

[charlie.it]

Thanks everyone for your help .

[Villeroy]

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

  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.