Re: [sqlite] Delete From Not Working Properly
I got it working finally. There were several problems that were all
contributing and it was not until Kees suggested looking at the
begins/commits to see if errors were happening that I saw the whole
picture. As mentioned in my past response to the thread, the code worked
when saving after starting a new game but not after loading an already
started game. The problem was both in the loading code and in the way in
which I was loading the data through my calls in my Database class.
In my loading code I have several areas where I must close and re-open a
connection to the database because of how some things are loaded (some
elements are loaded in as only ID values which I then get the data for from
my Database sqlite file which contains non-player/game instance specific
data like weapons information or message). Well there was a problem here
where the database connection could not be closed because there were
unfinalized statements. This is where the second area of problems comes
in. I was not placing my calls to my finalize function at the end of the
query functions. This meant that if there was a problem, finalize would
not be called and the prepared statement would not be destroyed.
Another problem was in my getRows function. The same issue as above
existed except that there were multiple chances for the statement to not be
finalized as I was returning 0 at any point an error was found. After
placing the calls to finalize in their correct places I found the final
thorn in my program which were the database closes in dataSystem in lData.
I had them setup so that they would be called even if the database was not
actually closed. This caused even more problems by invalidating previous
database connections and creating new ones. The end result was that by the
time sData in Database was called the database connection was mutilated.
Fixed everything up and ran the loading process and I was able to rid
myself of several bugs at once including getting the Delete From query to
execute properly.
Thanks for the suggestions and feedback you have all given me, it helped
tremendously.
On Fri, Feb 21, 2014 at 2:15 PM, Geo Wil wrote:
> Hello,
>
> Yesterday, while working on my game, I noticed that my game data was not
> updating in my save SQLite3 database. So I started tracking down what was
> going on and eventually my search lead me to my dData function.
>
> How this function works is that you pass a table name and a bool, the bool
> is just there in case errors happen to notify the player. I did exhaustive
> tests yesterday on this block of code and found that it was not functioning
> properly, it was not deleting the data from my tables.
>
> I checked to see if sqlite3_step was producing an error but it was sending
> back a value of 101 or SQLITE_DONE but the table data remained unchanged.
> I also commented out the code I use to replace the deleted data just to
> make sure the data retrieval code was not at fault. With just the dData
> call and nothing else the data still would not delete from the table.
>
> Here is the code I am using for my dData function:
>
> void Database::dData(string table, bool* bErrors)
> {
> sqlStr2 = "Delete From " + table;
>
> sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);
>
> if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
> &statement2, 0) == SQLITE_OK)
> {
> sqlite3_step(statement2);
> *bErrors = false;
>
> finalize(statement2, bErrors);
> }
>
> else
> {
> *bErrors = true;
> createBInfo();
> d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale +
> to_string(__LINE__),bTDate,"./SC_Log.txt");
> }
>
> sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
> }
>
> I am also going to link to my save database in case it itself is to blame
> although I tested this on several different files with the same results.
>
> http://sc.lmpgames.com/scSave.sqlite
>
> I already have posted about this on StackOverflow and so far everyone that
> has attempted to figure this out has been stumped. I am using Microsoft
> Visual Studio 2012 and C++.
>
> Thanks.
>
> --
> "I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
> should I?" -- Laughing Man
> __
>
> Laughing Man Productions² Entertainment and Gaming
> Network
>
--
"I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
should I?" -- Laughing Man
__
Laughing Man Productions² Entertainment and Gaming
Network
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On 21 Feb 2014, at 9:15pm, Geo Wil wrote: > I checked to see if sqlite3_step was producing an error but it was sending > back a value of 101 or SQLITE_DONE but the table data remained unchanged. Putting this sentence together with the 'Subject' header, it might just be worth reminding you that a 'DELETE FROM' that deletes no rows is perfectly legit command and returns SQLITE_DONE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On 2014/02/22 01:37, Geo Wil wrote: As for the fail path issue, it is not an issue or at least it has never been for me. The way I understand it is that if you just put the file name into a function requesting a path in Windows it will look in the folder the process is running from for that file. // Yes of course, but the problem is the typical folder a program will be installed to (which may not be the case in the dev machine) is c:\Program Files\ and that path is special and protected, and there is no sub-folder of that path that windows will allow you to keep a file and make changes to it unless your manifest specifically requires it (and even then the user will be bothered every time for access grants). You need to save the data very far away from the exe, such as the program-data folder I described earlier. You may have switched off the UAC on the dev PC, but it will be alive and well on an intended user's PC and it will not allow you (or anyone else - which is the beauty of it) to overwrite any file inside your program's exe folder or sub-folder thereof. Although you are right, I should be a bit more explicit with the path, maybe use ./Databases/[filename] so that it will explicitly look in the same folder and not have to use a default behavior that can sometimes be fallible. Being explicit about the name is of no consequence when the resulting location of the file is still inside a protected folder. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On 2/21/2014 6:37 PM, Geo Wil wrote: As for the fail path issue, it is not an issue or at least it has never been for me. The way I understand it is that if you just put the file name into a function requesting a path in Windows it will look in the folder the process is running from for that file. No, it would look in the current working directory (see GetCurrentDirectory, SetCurrentDirectory), which may or may not be the same directory where the EXE is located. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
I thought of another possible reason why this is going on. The code in
question works under one set of circumstances and not under another. If
there is no data in my database then I am able to save data. This begged
the question "why?" and the answer is because I do not load my data if I
start a new game. I have been looking at my lData code in my dataSystem
class and I found that I am getting errors there too when trying to close
my database connection from there. This, I think, is why I am getting
locked out during the save process. I am going to try updating the code in
lData and see what happens.
As for the fail path issue, it is not an issue or at least it has never
been for me. The way I understand it is that if you just put the file name
into a function requesting a path in Windows it will look in the folder the
process is running from for that file. Although you are right, I should be
a bit more explicit with the path, maybe use ./Databases/[filename] so that
it will explicitly look in the same folder and not have to use a default
behavior that can sometimes be fallible.
Thanks for the suggestions. Will post back what the results of my work
with lData are.
On Fri, Feb 21, 2014 at 3:59 PM, RSmith wrote:
>
> On 2014/02/22 00:32, Geo Wil wrote:
>
>> 1. Windows 7 Ultimate 64-bit
>>
>> 2. Here is the path I am using:
>> void Database::openSave(bool* bErrors)
>> {
>> if (sqlite3_open("*scSave.sqlite*",&dBase) != SQLITE_OK)
>>
>> {
>> *bErrors = true;
>> createBInfo();
>> d.createBReport("SQL Code 1",sqlite3_errmsg(dBase),bLocale +
>> to_string(__LINE__),bTDate,"./SC_Log.txt");
>> }
>>
>> else
>> {
>> *bErrors = false;
>> }
>> }
>>
>> 3. I checked that earlier today as well as the permission on the database
>> itself. Everything checks out.
>>
>> 4. Here you inspired me to do some extra digging. I added some couts to
>> the player data transaction block in the event that error is not null and
>> I
>> got an error of "Database is Locked". This was surprising because I have
>> no programs open that have my save database open and the only other call
>> to
>> the database before the transaction block starts is the openSave function
>> listed above.
>>
>>
> That path doesn't seem like a path, but just a call to open a filename,
> which should be in the same folder as the exe. Now, depending on where the
> exe is in Win7, that could be a problem. If your exe is in any of the
> protected or system folders (such as Program_files), Windows won't let you
> edit files in those places, it might simply shift the file to the
> virtualized folder and open it there, which usually works ok and very much
> transparent to your app - unless you are trying to force the full path in
> an open statement, such as SQLite should be doing. This may explain why the
> permission seem ok but the physical file is locked when you try to open it.
>
> The way to fix this is to use the correct program data path (typically
> "c:\users\myUser\AppData\Roaming\MyProgramName\") or the user's documents
> folder (should you want this file to be handled by the user at some point),
> in stead of the exe path - and/or to let Windows know that you know what
> you are doing and expect your datafiles to be changed by specifying a
> manifest to your exe with appropriate settings - just MSDN "Manifest file"
> for examples.
>
> btw: Getting the path to the correct program data path on any PC requires
> simply a call to the Windows Shell API with the correct parameter, again
> just MSDN it.
>
> Good luck!
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
"I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
should I?" -- Laughing Man
__
Laughing Man Productions² Entertainment and Gaming
Network
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On 2014/02/22 00:32, Geo Wil wrote:
1. Windows 7 Ultimate 64-bit
2. Here is the path I am using:
void Database::openSave(bool* bErrors)
{
if (sqlite3_open("*scSave.sqlite*",&dBase) != SQLITE_OK)
{
*bErrors = true;
createBInfo();
d.createBReport("SQL Code 1",sqlite3_errmsg(dBase),bLocale +
to_string(__LINE__),bTDate,"./SC_Log.txt");
}
else
{
*bErrors = false;
}
}
3. I checked that earlier today as well as the permission on the database
itself. Everything checks out.
4. Here you inspired me to do some extra digging. I added some couts to
the player data transaction block in the event that error is not null and I
got an error of "Database is Locked". This was surprising because I have
no programs open that have my save database open and the only other call to
the database before the transaction block starts is the openSave function
listed above.
That path doesn't seem like a path, but just a call to open a filename, which should be in the same folder as the exe. Now,
depending on where the exe is in Win7, that could be a problem. If your exe is in any of the protected or system folders (such as
Program_files), Windows won't let you edit files in those places, it might simply shift the file to the virtualized folder and open
it there, which usually works ok and very much transparent to your app - unless you are trying to force the full path in an open
statement, such as SQLite should be doing. This may explain why the permission seem ok but the physical file is locked when you try
to open it.
The way to fix this is to use the correct program data path (typically "c:\users\myUser\AppData\Roaming\MyProgramName\") or the
user's documents folder (should you want this file to be handled by the user at some point), in stead of the exe path - and/or to
let Windows know that you know what you are doing and expect your datafiles to be changed by specifying a manifest to your exe with
appropriate settings - just MSDN "Manifest file" for examples.
btw: Getting the path to the correct program data path on any PC requires simply a call to the Windows Shell API with the correct
parameter, again just MSDN it.
Good luck!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
1. Windows 7 Ultimate 64-bit
2. Here is the path I am using:
void Database::openSave(bool* bErrors)
{
if (sqlite3_open("*scSave.sqlite*",&dBase) != SQLITE_OK)
{
*bErrors = true;
createBInfo();
d.createBReport("SQL Code 1",sqlite3_errmsg(dBase),bLocale +
to_string(__LINE__),bTDate,"./SC_Log.txt");
}
else
{
*bErrors = false;
}
}
3. I checked that earlier today as well as the permission on the database
itself. Everything checks out.
4. Here you inspired me to do some extra digging. I added some couts to
the player data transaction block in the event that error is not null and I
got an error of "Database is Locked". This was surprising because I have
no programs open that have my save database open and the only other call to
the database before the transaction block starts is the openSave function
listed above.
On Fri, Feb 21, 2014 at 3:06 PM, Kees Nuyt wrote:
> On Fri, 21 Feb 2014 14:15:10 -0700, Geo Wil
> wrote:
>
> >Hello,
> >
> >Yesterday, while working on my game, I noticed that my game data was not
> >updating in my save SQLite3 database. So I started tracking down what was
> >going on and eventually my search lead me to my dData function.
> >
> >How this function works is that you pass a table name and a bool, the bool
> >is just there in case errors happen to notify the player. I did
> exhaustive
> >tests yesterday on this block of code and found that it was not
> functioning
> >properly, it was not deleting the data from my tables.
> >
> >I checked to see if sqlite3_step was producing an error but it was sending
> >back a value of 101 or SQLITE_DONE but the table data remained unchanged.
> >I also commented out the code I use to replace the deleted data just to
> >make sure the data retrieval code was not at fault. With just the dData
> >call and nothing else the data still would not delete from the table.
> >
> >Here is the code I am using for my dData function:
> >
> >void Database::dData(string table, bool* bErrors)
> >{
> >sqlStr2 = "Delete From " + table;
> >
> >sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);
> >
> >if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
> >&statement2, 0) == SQLITE_OK)
> >{
> >sqlite3_step(statement2);
> >*bErrors = false;
> >
> >finalize(statement2, bErrors);
> >}
> >
> >else
> >{
> >*bErrors = true;
> >createBInfo();
> >d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale +
> >to_string(__LINE__),bTDate,"./SC_Log.txt");
> >}
> >
> >sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
> >}
> >
> >I am also going to link to my save database in case it itself is to blame
> >although I tested this on several different files with the same results.
> >
> >http://sc.lmpgames.com/scSave.sqlite
> >
> >I already have posted about this on StackOverflow and so far everyone that
> >has attempted to figure this out has been stumped. I am using Microsoft
> >Visual Studio 2012 and C++.
>
> Which version of MS Windows?
> What is the path to the database file?
> Is the database perhaps stored in a "protected" directory?
> Any errors on execution of the BEGIN and COMMIT statements?
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
"I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
should I?" -- Laughing Man
__
Laughing Man Productions² Entertainment and Gaming
Network
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
Please consider adding an error and warning log (
http://www.sqlite.org/errlog.html) to your program to record any errors
that SQLite might be encountering.
On Fri, Feb 21, 2014 at 4:15 PM, Geo Wil wrote:
> Hello,
>
> Yesterday, while working on my game, I noticed that my game data was not
> updating in my save SQLite3 database. So I started tracking down what was
> going on and eventually my search lead me to my dData function.
>
> How this function works is that you pass a table name and a bool, the bool
> is just there in case errors happen to notify the player. I did exhaustive
> tests yesterday on this block of code and found that it was not functioning
> properly, it was not deleting the data from my tables.
>
> I checked to see if sqlite3_step was producing an error but it was sending
> back a value of 101 or SQLITE_DONE but the table data remained unchanged.
> I also commented out the code I use to replace the deleted data just to
> make sure the data retrieval code was not at fault. With just the dData
> call and nothing else the data still would not delete from the table.
>
> Here is the code I am using for my dData function:
>
> void Database::dData(string table, bool* bErrors)
> {
> sqlStr2 = "Delete From " + table;
>
> sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);
>
> if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
> &statement2, 0) == SQLITE_OK)
> {
> sqlite3_step(statement2);
> *bErrors = false;
>
> finalize(statement2, bErrors);
> }
>
> else
> {
> *bErrors = true;
> createBInfo();
> d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale +
> to_string(__LINE__),bTDate,"./SC_Log.txt");
> }
>
> sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
> }
>
> I am also going to link to my save database in case it itself is to blame
> although I tested this on several different files with the same results.
>
> http://sc.lmpgames.com/scSave.sqlite
>
> I already have posted about this on StackOverflow and so far everyone that
> has attempted to figure this out has been stumped. I am using Microsoft
> Visual Studio 2012 and C++.
>
> Thanks.
>
> --
> "I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
> should I?" -- Laughing Man
> __
>
> Laughing Man Productions² Entertainment and Gaming
> Network
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On Fri, 21 Feb 2014 14:15:10 -0700, Geo Wil
wrote:
>Hello,
>
>Yesterday, while working on my game, I noticed that my game data was not
>updating in my save SQLite3 database. So I started tracking down what was
>going on and eventually my search lead me to my dData function.
>
>How this function works is that you pass a table name and a bool, the bool
>is just there in case errors happen to notify the player. I did exhaustive
>tests yesterday on this block of code and found that it was not functioning
>properly, it was not deleting the data from my tables.
>
>I checked to see if sqlite3_step was producing an error but it was sending
>back a value of 101 or SQLITE_DONE but the table data remained unchanged.
>I also commented out the code I use to replace the deleted data just to
>make sure the data retrieval code was not at fault. With just the dData
>call and nothing else the data still would not delete from the table.
>
>Here is the code I am using for my dData function:
>
>void Database::dData(string table, bool* bErrors)
>{
>sqlStr2 = "Delete From " + table;
>
>sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);
>
>if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
>&statement2, 0) == SQLITE_OK)
>{
>sqlite3_step(statement2);
>*bErrors = false;
>
>finalize(statement2, bErrors);
>}
>
>else
>{
>*bErrors = true;
>createBInfo();
>d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale +
>to_string(__LINE__),bTDate,"./SC_Log.txt");
>}
>
>sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
>}
>
>I am also going to link to my save database in case it itself is to blame
>although I tested this on several different files with the same results.
>
>http://sc.lmpgames.com/scSave.sqlite
>
>I already have posted about this on StackOverflow and so far everyone that
>has attempted to figure this out has been stumped. I am using Microsoft
>Visual Studio 2012 and C++.
Which version of MS Windows?
What is the path to the database file?
Is the database perhaps stored in a "protected" directory?
Any errors on execution of the BEGIN and COMMIT statements?
--
Groet, Cordialement, Pozdrawiam, Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Delete From Not Working Properly
Hello,
Yesterday, while working on my game, I noticed that my game data was not
updating in my save SQLite3 database. So I started tracking down what was
going on and eventually my search lead me to my dData function.
How this function works is that you pass a table name and a bool, the bool
is just there in case errors happen to notify the player. I did exhaustive
tests yesterday on this block of code and found that it was not functioning
properly, it was not deleting the data from my tables.
I checked to see if sqlite3_step was producing an error but it was sending
back a value of 101 or SQLITE_DONE but the table data remained unchanged.
I also commented out the code I use to replace the deleted data just to
make sure the data retrieval code was not at fault. With just the dData
call and nothing else the data still would not delete from the table.
Here is the code I am using for my dData function:
void Database::dData(string table, bool* bErrors)
{
sqlStr2 = "Delete From " + table;
sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error);
if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
&statement2, 0) == SQLITE_OK)
{
sqlite3_step(statement2);
*bErrors = false;
finalize(statement2, bErrors);
}
else
{
*bErrors = true;
createBInfo();
d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale +
to_string(__LINE__),bTDate,"./SC_Log.txt");
}
sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error);
}
I am also going to link to my save database in case it itself is to blame
although I tested this on several different files with the same results.
http://sc.lmpgames.com/scSave.sqlite
I already have posted about this on StackOverflow and so far everyone that
has attempted to figure this out has been stumped. I am using Microsoft
Visual Studio 2012 and C++.
Thanks.
--
"I though what I'd do was, I'd pretend I was one of those Deaf-Mutes...Or
should I?" -- Laughing Man
__
Laughing Man Productions² Entertainment and Gaming
Network
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working -- FIXED
On Thu, 8 Oct 2009, Rich Shepard wrote: > Oh, rats! I messed up the table when converting the dates. Guess I need > to start the process over. Will report results when I'm done. Amazing! When the dates are correct, and not all the same, the delete statement works as intended on the table. Thanks, all! Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Well, do you actually have rows that you believe should satisfy the > condition? Show one of those. Oh, rats! I messed up the table when converting the dates. Guess I need to start the process over. Will report results when I'm done. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
Rich Shepard wrote: > On Thu, 8 Oct 2009, Igor Tandetnik wrote: > >> Show what the data looks like now, show the statement you are >> running, and define "not working". > > Igor, > > Here are two records: > > sqlite> select * from Penalties limit 2; > 2009-071|Water Quality, Storm Water|NWR|205 Auto Salvage, > Inc.|Portland|Belete|smith|CP|Failing To Collect Monitoring Data > Required In Schedule B of The Permit|2009-05-07| > 2004-159|Water Quality, Storm Water|NWR|205 Auto Salvage, > Inc.|Portland|Jurries|Camilleri|CPDO|Any Violation Related To Water > Quality Which Is Not Otherwise Classified In These > Rules.|2005-02-14|1335 > > The statement I'm running, and the lack of response is shown by > these statements: > > sqlite> select count(*) from Penalties; > 1477 > sqlite> delete from Penalties where DateIssued < '2005-01-01'; > sqlite> select count(*) from Penalties; > 1477 > > There are the same number of records after I run the delete > statement because none were deleted. Trying a select using the same > where clause results in no rows returned. Well, do you actually have rows that you believe should satisfy the condition? Show one of those. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Show what the data looks like now, show the statement you are running, and > define "not working". Igor, Here are two records: sqlite> select * from Penalties limit 2; 2009-071|Water Quality, Storm Water|NWR|205 Auto Salvage, Inc.|Portland|Belete|smith|CP|Failing To Collect Monitoring Data Required In Schedule B of The Permit|2009-05-07| 2004-159|Water Quality, Storm Water|NWR|205 Auto Salvage, Inc.|Portland|Jurries|Camilleri|CPDO|Any Violation Related To Water Quality Which Is Not Otherwise Classified In These Rules.|2005-02-14|1335 The statement I'm running, and the lack of response is shown by these statements: sqlite> select count(*) from Penalties; 1477 sqlite> delete from Penalties where DateIssued < '2005-01-01'; sqlite> select count(*) from Penalties; 1477 There are the same number of records after I run the delete statement because none were deleted. Trying a select using the same where clause results in no rows returned. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
Rich Shepard wrote: > I changed the data type in the schema to VARCHAR and the format is > now -MM-DD. However, it's still not working. What am I still doing > incorrectly now? Show what the data looks like now, show the statement you are running, and define "not working". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On Thu, 8 Oct 2009, Pavel Ivanov wrote: > Your dates are compared as simple strings. Thus with your statement you're > trying to delete all rows where DateIssued is January, 1 of any year > earlier than 2005. Bottom line: change the way you store your dates if you > really want to compare them in sql statements. Pavel, I changed the data type in the schema to VARCHAR and the format is now -MM-DD. However, it's still not working. What am I still doing incorrectly now? Thanks, Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
Simon Slavin wrote: > On 8 Oct 2009, at 9:35pm, Rich Shepard wrote: > >> A closer look tells me that the string format is incorrect for >> SQL. It >> needs to be -MM-DD rather than D/M/. That incorrect format >> seems to >> be the problem. > > It can be anything which sorts into the correct order when seen as a > string. So > > MMDD > /MM/DD > MMDDHHMMSS > > will all work. As long as you pick one and keep to it. But note that SQLite built-in date/time functions handle some of these formats better than others. See http://www.sqlite.org/lang_datefunc.html . This only matters if you need to perform computations on your dates that go beyond simple comparisons. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On Thu, 8 Oct 2009, Igor Tandetnik wrote: > Actually, columns with these declared types will have NUMERIC affinity. Thanks, Igor. I missed that. > Realize that -MM-DD format works not because SQLite treats it somehow > specially, but because for strings in this format, alphabetical order just > happens to match calendar order or dates. But yes, if you store your dates > as -MM-DD strings, most comparisons will just work. I converted those date formats in a spreadsheet. As soon as I finish cleaning the exported .csv for SQLite I'll be able to delete those rows. Thanks, Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
Rich Shepard wrote: > On Thu, 8 Oct 2009, Pavel Ivanov wrote: > >> And the main problem: SQLite doesn't have such type as date. All >> types it supports are listed here: >> http://www.sqlite.org/datatype3.html. > > We can use DATE, TIME, and DATETIME column types; they all have TEXT > storage class. Actually, columns with these declared types will have NUMERIC affinity. Storage class is a property of each individual value, not of a column as a whole: values in different rows of the same column may have different storage classes. >> Bottom line: change the way you store your dates if you really want >> to compare them in sql statements. > > A closer look tells me that the string format is incorrect for SQL. > It needs to be -MM-DD rather than D/M/. That incorrect format > seems to be the problem. Realize that -MM-DD format works not because SQLite treats it somehow specially, but because for strings in this format, alphabetical order just happens to match calendar order or dates. But yes, if you store your dates as -MM-DD strings, most comparisons will just work. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On 8 Oct 2009, at 9:35pm, Rich Shepard wrote: > A closer look tells me that the string format is incorrect for > SQL. It > needs to be -MM-DD rather than D/M/. That incorrect format > seems to > be the problem. It can be anything which sorts into the correct order when seen as a string. So MMDD /MM/DD MMDDHHMMSS will all work. As long as you pick one and keep to it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
On Thu, 8 Oct 2009, Pavel Ivanov wrote: > And the main problem: SQLite doesn't have such type as date. All types > it supports are listed here: http://www.sqlite.org/datatype3.html. Pavel, We can use DATE, TIME, and DATETIME column types; they all have TEXT storage class. > Bottom line: change the way you store your dates if you really want to > compare them in sql statements. A closer look tells me that the string format is incorrect for SQL. It needs to be -MM-DD rather than D/M/. That incorrect format seems to be the problem. Thanks, Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE FROM Not Working
> To delete all rows with dates earlier than 1/1/2005 I used the statement: > > DELETE FROM Penalties WHERE DateIssued < '1/1/2005'; > > but it did not delete the records. Doesn't matter if I use single quotes, > double quotes, or no quotes. Just a note: when you tried it without quotes you tried to delete rows with DateIssued less than 1/2005 = 0.000498... And the main problem: SQLite doesn't have such type as date. All types it supports are listed here: http://www.sqlite.org/datatype3.html. Your dates are compared as simple strings. Thus with your statement you're trying to delete all rows where DateIssued is January, 1 of any year earlier than 2005. Bottom line: change the way you store your dates if you really want to compare them in sql statements. Pavel On Thu, Oct 8, 2009 at 4:06 PM, Rich Shepard wrote: > This must be my error, but I am not seeing it. Your input is requested. > > I have a table named Penalties with a column named DateIssued and a > datatype of DATE. A select operation shows dates such as 4/6/1992 and > 12/15/1993. > > To delete all rows with dates earlier than 1/1/2005 I used the statement: > > DELETE FROM Penalties WHERE DateIssued < '1/1/2005'; > > but it did not delete the records. Doesn't matter if I use single quotes, > double quotes, or no quotes. > > Trying to figure out where my syntax is faulty I tried selecting those > records, but none are presented. I'm sure it's a simple user error in my > syntax, but I don't see what that error is and I don't find anything in Rick > van der Lans's or Mike Owen's books that's different from what I've tried. > > I'm sure it's a head-slapping, obvious error so please point it out to me. > > Thanks, > > Rich > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE FROM Not Working
This must be my error, but I am not seeing it. Your input is requested. I have a table named Penalties with a column named DateIssued and a datatype of DATE. A select operation shows dates such as 4/6/1992 and 12/15/1993. To delete all rows with dates earlier than 1/1/2005 I used the statement: DELETE FROM Penalties WHERE DateIssued < '1/1/2005'; but it did not delete the records. Doesn't matter if I use single quotes, double quotes, or no quotes. Trying to figure out where my syntax is faulty I tried selecting those records, but none are presented. I'm sure it's a simple user error in my syntax, but I don't see what that error is and I don't find anything in Rick van der Lans's or Mike Owen's books that's different from what I've tried. I'm sure it's a head-slapping, obvious error so please point it out to me. Thanks, Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
