U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » Sqlite Delete not working [SOLVED]

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Sqlite Delete not working [SOLVED] [message #34385]

Sat, 19 November 2011 23:10

lectus
Messages: 329
Registered: September 2006
Location: Brazil

Senior Member

Hello!
I'm trying Upp with Sqlite and an ArrayCtrl. So far this is an excellent tool for my tiny database apps. I got all the CRUD operations to work, but DELETE is not working.

Here is the code for DELETE:

String name = arr.Get(0);
Sql sql(db);
String x = "DELETE FROM PHONEBOOK WHERE NAME = '";
x += name;
x += "');";
sql.Execute(x);
arr.Remove(arr.GetCursor());

After this code gets executed the record is removed from ArrayCtrl arr, but the database doesn't change. When I refresh it the record is still there.

Any ideas?

Thanks

[Updated on: Sun, 20 November 2011 02:08]

Report message to a moderator

Re: Sqlite Delete not working [message #34386 is a reply to message #34385]

Sat, 19 November 2011 23:48

lectus
Messages: 329
Registered: September 2006
Location: Brazil

Senior Member

Anyone?

Report message to a moderator

Re: Sqlite Delete not working [message #34387 is a reply to message #34386]

Sun, 20 November 2011 00:15

tojocky
Messages: 607
Registered: April 2008
Location: UK

Contributor

Maybe you need to send commit command to sqlite?

Report message to a moderator

Re: Sqlite Delete not working [message #34388 is a reply to message #34387]

Sun, 20 November 2011 00:20

lectus
Messages: 329
Registered: September 2006
Location: Brazil

Senior Member

I already tried:

db.Commit()

Didn't work either.

Report message to a moderator

Re: Sqlite Delete not working [message #34389 is a reply to message #34385]

Sun, 20 November 2011 01:13

lectus
Messages: 329
Registered: September 2006
Location: Brazil

Senior Member

Hi!
I studied the HomeBudget example and put this code together:

	SqlId PHONEBOOK("PHONEBOOK");
	SqlId NAME("name");
	String name = arr.Get(0);
	SQL = db;
	SQL.Begin();
	SQL * Delete(PHONEBOOK).Where(NAME == name);
	SQL.Commit();
	arr.Remove(arr.GetCursor());

And now it works! Very Happy

Looks like I better use this syntax. But I still haven't figured out why it didn't work using Execute().

Report message to a moderator

Re: Sqlite Delete not working [message #34390 is a reply to message #34389]

Sun, 20 November 2011 01:33

Sender Ghost
Messages: 301
Registered: November 2008

Senior Member

lectus wrote on Sun, 20 November 2011 01:13

But I still haven't figured out why it didn't work using Execute().

Hello.

Because of quotes for name and ")" at the end?
Look here for the raw delete examples.

[Updated on: Sun, 20 November 2011 01:34]

Report message to a moderator

Re: Sqlite Delete not working [message #34391 is a reply to message #34390]

Sun, 20 November 2011 02:07

lectus
Messages: 329
Registered: September 2006
Location: Brazil

Senior Member

Sender Ghost wrote on Sat, 19 November 2011 19:33

lectus wrote on Sun, 20 November 2011 01:13

But I still haven't figured out why it didn't work using Execute().

Hello.

Because of quotes for name and ")" at the end?
Look here for the raw delete examples.

Yeah true. The syntax of the DELETE command was wrong. lol
There was an extra ")" at the end. And the ' are required.
Now it worked.
The correct concatenation of the string is:

	String x = "DELETE FROM PHONEBOOK WHERE NAME = '";
	x += nome;
	x += "';";

Even though the examples you provided are in Tcl language. In this language "name" and name are the same since everything is a string in Tcl.

Report message to a moderator

Re: Sqlite Delete not working [message #34392 is a reply to message #34391]

Sun, 20 November 2011 05:36

Sender Ghost
Messages: 301
Registered: November 2008

Senior Member

lectus wrote on Sun, 20 November 2011 02:07

Yeah true. The syntax of the DELETE command was wrong. lol

The mentioned link suggested to use indexes for WHERE clause. In this case, you didn't need to quote it or use "(" and ")".
Consider to change your database schema and use ArrayCtrl::AddIndex or SqlArray.

[Updated on: Sun, 20 November 2011 06:05]

Report message to a moderator