Re: [sqlite] Check if file exists in SQL syntax?
Thanks Keith!
2014-10-11 2:01 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> You do not say what operating system, but for example, on Windows, the
> following function works:
>
> SQLITE_PRIVATE void _GetFileAttributes(sqlite3_context *context, int argc,
> sqlite3_value **argv)
> {
> sqlite3_result_int(context,
> GetFileAttributesW(sqlite3_value_text16(argv[0])));
> }
>
> which is then declared to SQLite function interface (for connection db) thus:
>
> nErr += sqlite3_create_function(db, "GetFileAttributes", 1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _GetFileAttributes,0, 0);
>
> Returning the file attributes for the given pathspec, or -1 if it does not
> exist.
>
>
> SQLite version 3.8.7 2014-10-09 15:08:17
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select getfileattributes('D:\');
> 22
> sqlite> select getfileattributes('D:\momo');
> -1
> sqlite> select getfileattributes('D:\source\sqlite\sqlite3s.exe');
> 32
> sqlite> select getfileattributes('D:\source\sqlite');
> 16
> sqlite>
>
> You do similar on Unices just substitute the appropriate call to get the file
> attributes ...
>
>>-Original Message-
>>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] On Behalf Of Krzysztof
>>Sent: Friday, 10 October, 2014 12:21
>>To: General Discussion of SQLite Database
>>Subject: [sqlite] Check if file exists in SQL syntax?
>>
>>Hi,
>>
>>I'm collecting file names (full paths) in sqlite table. Each day I
>>need to remove non existing files from this table (thousands records).
>>For now I'm selecting all records and then checking if file exists
>>using C++ routine and if not then deleting it. I'm wondering if SQLite
>>has such function (could not find it in core functions). For example I
>>would like to call something like:
>>
>>CREATE TABLE my_table {
>> id INTEGER PRIMARY KEY,
>> filename TEXT,
>> tags TEXT
>>}
>>
>>DELETE FROM my_table WHERE NOT FileExists(filename)
>>
>>Regards
>>___
>>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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check if file exists in SQL syntax?
You do not say what operating system, but for example, on Windows, the
following function works:
SQLITE_PRIVATE void _GetFileAttributes(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
sqlite3_result_int(context,
GetFileAttributesW(sqlite3_value_text16(argv[0])));
}
which is then declared to SQLite function interface (for connection db) thus:
nErr += sqlite3_create_function(db, "GetFileAttributes", 1,
SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _GetFileAttributes,0, 0);
Returning the file attributes for the given pathspec, or -1 if it does not
exist.
SQLite version 3.8.7 2014-10-09 15:08:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select getfileattributes('D:\');
22
sqlite> select getfileattributes('D:\momo');
-1
sqlite> select getfileattributes('D:\source\sqlite\sqlite3s.exe');
32
sqlite> select getfileattributes('D:\source\sqlite');
16
sqlite>
You do similar on Unices just substitute the appropriate call to get the file
attributes ...
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Krzysztof
>Sent: Friday, 10 October, 2014 12:21
>To: General Discussion of SQLite Database
>Subject: [sqlite] Check if file exists in SQL syntax?
>
>Hi,
>
>I'm collecting file names (full paths) in sqlite table. Each day I
>need to remove non existing files from this table (thousands records).
>For now I'm selecting all records and then checking if file exists
>using C++ routine and if not then deleting it. I'm wondering if SQLite
>has such function (could not find it in core functions). For example I
>would like to call something like:
>
>CREATE TABLE my_table {
> id INTEGER PRIMARY KEY,
> filename TEXT,
> tags TEXT
>}
>
>DELETE FROM my_table WHERE NOT FileExists(filename)
>
>Regards
>___
>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
Re: [sqlite] Check if file exists in SQL syntax?
On 10/10/2014 2:20 PM, Krzysztof wrote: I'm collecting file names (full paths) in sqlite table. Each day I need to remove non existing files from this table (thousands records). I'm wondering if SQLite has such function (could not find it in core functions). SQLite doesn't have such a function - but it provides a way for you to create your own custom functions. So you can write one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check if file exists in SQL syntax?
Hi,
I'm collecting file names (full paths) in sqlite table. Each day I
need to remove non existing files from this table (thousands records).
For now I'm selecting all records and then checking if file exists
using C++ routine and if not then deleting it. I'm wondering if SQLite
has such function (could not find it in core functions). For example I
would like to call something like:
CREATE TABLE my_table {
id INTEGER PRIMARY KEY,
filename TEXT,
tags TEXT
}
DELETE FROM my_table WHERE NOT FileExists(filename)
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
