Question Authors' Forum

Creating MySQL qustion with new table

Creating MySQL qustion with new table

by Uthpalani Kavisekara -
Number of replies: 9

I was able to create a quiz with CodeRunner using the sample "customers" table given. Now I need to create different questions using different tables.

So as explained in this thread, I created a separate sqlite database file and did the required changes in the sample xml file (please check the attached). But the following error showed up (validate on save) when I try to save the xml file after uploading it to moodle.  

***Run error***
Error: near line 5: no such table: tbl_book

Any help is much appreciated.
Thanks.

In reply to Uthpalani Kavisekara

Re: Creating MySQL qustion with new table

by Richard Lobb -
I'm not sure what you mean by "I .. did the required changes in the sample xml file". You shouldn't ever need to edit xml files - they're simply an import/export exchange format.

When I import your xml file, it see it does contain a support file tbl_book.db but then I download that and open it with sqlite3 I get told it's not a database.

The file tbl_book.db that you attached to the above posting is a database but it contains just a table demo that contains a lot of sql queries. It's certainly not a book database.

If you have a working sqlite3 database, you should simply drop it into the support files in the authors form, deleting any database file that's already there. It should automatically be selected as the database for the question. 

If you're unsure, just post back, attaching the tbl_book.db (a workable sqlite3 db, containing a table tbl_book) and I'll add it the question you and export it back to you.

Richard

In reply to Richard Lobb

Re: Creating MySQL qustion with new table

by Uthpalani Kavisekara -

Hi Richard, I think the problem is with my database file as you have mentioned.

The original database is a MySQL database and I export it as a .sql file (please find the attached). From that created this SQLite database file using an online SQLite editor (https://sqliteonline.com/).

Kindly let me know whether there is anything wrong with what I have done and if it's possible please export the .sql file that I have attached here to the correct format.

Many thanks!

In reply to Uthpalani Kavisekara

Re: Creating MySQL qustion with new table

by Richard Lobb -

Sorry but that still isn't a useful database file. See the screenshot below, which is what I get when I try to inspect it in sqlite3.

Someone else once sent me a file containing the same sort of rubbish and he too had got it by applying some database translator - probably the one you're using - to a file from the web. It seems this isn't a very good approach.

I think for starters you need to install sqlite3 on your own machine so you can work with your database files. And it looks like you need to find another way to convert SQL files from the web into sqlite3.

Richard


In reply to Richard Lobb

Re: Creating MySQL qustion with new table

by Uthpalani Kavisekara -
Thank you Richard, I will first try to install sqlite3 on my machine and create the sqlite database file with that. I will check that and get back.
In reply to Richard Lobb

Re: Creating MySQL qustion with new table

by Uthpalani Kavisekara -

Hi Richard, I installed sqlite3 on my machine and somehow was able to create a sqlite3 database file with it. 

When I use that file as a support file and try to save it, a different error occurs. Seems some characters are missing. Can you identify the issue? I have attached both SQLite db file and the screenshot of the error I'm getting.


Attachment error-tbl-book.png
In reply to Uthpalani Kavisekara

Re: Creating MySQL qustion with new table

by Richard Lobb -
That's looking better - you now have a valid sqlite3 database file. Although I see that the book_name for b004 has got an extra \r\n on the end of the name. You need to clean that up with a command like

update tbl_book set book_name="Aspects of Sinhalese Culture" where book_id=b003;
The problem with your output is the column widths. If you edit the question and open the Question type details section you'll see some important info on how the question type works.

You seem to be displaying only two columns, whereas the original question was displaying three. With just two columns you probably want to set the template parameters field to something like

{"columnwidths": [30, 25]}
In reply to Richard Lobb

Re: Creating MySQL qustion with new table

by Mark Stern -
I do not think that the columnwidths parameter is the main issue. SQLITE has an output mode which determines what the output looks like. The default value for coderunner SQL questions is column, defined by a line in the template:

controls = [".mode column", ".headers on"]

However, column mode often causes truncation of output (see e.g. https://sqlite.org/forum/info/878fc45c77e8d5ce), and I think that is what has happened here. I suggest changing it in the template to something more sensible like list.
In reply to Mark Stern

Re: Creating MySQL qustion with new table

by Uthpalani Kavisekara -
Thanks Mark, I think I had the problem either with my sqlite db file or with the column width parameter. Changed both and now it's working.
In reply to Richard Lobb

Re: Creating MySQL qustion with new table

by Uthpalani Kavisekara -
hi Richard,
Like you said I removed that extra \r\n from the book name and generate a new sqlite database file. Also, set the template parameter field to two columns.
Now it's working! Thanks a lot!