SQL-query for SQLite does run in DB Browser but not in CodeRunner?

SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Christian Kruggel -
Number of replies: 6
Hi all,

I do have an SQL-query for SQLite that does run in DB Browser but does not succeed within code runner.

SELECT LehrerInnen.Kuerzel AS `Kürzel`,
       unterrichtet.Stunden,
       Klassen.Bezeichnung AS `Klasse` FROM Klassen
    INNER JOIN unterrichtet ON Klassen_Bezeichnung = Klassen.Bezeichnung
    INNER JOIN LehrerInnen ON Kuerzel = unterrichtet.LehrerInnen_Kuerzel
WHERE LehrerInnen.Kuerzel = "KR" and Klassen.Einschulungsjahr = "20/21"
Is answered by code runner with

***Laufzeitfehler***
Error: near line 6: table unterrichtet has no column named Kuerzel
Traceback (most recent call last):
  File "__tester__.python3", line 42, in <module>
    output = subprocess.check_output(['sqlite3', db_working], input=text_input,
  File "/usr/lib/python3.8/subprocess.py", line 415, in check_output
    return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
  File "/usr/lib/python3.8/subprocess.py", line 516, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command '['sqlite3', 'Beispiel']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "__tester__.python3", line 46, in <module>
    raise Exception("sqlite3 error: " + str(e))
Exception: sqlite3 error: Command '['sqlite3', 'Beispiel']' returned non-zero exit status 1.
The database that does accept the above without complaints is attached.

Why does code runner crash? Can you spot that straight away?

Pls excuse the german.

Any hint is highly appreceated by

Chris
Tags:
In reply to Christian Kruggel

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Richard Lobb -

I'm not a database programmer, nor do I speak German. But when I ask sqlite3 for info about your database I see the following:

sqlite> .tables
Klassen        LehrerInnen    SchuelerInnen  unterrichtet 
sqlite> .schema unterrichtet
CREATE TABLE unterrichtet(
    LehrerInnen_Kuerzel VARCHAR(5) CONSTRAINT FK1 REFERENCES "LehrerInnen"(Kuerzel),
    -- Achtung, Abweichung von der Konvention, folgendes Attribut Klasse müsste eigentlich Bezeichnung heißen
    Klassen_Bezeichnung VARCHAR(10) CONSTRAINT FK2 REFERENCES Klassen(Bezeichnung),
    Stunden INT CONSTRAINT valid CHECK(Stunden > 0),
    CONSTRAINT PK PRIMARY KEY(LehrerInnen_Kuerzel, Klassen_Bezeichnung)
);

The warning there seems to suggest this database isn't following the expected/required conventions with regard to the foreign key declaration?

In reply to Richard Lobb

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Christian Kruggel -
Thank you for your reply!

The warning "-- Achtung ..." is some outdated note to myself, just stating that the name of attribute Klasse should be different. That should have no effect on SQlite and the query still works outside coderunner. Is there any sanitizer between code runner (python) and SQlite?

I'm adding the questions XML below. And big big thank that you consider this issue - I'm almost lost, for tried many combinations of the SELECT above which pass DB Browser but fail within moodle / code runner and I haven't any idea why!
In reply to Christian Kruggel

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Richard Lobb -
Again with the caveat that I'm no database programmer ... I see that in your second question you have the extra template data

INSERT INTO Klassen (Bezeichnung, Einschulungsjahr) VALUES ('FIAE20A', '20/21');
INSERT INTO unterrichtet (Kuerzel,Klasse,Stunden) VALUES ('KR','FIAE20A',1);

When I run those queries with the database copied out of the question using sqlite3 from the command line (i.e., not using CodeRunner), I get similar errors.
In reply to Richard Lobb

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Christian Kruggel -
Please excuse me providing over-boarding test-data. If the XML contains more than on single question ("Schüler Klasse Lehrer Join 4 (Kopie)") please ignore everything else. I'm working my way through these questions and update the database itself, changing foreign keys attributes' names according to table_pk-scheme.

So these INSERTs have to fail.

The database Beispiel.db behind the failing question is derived from an older version. I used SQlite's rename to do that and are going to rebuild Beispiel.db from scratch with CREATEs.

Am I right to assume that there is nothing else in between an SQL-Statement entered via Moodle / code runner and SQlite? Code runner takes some input, passes it via python to SQlite, python gets the reply and passes it back - nothing else, correct?
In reply to Richard Lobb

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Christian Kruggel -
Richard,

please excuse me wasting your time. I missed the most important point - the error-message was close to the suggested solution (teacher's reply) and so I thought the SELECT was wrong.

In deed the message (***Laufzeitfehler***) was triggered by the INSERT that you pointed out. It has to precede teacher's reply and causes the error.

I got stuck on the SELECT that did not cause the error.

After changing the INSERT the error is gone and it all works like cream.

At least a 1000 thanks for great software code runner and adorable patience with it's clumsy users.

Take care

Chris
In reply to Christian Kruggel

Re: SQL-query for SQLite does run in DB Browser but not in CodeRunner?

by Richard Lobb -

No problem. Good to know that things are working again for you