Problems connecting to Sage - SQL syntax errors

[MayburyT]

I am relatively non-technical but loads of user experience with Excel, Access and Sage
I have created a Base database using the ODBC Driver to connect to Sage - connection tested OK and the tables all appear.
Executing a simple SQL command ( SELECT * FROM STOCK) in the Tools>SQL>Execute SQL Statement box gives a status of "Command successfully executed."
However
Any attempt to actually view the contents of the files results in errors
data content could not be loaded.
SQL Status: HY000, Error code: 1000, Syntax error in SQL expression
SQL Status: HY000, Error code: 1000, SELECT * FROM 'STOCK'
SQL Status: HY000, Error code: 1000, syntax error, unexpected STRING, expecting '(' or '{' or NAME

Using the Query design to create the same command (SELECT * FROM STOCK) using no commas, single or double inverted commas, and pressing Run, all result in errors.

Trying to understand a few online explanations suggests there is an issue with Base putting the wrong kind of inverted commas around the Table name. But I cannot follow any solution suggested.

Is there a way of resolving this? I would like to eventually use the Design view rather than direct SQL input.

Thanks

[r4zoli]

OOo/LibO Base user interface require using double quotes. [Tutorial] SQL syntax in odb file with HSQLDB
You can use any SQL dialect, when you connect to external data source, what is happened in your case, if you run in queries in "SQL direct" mode, (active green tick in query window). You must use SQL syntax of the connected server.

[MayburyT]

Hello
Sorry, reading my description I dont think I was clear.
I have tried
SELECT * FROM STOCK
SELECT * FROM 'STOCK'
and
SELECT * FROM "STOCK"

All failed and received the error msgs described below. Although notice the error msg says - SELECT * FROM 'STOCK' - with single quotes even though I put in double quotes.

Reading various threads, I think I am trying to find a simple tick box type solution that will either:
1. change the way Base sends its SQL queries to the ODBC or
2. change the way the ODBC interpretes the SQL coming from Base.

ie stop putting single quotes and put in double quotes.

I think.

As I say I am not that technical
Thanks

[FJCC]

As R4zoli said, the button you are looking for is on a tool bar of the window where you type in a query. It shows a green check mark and the text "SQL". Alternatively, you can select the menu Edit -> Run SQL Directly. Either the button of the menu item will cause Base to send the text of the query directly to the attached database server with no extra processing.

[MayburyT]

Hello R4zoli & FJCC

Thank-you for that - apologies I couldnt see that box for looking!!
The SQL now works when entered directly into that query SQL view. (as you knew it would!)
All of these work in the same way now
SELECT * FROM Stock, SELECT * FROM 'Stock', and SELECT * FROM "Stock"
I deliberately changed the table name to sentence case to check.


I still have the challenge of using the query design view. I dont see any equivalant "run SQL directly" button.

The closest I have found to a description of the problem & resolution I could understand (but not able to resolve) is the following:
===================================================================
The Sage server interprets single quotes as string delimiters
SQL servers interprete single quotes as literal string delimiters and double quotes as entity name delimiters

There seems to be a conflict between Base and the ODBC interface as to which is which.

Look in the configuration options of the ODBC connection for entries like "Safe SQL syntax" or "ANSI quotes", and toggle those.
======================================================================

I have looked in the ODBC connection configuration for any kind of option toggles.
Cant find any toggles at all.


I think this is summarised by:
I need to send my SQL to Sage with double quotes but either Base or the ODBC is translating my double quotes into single quotes throwing Sage into a wobbly.

Is there any way I can toggle any setting anywhere to be able to use the query design view?

Thanks for your time

[r4zoli]

Sage it is a database engine or accountig program which uses some SQL database behind?
The query design view not works with direct SQL mode, you needs to write SQL code to use it.

[rudolfo]

As far as I remember from some contacts with Sage, the program typically uses either an MS Access (Jet) database backend or an SQL-Server (mostly the light/redistributable version of it). ODBC is not too far away from the standard, at least I never needed to work with those braces {outer join} when working with ODBC. And generally if your query works in Tools -> SQL, which is always talking directly to the database backend, the same kind of quoting scheme will work in the query with the green tic "direct SQL" checked. Of course in Tools - SQL your query won't return anything, but it will throw an error if a column or something else is wrong with it.
As all database engines that I know use the single quotes as string delimiter you must never enclose column or table names with single quotes. This CAN'T work! MySQL for example uses backtics for this, which might look like single quotes, but are a completely different thing. If any database backend engine forces you to enclose table or column names with single quotes, try to get rid of it as soon as possible. Really, whoever programmed such a thing shows his complete ignorance of the SQL language.