UTF8 database bug

[User002]

Hello all,

I have found a possible bug in the LV Database toolkit.

For example a query to a database encoded with SQL_ASCII, that works with the toolkit:

SELECT col FROM public.test

However, with a PostgreSQL UTF8 database it has to be modified to:

SELECT "col" FROM public.test

As it is now, I have to add quotes for every column, which leads to fairly "UTF8-specific" database LV classes?

I assume the toolkit is not intended to work this way? 

Br,

/Roger

[rolfk]

I don't think it has anything to do with UTF8.

The quoted syntax is simply the correct official SQL syntax while some database backends do allow the more relaxed unquoted identifiers IF and only IF the identifiers do not contain spaces and do not conflict with reserved words. Your PostgreSQL client interface may be one who specifically insists on proper official SQL syntax quoting in all cases. That has nothing to do with the database toolkit but everthing with the database client library you are using to connect to your database.

[User002]

---

@rolfk wrote:

I don't think it has anything to do with UTF8.

The quoted syntax is simply the correct official SQL syntax while some database backends do allow the more relaxed unquoted identifiers IF and only IF the identifiers do not contain spaces and do not conflict with reserved words. Your PostgreSQL client interface may be one who specifically insists on proper official SQL syntax quoting in all cases. That has nothing to do with the database toolkit but everthing with the database client library you are using to connect to your database.

---

The SQL_ASCII database is also using a PostgreSQL backend.

If I follow your reasoning, then the database toolkit both works and does not work with PostgreSQL databases?

/Roger

[User002]

---

@RogerI wrote:

---

@rolfk wrote:

I don't think it has anything to do with UTF8.

The quoted syntax is simply the correct official SQL syntax while some database backends do allow the more relaxed unquoted identifiers IF and only IF the identifiers do not contain spaces and do not conflict with reserved words. Your PostgreSQL client interface may be one who specifically insists on proper official SQL syntax quoting in all cases. That has nothing to do with the database toolkit but everthing with the database client library you are using to connect to your database.

---

The SQL_ASCII database is also using a PostgreSQL backend.

If I follow your reasoning, then the database toolkit both works and does not work with PostgreSQL databases?

/Roger

---

I forgot to mention that if I add quotes, as you stated is the correct SQL syntax(?), then I get errors from the LV example access database and the non UTF postgresql database? Anyway, it is quite fishy to add quotes to a LV String?

The database charset can easily be checked, below two of the most famous vendors.

Oracle:

SELECT * FROM NLS_DATABASE_PARAMETERS

Postregsql:

SELECT pg_database."encoding" FROM pg_catalog.pg_database

Br,

/Roger

[mikeporter]

If I were you I'd be reporting a bug to the postgresql folks. I have worked with many different DBMS and have never seen one that required you to use quotes around column names that don't have spaces. What was the error you got from postgresql without the quotes, and the lv error you got with them?

Mike...

[User002]

---

@mikeporter wrote:

If I were you I'd be reporting a bug to the postgresql folks. I have worked with many different DBMS and have never seen one that required you to use quotes around column names that don't have spaces. What was the error you got from postgresql without the quotes, and the lv error you got with them?

Mike...

---

Assuming I have spaces and/or localized chars in the column names, am I required to add quotes to the LV string(s) containing the names, or will the database toolkit handle that for me?

I think the bug report is better suited over here. 

Br,

/Roger

[rolfk]

---

RogerI wrote

Assuming I have spaces and/or localized chars in the column names, am I required to add quotes to the LV string(s) containing the names, or will the database toolkit handle that for me?

I think the bug report is better suited over here. 

Br,

/Roger

---

The LV Toolkit is only a thin wrapper around the OLE Database interface from Microsoft. It does not know about the actual database used so can't really decide if a specific word in the SQL string is a reserved keyword or identifier and if it is an identifier if it should be quoted.

As such the Toolkit can't really guess the right thing for you. If you use the higher level functions to create the SQL strings it will attempt to do its best to create a properly formated SQL syntax string but eventhough SQL is supposed to be a standard there are always databases or database drivers that decide to do their own special syntax handling.

The problem here clearly is either in the used OLE or ODBC database driver or in the database backend itself.

[User002]

---

@rolfk wrote:

---

RogerI wrote

Assuming I have spaces and/or localized chars in the column names, am I required to add quotes to the LV string(s) containing the names, or will the database toolkit handle that for me?

I think the bug report is better suited over here. 

Br,

/Roger

---

The LV Toolkit is only a thin wrapper around the OLE Database interface from Microsoft. It does not know about the actual database used so can't really decide if a specific word in the SQL string is a reserved keyword or identifier and if it is an identifier if it should be quoted.

As such the Toolkit can't really guess the right thing for you. If you use the higher level functions to create the SQL strings it will attempt to do its best to create a properly formated SQL syntax string but eventhough SQL is supposed to be a standard there are always databases or database drivers that decide to do their own special syntax handling.

The problem here clearly is either in the used OLE or ODBC database driver or in the database backend itself.

---

In the "DB Tools Select Data.vi" the front panel "columns control" expects an array of strings. Now, it is pretty clear what the string contains (the db columns) at this stage and no guessing or parsing is required?

The problem here is that this vi clearly only works with a sql_ascii database and/or database drivers, since it has no logic to determine if it should quote the column before making the query.

Thin wrapper or not, it has some functionality that can be expected to work?

Br,

/Roger

[rolfk]

If there would be a definitive SQL standard!!! You have found yourself that quoting the identifiers seems to be required for one driver interface and causes errors for the other. So how should that function know what to do????

[User002]

---

@rolfk wrote:

If there would be a definitive SQL standard!!! You have found yourself that quoting the identifiers seems to be required for one driver interface and causes errors for the other. So how should that function know what to do????

---

Actually, it both works and does not work on the same backend and driver, it all comes down to what type of database (localized or not) I'm interfacing (hint: read previous posts).

As I said, there are some missing logic in the method. One way to solve this would be to check if it is a localized database at connection. Issuing a few SQL commands to verify the database type would hardly be a big modification to the existing code. It could probably be done in a few hours of work.

When can I start to commit patches upstream? 

Br,

/Roger