Libreoffice Base Query

[BobDe1]

I’ve created a simple query in base
SELECT memberpersonalinfo.RosterID, memberpersonalinfo.First_Name, memberpersonalinfo.Last_Name, memberpersonalinfo.Phone_Number, memberpersonalinfo.E-mail, memberpersonalinfo.DateJoined, memberpersonalinfo.DateResigned, rosterinfo.Team, rosterinfo.Status FROM security.rosterinfo AS rosterinfo, security.memberpersonalinfo AS memberpersonalinfo WHERE rosterinfo.RosterID = memberpersonalinfo.RosterID AND memberpersonalinfo.DateResigned IS NULL AND rosterinfo.Team = :Team
and keep getting this error
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘:Team’ at line 1

Any Ideas??
Bob

[Wanderer]

Have you set “direct” SQL or is Base allowed to interpret your SELECT?
.
IMHO in direct mode use of :param is not allowed.

[BobDe1]

Ok… you got me Where would I find that?
Bob

[Wanderer]

Check the linked thread. The icon may have changed, but it is still the right button in the row, when efiting in SQL-Mode:

Many Base Features Don't Work in SQL Direct / Native Mode

When working with an external database such as MySQL, there are times when it is logical to Run SQL command directly, also known as Native SQL, also known as Analyze SQL command = no, also known as Escape Processing = False. Yeah, lots of terms for the same thing in Base. I’m not going to go into lengthy details to describe when it is a good idea to enable this mode, as that’s off topic. Here are some images for those who don’t know what I’m talking about. All these images show the same thing. I…

[mariosv]

Perhaps you need the

memberpersonalinfo.E-mail

between quotes, I don’t think the hyphen - is allowed in such way.

[Wanderer]

@mariosv

don’t think the hyphen - is allowed in such way

But I would not expect a syntax error, just subtracting the content of “mail”, if this exists.
.
Actually there are few restrictions on column names:
Identifier Names - MariaDB Knowledge Base.
.
And maybe I was right with my first idea, because Base would have added quotes everywhere, if @BobDe1 didn’t switch direct SQL on. (I assume he copied his query from SQL-view in Base…)

[BobDe1]

I noticed the original query was incomplete …Here is the entire query
SELECT memberpersonalinfo.RosterID, memberpersonalinfo.First_Name, memberpersonalinfo.Last_Name, memberpersonalinfo.Phone_Number, memberpersonalinfo.E-mail, memberpersonalinfo.DateJoined, memberpersonalinfo.DateResigned, rosterinfo.Team, rosterinfo.Team2, rosterinfo.Team3 FROM security.rosterinfo AS rosterinfo, security.memberpersonalinfo AS memberpersonalinfo WHERE rosterinfo.RosterID = memberpersonalinfo.RosterID AND ( memberpersonalinfo.DateResigned IS NULL AND rosterinfo.Team = :Team OR rosterinfo.Team2 = :Team OR rosterinfo.Team3 = :Team )
And here is the error
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘:Team OR rosterinfo.Team2 = :Team OR rosterinfo.Team3 = :Team )’ at line 1
I tried the “Run SQL directly” and it failed as soon as I tried to execute the query…
With “Run SQL directly” off, the query will ask for a team number and then it fails
Thanks

Bob

[cpb]

@BobDe1,
.
you do not say how you connect.
.
you have not revealed your operating system.
.
you use CamelCase in your query but do not quote your field names therefore we have to assume that all of your field names are lower case.

@mariosv

I don’t think the hyphen - is allowed in such way

which is correct so you need to enclose e-mail within back-ticks.
.
as for the PARAM issue:
menu:>Edit>Database>Advanced Settings
make sure that Replace named parameters with “?” is checked.

[BobDe1]

OOps… forgot that… I’m using windows 10 as an OS and am connect MySQL/MariaDB.
All the tables are lowercase and all the field names in the tables are camelcase
“as for the PARAM issue:
menu:>Edit>Database>Advanced Settings
make sure that Replace named parameters with “?” is checked.”
I checked and can not find it…
Thanks
Bob

[RobertG]

@BobDe1 : You won’t get ‘Replace named parameters with “?”’ if you are connecting to MariaDB directly. It is a known error when connecting to MariaDB with JDBC-connection. Show a screenshot of your statusbar at the bottom of the Base file. This will show the connection type.

Could you create forms with subforms? Is it possible to input data in such a construction? Then parameters will be replaced correctly. You could also see this when opening the *.odb-file with a packing program, switch to content.xml and have a look for db:parameter-name-substitution="false". This should be set to true or could removed completely.

Execute direct: Open the query for editing. Switch design view off to see the SQL-code. The button on the right in toolbar will switch to executing in direct SQL. If this is pressed the code will be send directly to MariaDB. So no parameter will be asked for and MariaDB don’t know what to do with :Team.

[cpb]

@BobDe1,
.
the first statement made in my initial post was “you do not say how you connect”.
we still do not know and I was happy to leave it there.
however in light of the comments made by RobertG I feel the need to reply.
.
I have used MariaDB 11.0.2 on both windows 10 and windows 11 using JDBC without issue.
.
here is the connection string which was used for this specific db:
mariadb://127.0.0.1:3306/parameters?user=root&password=amanda

you can see from the pic that database name = “parameters”, user = “root” and password = “amanda”.

[BobDe1]

I got it !! Thanks, all of you…

Here are the three screenshots you asked for and when I opened the odb file I found
db:parameter-name-substitution=“false” … I changed it to “true” and now it works perfectly !!

Thanks again everyone
Bob

[cpb]

it seems that if you create your ODB using this connection (MySQL/MariaDB) then you inherit the bug:

but if you create your ODB using this connection (JDBC) then you do not inherit the bug:

my preferred open source engine is PostgreSQL and when creating the ODB I always select JDBC, selecting PostgreSQL also results in buggy behaviour.