Base/Firebird Queries broken

users
#21

When editing the query, it works using RunQuery (F5)

After editing and it is working, I save the Query.

Then clicking on the saved Query from main Query window results in error again.

#22

Howdy Paul,

No you don't need to go back - the parameter substitution is for problems
with sub forms and now I think you are just dealing with SQL differences
between HSQL and Firebird with regards to how it implements joins in select
statements.

#23

When editing the query, it works using RunQuery (F5)

After editing and it is working, I save the Query.

Then clicking on the saved Query from main Query window results in error
again.

IDK on that, haven't seen that particular problem before.

Did you say that the SQL statement will run with Run SQL Direct turned on?

#24

Hi Paul,

Ok, things are narrowing down on this issue.

I have a Query Design view in which I have a field Series set to 134.

I remove the 134 and press enter.

Run Query (F5)

It works.

Save it at this point. Don't switch back. Does this work?

Then try
"Equipment"."Series" = '134'
instead of
"Equipment"."Series" = 134
before.

Regards

Robert

#25

Hi Drew,

Yes, I have a suggestion - there is a property in the Base file which is
incorrectly set during the Migration Assistant run and must be fixed. The
error message looks like this is what is biting you.

The fastest way to fix it is at follows:
Close the base file and exit LibreOffice.
Rename the base file from <filename>.odb to <filename>.zip
Now open the file with your favorite zip tool.
You fill find a file named content.xml in the root of the file.
Open that with a text editor.
Search for this string: db:parameter-name-substitution="false"
Edit that so that it reads db:parameter-name-substitution="true"

Couldn't be the parameter-name-substitution. This bug appears in forms
with subforms, not in a simple query without any parameter-input.

Regards

Robert

#26

Ok, this is what happened.

Opening the "2 Amps to Volts-High by Series"

By main "Queries", right click and choose "Edit"

In view, changed 134 to '134'

Message: Warning    The entered criterion cannot be compared with this field.

Remove 134 from Series and hit enter.

<Edit><Run Query> results in all records

Add 134 to Series and hit enter.

<Edit><Run Query> results in just 134 Series records.

Query Design <Save >

Double left click on query name "2 Amps to Volts-High by Series"

Message: The data content could not be loaded.

Error code: 1

<More>       firebird_sdbc error:
                        *Dynamic SQL Error
                        *SQL error code = -104
                        *Invalid usage of boolean expression
                        caused by
                        'isc_dsql_prepare'

By main "Queries", right click and choose "Edit in SQL view..."

Remove AND "Amps"."Series" = '134'

<Edit><Run Query> results in all records

Add AND "Amps"."Series" = '134'

<Edit><Run Query> results in just 134 Series records.

Remove Add AND "Amps"."Series" = '134' and add Add AND "Amps"."Series" = 134

<Edit><Run Query> results in just 134 Series records.

Saved the query in multiple states

When ever I save the Query and double left click from main Query window on "2 Amps to Volts-High by Series"

Results in error message:

<More>       firebird_sdbc error:
                            *Dynamic SQL Error
                            *SQL error code = -104
                            *Invalid usage of boolean expression
                            caused by
                            'isc_dsql_prepare'

In effect, in either edit mode it works correctly.

The saved query always fails.

Hope this helps

Paul

#27

Hi Paul,

Opening the "2 Amps to Volts-High by Series"

There seems to be something wrong in the saved query, which isn't shown
while editing the query.

For other users:
1. I have tested to execute the query.
2. Execution fails.
3. I opened the query in design-view.
4. I executed the query.
5. Execution works.
...
This is the problem, what nobody could understand ...

6. I changed something in the query (removed "Equipment"."Series" = 134
) and saved the query.
7. Closed the design-view.
8. Execution fails.
...
Then I opened the query, added the removed part again, saved ...
All the same.

...But when I open the query and save the query with a new name the
query will be saved with this new name and could be executed. So I
created, for example
"12 Amps to Volts-High by Series"
and it works.

I guess there is something saved in the code of the odb-file, which
isn't loaded for query-design. If I save the query with changed content
this part wouldn't be changed. If I save the query as new the content
will be created from the query-editor-gui.

Never seen such a behaviour before.

Regards

Robert