Using SQL cursor and loop in LO base macro

[LO_user1]

Hello All. I need some guidance from developers. Here is a sql statement using cursor with loop which has to be used to fetch students into a subform. If I ‘CALL’ it in Mysql it will ask for two input values, grab records from ‘Student_Classes’ and create some new records in ‘Exam_Marks’ using this data.

CREATE PROCEDURE `fetch_students`(IN `cl_id` INT, IN `ex_id` INT)
BEGIN
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE st_id INT;
   DECLARE C1 CURSOR FOR SELECT Student_ID FROM Student_Classes WHERE     
   Class_ID = cl_id;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   OPEN C1;
   get_loop: LOOP
   FETCH C1 INTO st_id;
      IF done = 1 THEN 
       LEAVE get_loop;
      END IF;
      INSERT INTO Exam_Marks(ST_IDfk,Exam_IDfk)
      VALUES(st_id,ex_id);
   END LOOP get_loop;
   CLOSE C1;
END

I want to use it on LO Base form with a button on main form ‘Exams’. I know I can not use it as CALL Procedure. Instead I have to write a fresh code in Basic script for a Macro. Can you tell me any link where this type code (cursor with loop) can be copy or if you translate this one for me. Assume that Main form have two required fields ‘Class_ID’ and ‘Exam_ID’. This code will read these values from main form instead of getting from user as above sql statement does.

[Ratslinger]

Hello,

Just a note, the procedure has at least one error in it - st is not declared.

It appears you simply want to specify a Class_ID and an Exam_ID. Then retrieve all table records in Student_Classes with the same Class_ID as specified create new records in Exam_Marks table using the retrieved Student_ID and the specified Exam_ID. Is this correct? If so, then what? Maybe display the new records in a table control?

[Ratslinger]

Also just noticed you have deleted the question and answer given to you on 2019-04-07 regarding LO base sql code for subform creation.

Was there a reason to do this? This may have been of help to others. It is a waste of time and resources otherwise. You never even responded to the post.

[LO_user1]

Hello Ratslinger! Your are right. This is an error due to typing mistake. st should be st_id which is declared. And you explained about what I want to do is correct. This can achieve by using Tools>SQL in two steps. First using Insert Into Select From and Update the foreign key field. Or using pypmyadmin supplying two values. But the problem is that We have to compile 140 Exams scores after every quarter. On each click about 25 students will be fetched. So about 3500 records to be created. This is not possible by an ordinary user of database. So need a button press to run a macro to complete the task.

[LO_user1]

Regarding you second comment, actually I tried to correct my answer but I could not decide what to write? I later discovered that the problem was not a Firebird database or Libreoffice 6.2.2.2. It was due to incomplete installation. When you install this new version it some time installed complete but most the time it misses some files. I tried by package install and through apt install. Only one time I could get complete install after many tries.

[Ratslinger]

Unfortunately you have not actually answered my questions. Most of what you posted just skirts around the issues. Also as stated there was AT LEAST 1 error in the procedure and that is all you looked for. There are more! That is for you to find. Don’t know how you could possibly be using this procedure.

Will answer question without further information.

[jimk]

This question is marked as closed but no answer is marked as correct. Also please do not add “[SOLVED]” to the title in this forum. See guidelines for asking.

[Ratslinger]

Hello,

You can Call a procedure from a macro. Here is a link to a working call within Calc → Calc: call a stored procedure.

The adaptation of this to your Base file should be minimal. There are many posts here on the ASK site by me which can give you information on getting data from controls on a form or other necessary code. Specific code needs to be done by you since you have all the information. Internal form names, control names, table & field names are all part of creating this working procedure.

Have given the direction asked for.

Edit:

Have taken some time, created mock tables and entered the corrected Stored Procedure (posted one still not correct even though two changes made already) and used the following Macro called from a push button:

