Plugin Fails to rebuild queryobject

[CarolusMagnuss]

Hi there,

I encounter a bug, where the AddInn Fails to rebuild a query from the repository. The LogFile gives me:

When i check the error log i finde the following error:

Beim Importieren des Objekts 'einschreibung_studenten_liste_verteilung' wurde von Microsoft Access ein Fehler festgestellt.

In Zeile 10 wurde ein Fehler entdeckt.
Erwartet: 'Name'. Gefunden: dbBoolean.

However then i look into the sourcefile i get this:

Operation =1
Option =0
Where ="(((einschreibung.matnr) Not In (Select matnr From haertefall_accepted Where best"
    "aetigt = 1) And (einschreibung.matnr) Not In (Select matnr From praxiseinteilung"
    ")) AND ((einschreibung.vlfreie_zeit)=0) AND ((einschreibung.Seminarjahr)=Year(Da"
    "te())))"
Begin InputTables
    Name ="einschreibung"
    Name ="einschreibung_studenten_wunsch"
    dbBoolean "ReturnsRecords" ="-1"                         'This is Line 10
    dbInteger "ODBCTimeout" ="60"
    dbByte "RecordsetType" ="0"
    dbBoolean "OrderByOn" ="0"
    dbByte "Orientation" ="0"
    dbByte "DefaultView" ="2"
    dbBoolean "FilterOnLoad" ="0"
    dbBoolean "OrderByOnLoad" ="-1"
    dbBoolean "TotalsRow" ="0"
    dbBoolean "UseTransaction" ="-1"

Now i have no Idea whats going wrong, this looks fine to me, but clearly there is an issue i am not seeing, could you help me find out?

[hecon5]

Line 10, in this case is actually line 10 from the begin statement, which is a dbBoolean (dbBoolean "OrderByOnLoad" ="-1"). It looks like there's some additional information missing from the source, namely the input fields, and the rest of the relational info. Does this query perhaps reference another query in your database?

If you also exported the SQL, can you post that, too?

[CarolusMagnuss]

Oh thank you for your time, I didn't want to post the entire sourcefile to keep the code small but if its needed i will attach it at the bottom.

The SQLstatement is as follows:

SELECT praxisverfuegbarkeit.id_praxis, praxisverfuegbarkeit.id_zeitraum, praxisverfuegbarkeit.besonderheiten, 0 AS belegt INTO praxen_tmp
FROM praxisverfuegbarkeit
WHERE (((praxisverfuegbarkeit.id_zeitraum)>=14 And (praxisverfuegbarkeit.id_zeitraum)<=28) AND ((praxisverfuegbarkeit.verfuegbar)=True) AND (([praxisverfuegbarkeit].[id_praxis] & "," & [praxisverfuegbarkeit].[id_zeitraum]) Not In (SELECT (praxiseinteilung.id_praxis & "," & praxiseinteilung.id_zeitraum) as id FROM praxiseinteilung Where matnr is not null)))
ORDER BY praxisverfuegbarkeit.id_zeitraum, Rnd(praxisverfuegbarkeit.id_praxis);

I don't have this issue with the other Database i am managing with this plugin, so It works fine with every other query.

Sourcecode:

Operation =1
Option =0
Where ="(((einschreibung.matnr) Not In (Select matnr From haertefall_accepted Where best"
    "aetigt = 1) And (einschreibung.matnr) Not In (Select matnr From praxiseinteilung"
    ")) AND ((einschreibung.vlfreie_zeit)=0) AND ((einschreibung.Seminarjahr)=Year(Da"
    "te())))"
Begin InputTables
    Name ="einschreibung"
    Name ="einschreibung_studenten_wunsch"
    dbBoolean "ReturnsRecords" ="-1"
    dbInteger "ODBCTimeout" ="60"
    dbByte "RecordsetType" ="0"
    dbBoolean "OrderByOn" ="0"
    dbByte "Orientation" ="0"
    dbByte "DefaultView" ="2"
    dbBoolean "FilterOnLoad" ="0"
    dbBoolean "OrderByOnLoad" ="-1"
    dbBoolean "TotalsRow" ="0"
    dbBoolean "UseTransaction" ="-1"
    Begin
        Begin
            dbText "Name" ="einschreibung.pfingstwoche"
            dbLong "AggregateType" ="-1"
            dbInteger "ColumnWidth" ="2175"
            dbBoolean "ColumnHidden" ="0"
        End
        Begin
            dbText "Name" ="matnr"
            dbLong "AggregateType" ="-1"
        End
        Begin
            dbText "Name" ="praxis1"
            dbLong "AggregateType" ="-1"
        End
        Begin
            dbText "Name" ="praxis2"
            dbLong "AggregateType" ="-1"
        End
        Begin
            dbText "Name" ="praxis3"
            dbLong "AggregateType" ="-1"
        End
        Begin
            dbText "Name" ="zeit.id"
            dbLong "AggregateType" ="-1"
        End
    End
    Begin
        State =0
        Left =0
        Top =0
        Right =1540
        Bottom =842
        Left =-1
        Top =-1
        Right =1524
        Bottom =262
        Left =0
        Top =0
        ColumnsShown =539
        Begin
            Left =48
            Top =12
            Right =192
            Bottom =156
            Top =0
            Name ="einschreibung"
            Name =""
        End
        Begin
            Left =327
            Top =97
            Right =471
            Bottom =241
            Top =0
            Name ="einschreibung_studenten_wunsch"
            Name =""
        End
        Begin
            Left =624
            Top =4
            Right =768
            Bottom =148
            Top =0
            Name ="zeit"
            Name =""
        End
    End

