database SELECT SYNTAX

[nomade42k]

hello

I have a VI made with DB connectivity toolkit.  On it, I am using a DB tool select data.  I am trying to read a specific data from a table using a SQL query (in condition).  Well, when a using the sentence

SELECT id FROM SensoresIR1 WHERE id=36151

labview give me a error:

Possible reason(s):

ADO Error: 0x80040E14

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.1.36-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM SensoresIR1 WHERE id=36151' at line 1 in NI_Database_API.lvlib:Conn Execute.vi->NI_Database_API.lvlib:DB Tools Select Data.vi->interfaz1.vi

Possible reason(s):ADO Error: 0x80040E14Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.1.36-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM SensoresIR1 WHERE id=36151' at line 1 in NI_Database_API.lvlib:Conn Execute.vi->NI_Database_API.lvlib:DB Tools Select Data.vi->interfaz1.vi

can you help me?

Carlos Bolvaran

Vesat Ingenieria

[PhillipBrooks]

It appears you are querying the table SensoresIR1 for the 'id' that you already know.

[nomade42k]

Hello!

I put 36151 just to simplify the ask.  I need to read the last data from the SensoresIR1 table, but the syntax for the query SELECT doesn't work

[PhillipBrooks]

What is the name of the last field in the table?

Your query should be something like:

SELECT LastField FROM SensoresIR1 WHERE id=36151

LastField would be replaced with the name of the field as defined in the database...

[nomade42k]

The table has data from a sensor that measures open or close.  SensoresIR1 has the fields: id-time-timeint-measure-norm-period.  The only field that identify a certain data is id.

id is a autonumeric field, so I dont know what is the last number for id, but I known that it is the maximun value for id or, like you say, the last field.

I will try with your sentence

[Dennis_Knutson]

What's the data type for your id column?

You say you don't know the last id but then you are specifying an id and assuming that is the last entry?

[nomade42k]

In mysql database, id is autonumeric.  In labview I assume that it is variant.  When I use 36151 here is for simplify the question.

Now,  I have used the syntax 

where id=(SELECT MAX(id) FROM SensoresIR1) in condition field on DB tool select data VI, and my VI work.

Can the syntax be different for different versions of ODBC conector?

[mariusz]

Try this:

SELECT * FROM SensoresIR1 ORDER BY id DESC;

This will return all fields, order by id in descending order.

If you want to limit number of returned rows do this

SELECT * FROM SensoresIR1 ORDER BY id DESC LIMIT 10;

and if you want to select only few columns

SELECT col1, col2 FROM SensoresIR1 ORDER BY id DESC LIMIT 10;

and finally if you want to rename returned columns to something better looking :

SELECT col1 AS "COLUMN 1" FROM SensoresID1 ORDER BY id DESC LIMIT 10;

Mariusz

[PhillipBrooks]

Assuming that your table is a simple one (not a stored procedure, no triggers or other customization), you can usually use a special query to retreive the IDENTITY 

SELECT @@IDENTITY

[PhillipBrooks]

Don't know what happened to my post, it should have had an additional sentence above 

---

If you have just added the data to the table using LabVIEW and want to return the id number in order to create records in a linked table, there are various ways to query (as shown by nomade42k and Mareiusz).

Assuming that your table is a simple one (not a stored procedure, no triggers or other customization), you can usually use a special query to retreive the IDENTITY 

SELECT @@IDENTITY