Sub RunStoredProc
  Rem Used vs Registered connection since this is a Base file
    oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
  Rem Prepare statement for Call
    oStmt = oConnection.prepareCall("CALL `fetch_students`(?,?)")
  Rem set the two variables - These values should come from controls on form
  Rem did not create since can be List box, numeric control etc. so just hard coded
    oStmt.setInt(1, 1)          'Parameter for Class ID'
    oStmt.setInt(2, 16)        'Param for Exam ID'
    oStmt.execute()
    MsgBox "Done!"
    oStmt.close()
End Sub

Other items need to be done. As mentioned the input controls. Also verification this is not a duplicate - verify records don’t already exist. Display of results? Probably other possible concerns such as what if wrong Class or Exam # entered?

Edit 2019-04-29:

MySQL database ------ Test01.odt

After downloading this file, change the extension from .odt to .sql. This site does not allow .sql extensions. Use this file to create the MySQL database. This is most easily done through MySQL Workbench. From menu choose Server->Data Import. Then Import from Self-Contained File. Name the new schema MyTest. Tables and modified procedure (added Class_ID) are included within.

Make sure you have MySQL JDBC connection. Connectors can be found here → Connector/J. The .jar file needs to be added to class path. For this see LO documentation → LibreOffice Base Handbook. Some info in Chapter 2 Creating a Database.

Here is the needed Base file ------ MySQLjdbcTestProcedure.odb – added comments to code

Provided the database and connector are installed this should work as is. One form:

Have included some of the error checking and functionality mentioned earlier. The Class ID control is a list box (only unique and valid ID’s). The Exam ID control is a numeric field.

Although much more than asked for and more than should have been asked for, there is still more to be done in my estimation.

Edit #2 for 2019-04-29:

Have decided to add a second Base sample. This uses an HSQLDB embedded database so is all enclosed. However, because of that, functionality is lost. It is being provided simply because, based upon comments, the previous procedure, although fully tested, may prove to be too difficult.

The screen can be examined, the macros are available and the MySQL procedure is saved as a query which, although cannot be executed from there, can still be viewed and copied.

Sample #2 ---- MockHSQLDBProcedure.odb – added comments to code

Edit #3 for 2019-04-29:

Sample #3 ---- HSQLDBnoProcedure.odb — completely operational with HSQLDB and does NOT use Procedure. Code (SQL portion) is not for MySQL.

[LO_user1]

Thank you Ratslinger for taking time to solve this problem. This may useful to many who are working on oracle forms and reports. We are already using this procedure there but want to migrate to mysql and LO Base as of free software. Please explain oStmt.setInt(1, 1) ‘Parameter for Class ID’ and oStmt.setInt(2, 16) ‘Param for Exam ID’. What is the meaning of (1,1) and (2,16) and how to enter Class_ID and Exam_ID. Secondly in form you shown here have Exam_Marks subform but not shown the main form which is Exams. This main main form have two necessary fields Exam_ID which is Primary key field and Class_ID field. In subform Exam_IDfk should be same as Exam_ID on main form(being a child) and should be one value for all fields. Please take some more time and edit your answer as I suggested. Thank you for your kindness. If you allow me I can edit my answer and put form image as I want it to be.

[LO_user1]

In oracle Sql Editor it shown as
DECLARE ex_id NUMBER := Exams.Exam_ID and
cl_id NUMBER := Exams.Class_ID (instead of parameters of the procedure.)

[Ratslinger]

What makes you think this has anything to do with Oracle? Your question deals with MySQL, you state that you call it in MySQL, my screen shot has in the File Name MySQL, so where is Oracle. All this was done in MySQL with a corrected Procedure - yours still has an error in it.

Don’t understand why you are bringing up Oracle SQL Editor either.

oStmt.setInt is to set the parameters of fetch_students (those two ? in the CALL statement). The first number is the parameter position and the second number is the mentioned hard coded value. Thus (1,1) is the first parameter set to 1 and (2,16) is the second parameter set to 16.

My answer stated it did not include controls because you may use different controls for this input. It could be list boxes or text boxes or others. Besides, my posting a form image with this does you no good. My posting a sample form also does no good. You need to use your naming within the MySQL Base file you have established.

Using Mr. is just guessing.

[LO_user1]

