Unable to delete one row in database

[StavA]

Hi there!

I am a relatively new Labview user, and this is my First post on these forums. I posted an issue on the NI forums, but figured it wouldn't hurt to look elsewhere for help. Below is a link to my issue outlined on the NI forums, but i shall do so again.

Link to first thread:

http://forums.ni.com/t5/LabVIEW/Unable-to-delete-one-row-from-Database/m-p/3340518/highlight/false#M981003

Setup: postgresql database with table clust, in the public schema. This table has 3 columns; user, password, privilege. This table has 2 test entries; the first entry (alex, 123, 123) and second entry (mike, 456, 456)

What i am trying to do: delete one row from a table in my database.

What is happening: When i try to delete the entry alex, it deletes all the entries. When i try to delete mike, nothing happens.
Below are 2 images of an extremely simple VI running, and completing without error, except what i outlined above. Hopefully someone knows what is going on, as i really have no clue.

Thanks for reading,

StavA

 

Edited August 29, 2016 by StavA

[Jordan Kuehn]

Does  postgresql  require a semicolon at the end of the query?

[StavA]

I have tried with and without, it has made no discernible difference that i can tell.

[ShaunR]

try

DELETE FROM %s WHERE 'user' ILIKE '%%%s%%';

maybe the data has inadvertent spaces or capitlisation

Edited August 29, 2016 by ShaunR

[StavA]

HI ShaunR, thank you for taking a look at my problem and providing a potential solution. Unfortunately, it was not successful. Here is a screenshot of the suggested change and the output, which runs fine. However the entry (mike, 456, 456) still remains in the database.

[ShaunR]

Can you show the result of a query that returns all rows before and after you have tried to delete?

BTW. It is better to post a snippet or the actual VI you are using than normal images.

[Jordan Kuehn]

Try encasing the column name in double quotes "user" and using the original query with the = comparitor and the semicolon.

postgresql test.vi

Edited August 30, 2016 by Jordan Kuehn
add example vi

[Darin]

If all you are doing is querying a DB you may not notice that you are implicitly inside a transaction.  To make changes that are available outside the current session you need to explicitly commit.

[StavA]

Thanks for everyones responses to my query. I found out the problem. The problem isn't in Labview, it was in postgreSQL. Specifically, the column 'user' is a reserved key word, and it just so happened that the owner of the database is named alex. So what was happening was whenever i specified to delete from table where user = 'alex', it would delete everything that belonged to the owner, alex. The simple fix is to not use the keyword 'user', instead using name or userid. That resolved the issue. Basically, the stars aligned to cause me a world of grief.

Anyways, this post can be marked as resolved/closed/ended.