Hello,
My L.O subform can not be used with a server which use trigger and sequence. Both main and sub forms can not be used together. The sequence number is the key to join the main table and the sub table. The main tables must be filled at first to generate the primary key sequence.
Is there a possibility to obtain the next sequence number from the server after inserting a line ?
Thank you
Hello,
Please provide OS, specific LO version, database used (guessing PostgreSQL version ? based on another question) and connection type. This can all be key and should always be in the question.
Using PostgreSQL and a native connector (also tried JDBC 42-2-2) don’t see any problem with using sequence. Can easily do form/subform together and on new records using sequence. Not certain what in the trigger may cause you a problem. Please explain.
This is the case of two tables in a school library. The first table record information about a student. A primary key is a number id for each student. The second table record books movment. Each time a book is given to a student, one line is inserted in this second table. The join and relationship between both tables are the number id of the student. This id is a automatic sequence from remote server.
Filling just the student table with a single form works well: the trigger on the server side catch data BEFORE insertion then fill the id field thanks to sequence. I do not fill the id field in the form even if it is required, it is left blank.
The problem appears with subform. Data in the main form are written to first table without problem. But for the second form (the subform, the 2nd table) , I can not write any thing in the sub form because all fields are locked !! When I finished to fill the main form, a TAB or ENTER keystroke display a new blank form, L.O is ready for a new on
(…the next) L.O ignore the subform. Sometimes an error message came for the server for data mismatch and show error code 1 with a data casting syntax. This is interpreted by L.O that something has not updated. I do not know what are not updated because the data from main form is recorded and subform is ignored. I do not know what kind of process L.O did for the subform on the server, I think it is not an INSERT command because a second trigger for that is attached to second table and it is not runned.
After this incident, when I navigate forward and backawrd through the form, all subform became grey and locked and all previous subdata are not displayed. Only data of main form are displayed. To display ancient data of subform/2nd table , I have to close the form and reopen it.
First you provided none of the information asked for in my above comment - please provide.
You comments seem to be a problem different that the original question. Cannot come to a conclusion with that you have provided. In addition to the information asked for, please post a sample of your .odb file. Yes I understand it has no data. Will deal with that. Edit your question and insert there.
Software used: slackware 14.2, postgresql 9.5.1, L.O 6.2.7. All are 64bits.
Connection is PostgreSQL through localhost.
Hello,
It appears you have other problems than the original question.
First the original question.
Is there a possibility to obtain the next sequence number from the server after inserting a line ?
This SQL:
SELECT nextval('YOUR_SEQUENCE');
but that may cause problems as it will increment the sequence number.
SELECT currval('YOUR_SEQUENCE');
will retrieve the current value.
Now as for the problems stated in the comments, there is not enough information to help there. As earlier stated, don’t know what the trigger you have is doing. You typically do not need a trigger just to use a sequence.
Do not know what connection type is being used (PostgreSQL/localhost is not a type) - JDBC, ODBC, Native? Going to guess Native since comment mentions "PostgreSQL’.
Don’t know how the forms are set up. Asked for a sample there.
Stated earlier, tested using sequence with form & subform with both Native and JDBC connections and had none of the problems stated. There must be more undisclosed information than what has already been presented.
Edit:
Carried one step further and added a trigger to update the ID vs just a sequence. This did create a problem but nothing of which you have stated. Rather, in the sub form, the linked ‘id’ was always 0 until the master was refreshed and went back to the newly entered record. Then all was OK.
Could overcome this without code. Used two push buttons: one to refresh & one to go to last record.
Again not the problems you have described and didn’t need this with just a serial sequence. No need for trigger in this case.
The subject was posted before I analyze the real problem. I thought it was a sequence issues. In fact it may be a subform. I will post a new one more precise.
connection type= PostgreSQL. This is enlisted and proposed by L.O when setting connection. It is amongst JDBC, Mysql, Firebird, …
I said ‘localhost’ because there is no network infrastructure yet.