[SQLite] Search multiple columns in a table and display selected results

[ChenTW]

Hi everyone,

I am developing a LabVIEW application using SQLite for a data logging and analysis system.

The goal of my application is to allow users to search across multiple columns in a table using partial text matching, and also to let users choose which columns should be displayed in the final results.

In my current implementation, I am using the following SQL statement to search across multiple columns:

SELECT *
FROM RawData
WHERE BodyPart LIKE ? OR Position LIKE ? OR Phase LIKE ? OR DateTime LIKE ?;

However, I am running into an issue when trying to use "Bind Text" in LabVIEW. Although I can prepare the statement successfully, I am not able to correctly pass multiple parameters for filtering as expected. ONLY % work ....

I have attached my VI and database file for reference.

Any advice on how to properly handle multiple parameters with SQLite in LabVIEW would be greatly appreciated.

Thanks!

[cordm]

What result do you expect? If you bind even a single %, any row will match.

Does it work correctly if you change the default query for each column to an empty string?

[drjdpowell · Accepted solution]

Your SQL statement is expecting 4 bound parameters.  If you want to only bind one parameter and use it in 4 places then you need to specify it with '?1' rather than '?':

SELECT *
FROM RawData
WHERE BodyPart LIKE ?1 OR Position LIKE ?1 OR Phase LIKE ?1 OR DateTime LIKE ?1;

This indicates you are using the first bound parameter in 4 places. 

[ChenTW · Accepted solution]

Thank you for your reply.

I want to use these four items as filters. I found my issue，it should use AND instead of OR.

SELECT *
FROM RawData
WHERE BodyPart LIKE ? AND Position LIKE ? AND Phase LIKE ? AND DateTime LIKE ?;

This achieves the result I want.

I’m not very familiar with SQLite, so as you suggested, if I want to bind only one parameter and use it in four places, I need to specify it as “?1” rather than “?”.

Is there any documentation or manual related to “?1”? I would like to learn more about how to use it.

Thank you again.

[drjdpowell]

Section 4 of this link is about Parameters: https://sqlite.org/lang_expr.html