Hi! Actually you can not compare Oracle Forms with Mysql. Mysql just a database not a front end. We use LO Base as front end. So compare Oracle Forms with Base. Why I mentioned here is to just show you that we mention Table_Name dot Control_Name to tell the macro from where the value to be taken. Like you mention (1,1) and (2,16) mean we are supplying the values in Macro not mentioning the table name and control name. I am not bound on using a procedure of Mysql in the Macro. Need to achieve the goal of getting data. Do not concern on how to get it with which method adapt.
The example you referred has sFormat = “minor” and then oStmt.setString(1, sFormat). I need something like this sRecord=Control_Name.Table_Name and then oStmt.setString(1, sRecord). (Please mention the correct the syntax). I hope you understand my requirement now. I just want to run a script by using macro which do not ask any input instead take it from main form current/active record.

[LO_user1]

And please do not consider me a professional developer but just a novice especially for Base and Marcos. Yes I have some no how of Sql and sql-plus. I have worked before in MS Access, Filemaker Pro and Oracle.
In Access although some Basic langue is used but that is quite easy than LO.

[Ratslinger]

@LO_user1

You really are not understanding what I have given you already. It appears you can only deal with an actual working application. I have always understood your requirement. You have not understood my answer. The only item missing from my answer is getting the data from some control on a form to the macro parameters. As stated, did not provide (but explained how to get it) because each control can be different and it is not known what controls you will use nor the naming used withing the entire process. Cannot provide definitive code without specific information. Unfortunately you apparently did nothing in this regard.

Will modify my answer to include the necessary pieces to put together the working model I have using Base and MySQL.

[LO_user1]

Thank you very Ratslinger! You have done my job. I shall use this Macro code in actual database “School Classes & Results”. Now you have provided the complete working macro code in the provided database file.
You said “You really are not understanding what I have given you already.” Yes it is true. I have not ability to complete your provided code according my situation. Because I do not understand and have not learnt the correct code of LO macros. Anyway thanks again. I have tested it and it is working fine. There need only another table name “Exams” and that should be the main table. User will just go to the next record, click the button and fetch the data in subform.

[Ratslinger]

Note: This can also be done with Firebird server or embedded. However the Procedure is created differently:

CREATE PROCEDURE fetch_students(CL_ID INTEGER, EX_ID INTEGER)
AS
   DECLARE st_id INT;
BEGIN
   FOR SELECT "Student_ID" FROM "Student_Classes"
     WHERE "Class_ID" = :cl_id
     INTO :st_id
   DO
      INSERT INTO "Exam_Marks"("CL_IDfk", "ST_IDfk", "Exam_IDfk") VALUES(:cl_id,:st_id,:ex_id);
END

and is executed as regular SQL (based on macro presented in answer):

sSQL = "execute procedure fetch_students(" & iClassID & "," & iExamNumber & ")"
oStmt.executeUpdate(sSQL)

[LO_user1]

Hi again, I used your provided odb with odt and it was working fine. But as I mention earlier my requirement is to have a master form with subform and macro should take data from active record of master or parent form. In your odb file it is opposite. So I tried to create a form with new table Exams which have some records. But the data was not loading in subform. So I created new db and tried the macro code you provided. It stuck no following error message. “BASIC runtime error. Property or method not found: Value.” It shows the line “iExamNumber = oField.Value” with blue high lighted. This line is the same as your code. I just changed the form name and control name in code. I think it need to insert some lines in the code.

[LO_user1]

I changed the code taking hint from an other sample db file. Instead of “iExamNumber = oField.Value” I put “iExamNumber = oForm.Columns.getByName(“Exam_IDpk”).Value” and the next field also. It is working as required now. Thank for all your help and guidance. I really appreciate as you deal with the novice members like me.

[Ratslinger]

@LO_user1

You need to read the LO documentation. Your comments are even incorrect. You state ‘In your odb file it is opposite.’ but that is not correct as there is no sub form in my files. Have stated this in earlier comment as there is no need for a sub form but you do not seem to care for information provided.

This question has been more than answered from what was originally asked. You should accept the answer & close the question. If you have further questions, please ask as specific new question.