[joyfullservice]

@CarolusMagnuss - I know we have seen problems sometimes with how subqueries are exported. You can see these issues for more details on that.

I was trying to compare the SQL you posted with the exported query source, and they look very different to me. Would you be able to run a fresh export of that query from a working database, and post that source along with the SQL source that you get when you click the SQL button in the query designer? That might help us to identify the discrepancy.

Also, as discussed more in #76, you might try making a small change to the query in the SQL view, and save and close the query. (Without opening it in the visual designer window.) When you run an export after that, it will generate a different type of export file. It is possible that this different format of export file may import just fine.

Another option to consider is the checkbox to force the import of the original query SQL during build. This is not the preferred option because it resets the layout in the designer window, but it can solve some problems with importing certain types of queries.

Sometimes you can design the query in a little different way, and it will work just fine.

I hope that helps!

[CarolusMagnuss]

@joyfullservice
Thanks alot for you time and your Work.

This is a 10 year old Database i started to maintain last year after it had been abandoned for several years, so i dont really know the ins and outs of everything in there yet. That being said you are quite right, the source and the sql of this query dont really match at all. Checking the Query in the database shows that it is broken.

I can't really say when this happened, i might have to check the live version on monday as I dont have access to it right now, I will also check my sourcecode history to see if i can find a working version. After all isnt that what this is all for.

I will get back to you.

Regards

[CarolusMagnuss]

Hi there again,

after checking my source history and reading the linked issues i can confirm that the problem are subquerys within queries with the wrongful realignment of parenthesis and brackets during the export.
I now have to find the last working file version in my vcs and restore to this version, I also have to check my other queries if anything else broke during export. I will then try the suggested settings to force the import of the original sql and test again.

I didn't rebuild from source for a while now, thats why i didn't notice earlier. I also simply add files to a commit which are shown as changed, although i know i didn't alter them because its usually due to some sanitization procedures (Removing extra lines, removing and adding an identical line) Thats why i missed this.

[CarolusMagnuss]

I now managed to revert the broken queries to the last working version. There were 5 in total, which prompted an error on build.
I did export and rebuild now an it seems to work fine. I do hope that I didn't lose any recent changes during this process.

Not sure if related or not, but when the build of queries failed, quite often the AddIn failed to build all modules of the database. Even more strangely, simply by going into the options and checking and unchecking some of them seemed to reset that behaviour and the modules were built sucessfully on a subsequent build attempt.

I will have to keep a close eye on this behaviour and I apprecciate all the effort that went into this AddInn

Regards
CarolusMagnuss

[hecon5]

If you do experience a failed build, a "Full Export" of your Database will export all objects anew, and I wonder if that may have been part of the issue (VCS didn't detect changes, so didn't export, even though it was changed). Much of that detection tooling has been refactored in 4.x, so that's a bonus, but from what you're mentioning, it does seem suspicious.

Please update as you figure out more!

[CarolusMagnuss]

So i had to update another old query, where i simply added another column in the query designer. It now looks like this and uses a subquery

SELECT WeektoID([minID]) AS minW, WeektoID([maxID]) AS maxW, CInt(Replace([maxW],"KW",""))-CInt(Replace([minW],"KW","")) AS tte, studenten.matnr, studenten.gruppe, studenten.name, studenten.vorname, studenten.email, tt.minID, tt.maxID, Casus_info.Evaluation_added, studenten.Kommentar
FROM ((SELECT praxiseinteilung.matnr, Min([zeitraum.id]) AS minID, Max([zeitraum.id]) AS maxID FROM zeitraum LEFT JOIN praxiseinteilung ON zeitraum.id = praxiseinteilung.id_zeitraum WHERE (((praxiseinteilung.id_praxis)<>-1 AND ((zeitraum.Id)>=(Select First(Beginn) From Einstellungen) And (zeitraum.Id)<=(Select First(Ende) From Einstellungen)))) GROUP BY (praxiseinteilung.matnr) ORDER BY Min([vlwoche]))  AS tt INNER JOIN studenten ON tt.matnr = studenten.matnr) LEFT JOIN Casus_info ON tt.matnr = Casus_info.matnr
ORDER BY studenten.name;

And sure enough the build fails again, although i do force the import of the original sql query.
I did go into the sql builder and simply deleted the ending semikolon, saved and exported so that the query gets saved in the dbMemo format. This works and lets the Database rebuild.

The failing modules do seem to be related as the only fail to be built if I have a failed query build, but i don't get an error file for the modules, so i can't really tell where exactly the AddInn trips.

Regards

[hecon5]

@joyfullservice: from the sound of it...it may be the logic to check if a query has been altered inside/outside of the export is not properly detecting changes.

@CarolusMagnuss: next time you get a failed build on a sub-query, before editing the subquery in the window like you did, do a FullExport. This will force a complete export of all code, regardless of whether the addin thinks it's been changed.

If the build works afterwards, then it sounds like the logic is borked. The new indexing functions are being rolled into V4.x, so this is less likely to occur (if it is indeed the export detection logic). If not, then we'll check other things. But from what you've mentioned, I suspect the export logic may be caught in an edge case.