[sqlite] Characters corrupt after importing a CSV file

[James K. Lowden]

On Thu, 26 Feb 2015 10:58:30 +
Adam Podstawczy?ski  wrote:

> While this solves the issue for me, I still believe this behavior is
> confusing ? truncated characters should be handled more gracefully.

To your point, I think the column and column-width features of the
shell could benefit from some re-thinking.  

My ~/.sqliterc includes these lines:

.mode column
.header ON

and truncated strings and numbers are a source of confusion and error
even though I know how they work.  

I wish that by default column formatting would be "soft" in the sense
that printf(1) and printf(3) define: digits to the left of the decimal
are never truncated, and strings are not truncated unless explicitly
requested:  

$ printf '%5.4f\n' 123456789.123456789
123456789.1235
$ printf '%5.4s\n' 123456789.123456789
 1234

This would make the output "snakey" on the screen sometimes, but would
never conceal important information.  

I would change the width specification to accept the width and
precision notation used by printf.  The statement 

.width 8

would mean "set the minimum column width to 8", whereas

.width  8.8  

would mean "set the minumum width to 8, minimum precision to 8, and
truncate strings to 8".  I wouldn't worry about columns that mix
strings and numbers; funny-looking strings in that case should be
expected.  And, besides, don't do that.  

It would also be nice to have a mode ".autowidth" that set column
widths based on, say, the first 10 lines (and, preferably, their
labels) in a manner similar to column(1).  Provided it never truncated
anything, I would use that more often than an improved .width.

Thoughts?

--jkl

[sqlite] problem compiling std_call dll

[James K. Lowden]

On Sat, 28 Feb 2015 11:22:12 +
Bart Smissaert  wrote:

> It doesn't compile and gives linking errors:
> 
> Error 1 error LNK2001: unresolved external symbol
> _sqlite3_db_filename
> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall
> \SQLite3_StdCall.obj SQLite3_StdCall
> Error 2 error LNK1120: 1 unresolved
> externals C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release
> \SQLite3_StdCall.dll 1 1 SQLite3_StdCall

What you mean is, it *does* compile, and then the linker emits an
error.  :-)

> Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API
> but points to line 1.

Not strange, or anyway not too strange.  The linker doesn't know from
source code.  It only knows the object code references a symbol for
which it found no definition.  

The symbol you want, sqlite3_db_filename appears to be defined in
sqlite3ext.h, line 475:

 #define sqlite3_db_filename sqlite3_api->db_filename

If you include that file, the linker shouldn't attempt to resolve
the symbol, because the preprocessor defines it away.  

HTH.  

--jkl

[sqlite] problem compiling std_call dll

[Scott Robison]

On Sun, Mar 1, 2015 at 10:55 PM, J Decker  wrote:

> On Sun, Mar 1, 2015 at 9:22 PM, Keith Medcalf  wrote:
>
> > >I do not know if this is the case, but typically Windows creates names
> > >like
> > >_sqlite3_db_filename at X (where X is a number) if a function is defined
> as
> > >stdcall. It doesn't *have* to do this, but that's the convention used by
> >
> > the @X is used to include the ordinal reference (@X) in the
> symbol
> > since you can link by either name or ordinal (or, in the case of
> specially
> > constructed libraries, by both having to match).
>
> 1) the @X is the size of paramters pushed on the stack.
> 2) (something else in another mail that irked me)  It's not a 'default
> calling convention for windows' it's a default calling convention for some
> compilers; and doesn't have be even be on windows.
>

1. I didn't see you'd already explained the @X meaning. Sorry for
duplicating your answer.
2. Just in case you meant me, I was not trying to say it is the default
calling convention for windows. I was saying that appending "@X" to the
symbol is the default convention used by (at least) Microsoft compilers
when using the stdcall calling convention. It's a confusing bit of
terminology what with multiple applications of the word "convention" so I
just wanted to clarify what I meant. I understand Windows does not require
everyone to use stdcall for their own APIs and such.

-- 
Scott Robison

[sqlite] problem compiling std_call dll

[Scott Robison]

On Sun, Mar 1, 2015 at 10:22 PM, Keith Medcalf  wrote:

> >I do not know if this is the case, but typically Windows creates names
> >like
> >_sqlite3_db_filename at X (where X is a number) if a function is defined as
> >stdcall. It doesn't *have* to do this, but that's the convention used by
>
> the @X is used to include the ordinal reference (@X) in the symbol
> since you can link by either name or ordinal (or, in the case of specially
> constructed libraries, by both having to match).  This is not, however, the
> "normal" case.  The normal case is to export symbols only and link the
> modules at load time by name.  This syntax is used specifically so that
> when you add a new entry ordinal to a dll, it does not get inserted into
> the name table in default order, but rather that symbols keep their
> previous version ordinals so that intra-version trampolines contained in
> link libraries will continue to work between different versions of the load
> library.
>

It's the size of the data pushed onto the stack by the caller and is used
by the callee to clean up the stack. Not unlike the pascal calling
convention. See
https://en.wikipedia.org/wiki/X86_calling_conventions#stdcall ... In any
case, the @X doesn't *need* to be part of the symbol (the Windows API entry
points don't use it, even though they use stdcall as I understand it) as
the cleanup code isn't dependent on the symbol. It's just a convention.

-- 
Scott Robison

[sqlite] problem compiling std_call dll

[Keith Medcalf]

>I do not know if this is the case, but typically Windows creates names
>like
>_sqlite3_db_filename at X (where X is a number) if a function is defined as
>stdcall. It doesn't *have* to do this, but that's the convention used by

the @X is used to include the ordinal reference (@X) in the symbol 
since you can link by either name or ordinal (or, in the case of specially 
constructed libraries, by both having to match).  This is not, however, the 
"normal" case.  The normal case is to export symbols only and link the modules 
at load time by name.  This syntax is used specifically so that when you add a 
new entry ordinal to a dll, it does not get inserted into the name table in 
default order, but rather that symbols keep their previous version ordinals so 
that intra-version trampolines contained in link libraries will continue to 
work between different versions of the load library.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

[sqlite] Does length() use strlen() on blobs?

[David Barrett]

Thank you everybody; casing to BLOB on insert is the solution I was looking
for.  This will ensure length() returns the number of bytes, which is
exactly what I want.  Thanks!

-david

On Sun, Mar 1, 2015 at 1:05 PM, Keith Medcalf  wrote:

>
> length() on a blob returns the length of the blob without examining the
> blob itself.  The length is encoded in the data structure.
> length() on text returns the number of characters in the text.  This
> requires retrieving the data and counting.
>
> Whether sqlite stores a blob or text in the column depends on what you
> pass it.  For example:
>
> sqlite> create table x(x blob);
> sqlite> insert into x values ('x');
> sqlite> insert into x values (cast('x' as blob));
> sqlite> select * from x;
> x
> x
> sqlite> select typeof(x) from x;
> text
> blob
>
> So, if you insert text into a column, text is stored.  If you insert a
> blob into a column, a blob is stored.  Column affinity is (as in all cases)
> practically irrlevant.  What you put will depend on how you put it (if
> inline as in the above example), or whether you use bind_blob or bind_text
> when storing the data -- just as what you get out depends on the type you
> ask for when retrieving the data.
>
> ---
> Theory is when you know everything but nothing works.  Practice is when
> everything works but no one knows why.  Sometimes theory and practice are
> combined:  nothing works and no one knows why.
>
>
> >-Original Message-
> >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> >bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
> >Sent: Sunday, 1 March, 2015 11:41
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Does length() use strlen() on blobs?
> >
> >Hi!  If I define a column as BLOB type, will length() run strlen() on the
> >data, or will it consult some kind of internal value to determine the
> >length?  The documentation says:
> >
> >"For a string value X, the length(X) function returns the number of
> >characters (not bytes) in X prior to the first NUL character.  ... For a
> >blob value X, length(X) returns the number of bytes in the blob."
> >
> >This suggests to me it uses some internal length value (eg, not strlen),
> >but given that sqlite doesn't use strong typing, I'm wondering if I
> >insert
> >a string into that blob if it'll return the blob length or the string
> >length.
> >
> >I ask because I'm going to be using sqlite on a performance-sensitive
> >application to store small strings (as blobs), and one of the most
> >frequent
> >queries will be to get the length of the blob.  I'm wondering if I should
> >create a separate length column, or if I should just use length().
> >
> >Thanks!
> >
> >-david
> >___
> >sqlite-users mailing list
> >sqlite-users at mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] problem compiling std_call dll

[J Decker]

On Sun, Mar 1, 2015 at 9:22 PM, Keith Medcalf  wrote:

> >I do not know if this is the case, but typically Windows creates names
> >like
> >_sqlite3_db_filename at X (where X is a number) if a function is defined as
> >stdcall. It doesn't *have* to do this, but that's the convention used by
>
> the @X is used to include the ordinal reference (@X) in the symbol
> since you can link by either name or ordinal (or, in the case of specially
> constructed libraries, by both having to match).


1) the @X is the size of paramters pushed on the stack.
2) (something else in another mail that irked me)  It's not a 'default
calling convention for windows' it's a default calling convention for some
compilers; and doesn't have be even be on windows.

there are usually command line options to set default calling conventions
to fix it; and it should be well defined in the header so long as both
sources includes the same prototypes and have the same settings it should
work.

This is not, however, the "normal" case.  The normal case is to export
> symbols only and link the modules at load time by name.  This syntax is
> used specifically so that when you add a new entry ordinal to a dll, it
> does not get inserted into the name table in default order, but rather that
> symbols keep their previous version ordinals so that intra-version
> trampolines contained in link libraries will continue to work between
> different versions of the load library.
>
> ---
> Theory is when you know everything but nothing works.  Practice is when
> everything works but no one knows why.  Sometimes theory and practice are
> combined:  nothing works and no one knows why.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Does length() use strlen() on blobs?

[R.Smith]

On 2015-03-01 08:40 PM, David Barrett wrote:
> Hi!  If I define a column as BLOB type, will length() run strlen() on the
> data, or will it consult some kind of internal value to determine the
> length?  The documentation says:
>
> "For a string value X, the length(X) function returns the number of
> characters (not bytes) in X prior to the first NUL character.  ... For a
> blob value X, length(X) returns the number of bytes in the blob."
>
> This suggests to me it uses some internal length value (eg, not strlen),
> but given that sqlite doesn't use strong typing, I'm wondering if I insert
> a string into that blob if it'll return the blob length or the string
> length.

I believe it is StrLen() that is the more complex because it needs to 
consider multi-byte character sets (Unicode etc.) as opposed to just 
straight byte-length. SQLite knows the byte-size of any stored value, 
this is imperative to the way it stores data and querying it is rather 
quick (though I do not know of any benchmarks having tested it 
specifically, but you are welcome to devise a test bed for it).

If it is really performance-sensitive, or some tests reveal the length 
function being a tad sluggish, you might opt to simply add another 
column to store the byte/string/whatever lengths you wish and use the 
normal column value in your select to fetch it (as opposed to a 
function) - that will most certainly be very fast when fetching values, 
but it might add a few cycles to the inserts/updates.

Also, conventional wisdom dictates storing strings as strings, I am very 
sure it is at least as fast as storing blobs. Internally, SQLite writes 
bytes, whether they are from strings or blobs, not much difference. 
Significant time was spent by the SQLite devs to make sure strings go in 
and out of the DB very efficiently. You will score a few cycles if you 
can live without UTF8, but not much. Please do devise some speed tests 
though, I believe you won't find a significant performance difference 
but it's worth checking if your application needs every drop of speed - 
just to be sure (Plus we'd be interested in the results if you do find 
significant differences).

[sqlite] problem compiling std_call dll

[Scott Robison]

On Sun, Mar 1, 2015 at 9:28 PM, James K. Lowden 
wrote:

> On Sat, 28 Feb 2015 11:22:12 +
> Bart Smissaert  wrote:
>
> > It doesn't compile and gives linking errors:
> >
> > Error 1 error LNK2001: unresolved external symbol
> > _sqlite3_db_filename
> > C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall
> > \SQLite3_StdCall.obj SQLite3_StdCall
> > Error 2 error LNK1120: 1 unresolved
> > externals C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release
> > \SQLite3_StdCall.dll 1 1 SQLite3_StdCall
>
> What you mean is, it *does* compile, and then the linker emits an
> error.  :-)
>
> > Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API
> > but points to line 1.
>
> Not strange, or anyway not too strange.  The linker doesn't know from
> source code.  It only knows the object code references a symbol for
> which it found no definition.
>
> The symbol you want, sqlite3_db_filename appears to be defined in
> sqlite3ext.h, line 475:
>
>  #define sqlite3_db_filename sqlite3_api->db_filename
>
> If you include that file, the linker shouldn't attempt to resolve
> the symbol, because the preprocessor defines it away.
>

I do not know if this is the case, but typically Windows creates names like
_sqlite3_db_filename at X (where X is a number) if a function is defined as
stdcall. It doesn't *have* to do this, but that's the convention used by
default. The fact that the error message is _sqlite3_db_filename instead of
_sqlite3_db_filename at X makes me suspect that there is a prototype somewhere
for the function that doesn't include stdcall. Perhaps the header and
source files are out of sync? If that were the case, the @X version of the
symbol might be in the obj file (the source file got stdcall right), while
consumers using the header try to use the cdecl form without the @X
(because the prototype is different in the two locations.

I hope this isn't too rambling. Just thoughts from a sleep deprived person.
:)

-- 
Scott Robison

[sqlite] Sqlite and threads/multiple applications

[R.Smith]

On 2015-03-01 11:22 AM, Olivier Vidal wrote:
> ok, thank you Simon and Keith!
> other people have experience with multiple applications/one sqlite 
> database?
>
> looking at this list , I 
> wonder if there is an operating system to favour with Sqlite. I saw 
> that there could be problems with older versions of Windows or Linux. 
> But take the example of a web server, with as the latest version of 
> Windows Server 2012 or the latest version of Ubuntu. What would be the 
> most reliable operating system for Sqlite(last version)?

Almost everyone on this list has some multi-access DB systems running at 
their respective places of work - they don't all speak up because it's 
kind of the standard thing, but if you pose a question about a specific 
problem, you will find lots and lots of people here have already 
experienced the thing you are struggling with. SQLite works exceedingly 
well as a multi-application database, so long as it is not a networked 
solution (as others have mentioned).

SQLite is not at all a consideration factor when choosing the operating 
system - it works as advertised on all of them. Picking an operating 
system some basic guidelines (though not absolute deciders) might be:
   - If it will be a very user-centric system or needs to be aesthetic 
for some reason (like marketing pods), consider Mac OSX,
   - If it needs to do lots of other things too and have non-technical 
users, maybe Windows will suit best,
   - If it's more of a server-type setup or needs a lot of autonomy 
and/or mainly admin personnel accessing it, Linux might be best,
   - If it needs to run on phones or devices, obviously whatever OSes 
the target market requires.

Another argument may go: Pick whatever OS you have the most experience 
on and suits your financial model - there is nothing precluding Windows 
from being a service machine or precluding Linux from doing a pretty 
interface, etc. and SQLite works well on them all.

[sqlite] Does length() use strlen() on blobs?

[Simon Slavin]

On 1 Mar 2015, at 6:40pm, David Barrett  wrote:

> I'm wondering if I insert
> a string into that blob if it'll return the blob length or the string
> length.

You can put a value of any type into a column of any affinity.  i.e. you can 
put a BLOB into a BLOB column or a string into a BLOB column.  What the 
length() function does depends on what type the value has, not what type the 
column has.

> I ask because I'm going to be using sqlite on a performance-sensitive
> application to store small strings (as blobs)

Why aren't you storing strings in string columns ?  Internally to SQLite, 
string values and BLOB values are handled almost identically.

Simon.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Richard Hipp]

On 3/1/15, Paolo Bolzoni  wrote:
> Dear everyone,
>
> I find strange and confusing that bind indexes start from 1 (docs in
> [1]) and instead column indexes start from 0 (doc in [2]). Is there
> any technical reason or it is just an unlucky legacy?

Seems like there was a reason for this, 11 years ago when it went in,
but I cannot now call that reason to mind right this moment.  So lets
just call it unlucky legacy.

>
> Yours faithfully,
> Paolo
>
>
> [1] https://www.sqlite.org/c3ref/bind_blob.html
> [2] https://www.sqlite.org/c3ref/column_blob.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[R.Smith]

On 2015-03-01 04:41 PM, Paolo Bolzoni wrote:
> Dear everyone,
>
> I find strange and confusing that bind indexes start from 1 (docs in
> [1]) and instead column indexes start from 0 (doc in [2]). Is there
> any technical reason or it is just an unlucky legacy?

I'm going to venture a guess here and go with "Unlucky Legacy", but it 
is not really confusing I think. Please note that column indices start 
from 1 too when you refer a column or field in the TCL/SQL - a design 
that might stem from the SQL standard. I observed the same in MySQL or 
MSSQL etc. (refer ELEMENT() or similar functions) - Only the SQLite API 
refers to the first column/Index as 0, but the API is a programming API 
and not a TCL and follows the base way all programming constructs (at 
least those I am familiar with) such as array indexing, bytes, pointers 
etc. do it.  Where the API needs to refer to SQL/TCL constructs, such as 
in your example [1], the lines get blurry and the API adapts (which is 
far better than having the SQL adapt).

I'm sure if we had the opportunity to redesign the SQL standard - it 
might look somewhat different.

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Jay Kreibich]

On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:

> On 3/1/15, Paolo Bolzoni  wrote:
>> Dear everyone,
>> 
>> I find strange and confusing that bind indexes start from 1 (docs in
>> [1]) and instead column indexes start from 0 (doc in [2]). Is there
>> any technical reason or it is just an unlucky legacy?
> 
> Seems like there was a reason for this, 11 years ago when it went in,
> but I cannot now call that reason to mind right this moment.  So lets
> just call it unlucky legacy.

Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
sure it is part of the SQL standard, but it is more or less the defacto 
standard of SQL APIs, and might be considered part of the SQL language.

I assume column indexes start at 0 because of C.  Column indexes are used 
within the context of the C language API, so it makes some sense to use C 
conventions.


I know they?re very easy to confuse (I still do it myself), but I can kind of 
see why we ended up there, even if it isn?t a very strong reason.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

Dear everyone,

I find strange and confusing that bind indexes start from 1 (docs in
[1]) and instead column indexes start from 0 (doc in [2]). Is there
any technical reason or it is just an unlucky legacy?

Yours faithfully,
Paolo


[1] https://www.sqlite.org/c3ref/bind_blob.html
[2] https://www.sqlite.org/c3ref/column_blob.html

[sqlite] Sqlite and threads/multiple applications

[Simon Slavin]

On 1 Mar 2015, at 9:22am, Olivier Vidal  wrote:

> looking at this list , I wonder if 
> there is an operating system to favour with Sqlite.

Operating systems aren't a problem.  Chose whatever OS you like based on other 
considerations.  The same is true of the popular disk formats.  If SQLite works 
with one at all, it'll work properly.  The problem is with network filing 
systems (SMB, CIC, AFP, NFS), none of which seem to support locks properly.

Simon.

[sqlite] Does length() use strlen() on blobs?

[Keith Medcalf]

length() on a blob returns the length of the blob without examining the blob 
itself.  The length is encoded in the data structure.
length() on text returns the number of characters in the text.  This requires 
retrieving the data and counting.

Whether sqlite stores a blob or text in the column depends on what you pass it. 
 For example:

sqlite> create table x(x blob);
sqlite> insert into x values ('x');
sqlite> insert into x values (cast('x' as blob));
sqlite> select * from x;
x
x
sqlite> select typeof(x) from x;
text
blob

So, if you insert text into a column, text is stored.  If you insert a blob 
into a column, a blob is stored.  Column affinity is (as in all cases) 
practically irrlevant.  What you put will depend on how you put it (if inline 
as in the above example), or whether you use bind_blob or bind_text when 
storing the data -- just as what you get out depends on the type you ask for 
when retrieving the data.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
>Sent: Sunday, 1 March, 2015 11:41
>To: General Discussion of SQLite Database
>Subject: [sqlite] Does length() use strlen() on blobs?
>
>Hi!  If I define a column as BLOB type, will length() run strlen() on the
>data, or will it consult some kind of internal value to determine the
>length?  The documentation says:
>
>"For a string value X, the length(X) function returns the number of
>characters (not bytes) in X prior to the first NUL character.  ... For a
>blob value X, length(X) returns the number of bytes in the blob."
>
>This suggests to me it uses some internal length value (eg, not strlen),
>but given that sqlite doesn't use strong typing, I'm wondering if I
>insert
>a string into that blob if it'll return the blob length or the string
>length.
>
>I ask because I'm going to be using sqlite on a performance-sensitive
>application to store small strings (as blobs), and one of the most
>frequent
>queries will be to get the length of the blob.  I'm wondering if I should
>create a separate length column, or if I should just use length().
>
>Thanks!
>
>-david
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does length() use strlen() on blobs?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/01/2015 10:40 AM, David Barrett wrote:
> Hi!  If I define a column as BLOB type, will length() run strlen()
> on the data, or will it consult some kind of internal value to
> determine the length?

sqlite> select cast(x'424446' as text);
BDF
sqlite> select length(cast(x'4244460048' as text));
3
sqlite> select length(cast(x'4244460048' as blob));
5

Length of blob is number of bytes.  Length of text is number of
characters until first null byte.  I'll leave it up to you to figure
out what happens with multibyte characters.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTzeyYACgkQmOOfHg372QSp5QCdEwDFmFfCcwEgR0J7D5uUzlAq
J1gAniGY88fNty/pv7qq2zUZ5RWxZKZs
=qUxe
-END PGP SIGNATURE-

[sqlite] Does length() use strlen() on blobs?

[David Barrett]

Hi!  If I define a column as BLOB type, will length() run strlen() on the
data, or will it consult some kind of internal value to determine the
length?  The documentation says:

"For a string value X, the length(X) function returns the number of
characters (not bytes) in X prior to the first NUL character.  ... For a
blob value X, length(X) returns the number of bytes in the blob."

This suggests to me it uses some internal length value (eg, not strlen),
but given that sqlite doesn't use strong typing, I'm wondering if I insert
a string into that blob if it'll return the blob length or the string
length.

I ask because I'm going to be using sqlite on a performance-sensitive
application to store small strings (as blobs), and one of the most frequent
queries will be to get the length of the blob.  I'm wondering if I should
create a separate length column, or if I should just use length().

Thanks!

-david

[sqlite] Sqlite and threads/multiple applications

[Olivier Vidal]

ok, thank you Simon and Keith!
other people have experience with multiple applications/one sqlite database?

looking at this list , I 
wonder if there is an operating system to favour with Sqlite. I saw that 
there could be problems with older versions of Windows or Linux. But 
take the example of a web server, with as the latest version of Windows 
Server 2012 or the latest version of Ubuntu. What would be the most 
reliable operating system for Sqlite(last version)?

olivier

> Keith Medcalf 
> 1 mars 2015 00:17
>
> There will be only one *connection* that can write at a time. This 
> will correspond to a single process, but not necessarily a single 
> thread within that process. Mulithreaded access on a connection must 
> be serialized (or will be automatically, unless you defeat the 
> automatic thread seialization), but the transaction applies to the 
> connection and is not linked to the processID or threadID.
>
> ---
> Theory is when you know everything but nothing works. Practice is when 
> everything works but no one knows why. Sometimes theory and practice 
> are combined: nothing works and no one knows why.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> Olivier Vidal 
> 28 f?vrier 2015 20:02
> Hello all,
>
> According to this paragraph http://www.sqlite.org/faq.html#q5, 
> multiple applications or multiple instances of the same application 
> can access the *same* database at the *same time*. Even in WAL mode?
>
> If I have understood correctly, all applications will be able to read 
> the sqlite database at the same time, but there will be only one 
> thread (one thread of one application) who will write at the same time 
> (in WAL mode).
>
> According to your experience, it is reliable? Serialization is still 
> correctly performed through applications?
> There are precautions to take?
>
> Thank you!
>
> Olivier
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem compiling std_call dll

[Bart Smissaert]

Iooked at that underscore (in _sqlite3_db_filename) as well, but that is
just how VS presents the error, it didn't find a faulty underscore in the
code. So, that is not it. As far as I can see the SQLite3 functions in this
project are only in 3 places: sqlite3.h, SQLite3_StdCall.c and in the .def
file. sqlite3_db_filename is in all 3 and all seems to be spelled right.

Not sure if it gives any more information, but this is the build output:

1>-- Build started: Project: SQLite3_StdCall, Configuration: Release
Win32 --

1> SQLite3_StdCall.c

1> Creating library
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.lib
and object
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.exp

1>SQLite3_StdCall.obj : error LNK2001: unresolved external symbol
_sqlite3_db_filename

1>C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
: fatal error LNK1120: 1 unresolved externals

== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==



RBS

On Sun, Mar 1, 2015 at 7:10 AM, Amit Chaudhuri 
wrote:

> Bart,
>
> you say "don't know anything about C or C++ or compiling C code"
> Perhaps this will help.
>
> The error message (unresolved) external means that the linker is
> trying to find something (_sqlite3_db_filename in this case) but it
> cannot.
>
> Possible common causes include - it has been misspelt (I'm looking at
> that underscore) or you have failed to point it at a library.
>
> I'm afraid I'm not fluent in the windows tools to track this down and
> don't have a system I can follow along with.  But on Linux one might
> use the nm utility to examine a contents of library files for symbols.
>
> Regards,
>
> Amit
>
> On Sat, Feb 28, 2015 at 11:22 AM, Bart Smissaert
>  wrote:
> > Trying to compile a std_call dll to be used with VB6 and VBA.
> > Using instructions and files from this website:
> > https://sqliteforexcel.codeplex.com/
> > All works well and have added a few SQLite functions that weren't in the
> > compiled dll
> > as in the download section from that website.
> > However having problems adding sqlite3_db_filename.
> >
> > I added this to sqlite3.h:
> >
> > const char *sqlite3_db_filename(sqlite3 *db, const char *zDbName);
> >
> > and this to SQLite3_StdCall.c:
> >
> > SQLITE3_STDCALL_API const char * __stdcall
> > sqlite3_stdcall_db_filename(sqlite3 *pDb, const char *zDbName)
> > {
> >  return sqlite3_db_filename(pDb, zDbName);
> > }
> >
> > It doesn't compile and gives linking errors:
> >
> > Error 1 error LNK2001: unresolved external symbol
> > _sqlite3_db_filename
> > C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\SQLite3_StdCall.obj
> > SQLite3_StdCall
> > Error 2 error LNK1120: 1 unresolved
> > externals
> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
> > 1 1 SQLite3_StdCall
> >
> > Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API but
> > points to line 1.
> > This is at the start of SQLite3_StdCall.c:
> >
> > #include "stdafx.h"
> > #include "SQLite3_StdCall.h"
> >
> > Compiling this on Windows 7 with Microsoft Visual Studio Professional
> > Studio 2013.
> > Admittedly, don't know anything about C or C++ or compiling C code, so I
> am
> > sure I must
> > be overlooking something simple and silly, but just can't figure it out.
> > Any ideas what could be the problem here?
> >
> >
> > RBS
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] problem compiling std_call dll

[Teg]

Hello Bart,

dumpbin /all sqlite3.obj > sqlite.txt

You can then look at the sqlite.txt file with a text editor. I found
_sqlite3_db_filename listed in there a couple times. That tells you it
was probably compiled in. If you can't find it then that could be part
of the problem.

Then I might dump the obj for the Stdcall file and see how it mentions
the  function  too.  I have no idea how long this function has been in
Sqlite.  It makes me wonder if you're using an older version where the
function didn't exist.

As  an  experiment,  I'd  probably  comment  out  the function in the
stdcall  wrapper  that calls _sqlite3_db_filename then rebuild. See if
the error goes away.


Sunday, March 1, 2015, 5:15:32 AM, you wrote:

BS> Iooked at that underscore (in _sqlite3_db_filename) as well, but that is
BS> just how VS presents the error, it didn't find a faulty underscore in the
BS> code. So, that is not it. As far as I can see the SQLite3 functions in this
BS> project are only in 3 places: sqlite3.h, SQLite3_StdCall.c and in the .def
BS> file. sqlite3_db_filename is in all 3 and all seems to be spelled right.

BS> Not sure if it gives any more information, but this is the build output:

1>>-- Build started: Project: SQLite3_StdCall, Configuration: Release
BS> Win32 --

1>> SQLite3_StdCall.c

1>> Creating library
BS> 
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.lib
BS> and object
BS> 
C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.exp

1>>SQLite3_StdCall.obj : error LNK2001: unresolved external symbol
BS> _sqlite3_db_filename

1>>C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
BS> : fatal error LNK1120: 1 unresolved externals

BS> == Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==



BS> RBS

BS> On Sun, Mar 1, 2015 at 7:10 AM, Amit Chaudhuri 
BS> wrote:

>> Bart,
>>
>> you say "don't know anything about C or C++ or compiling C code"
>> Perhaps this will help.
>>
>> The error message (unresolved) external means that the linker is
>> trying to find something (_sqlite3_db_filename in this case) but it
>> cannot.
>>
>> Possible common causes include - it has been misspelt (I'm looking at
>> that underscore) or you have failed to point it at a library.
>>
>> I'm afraid I'm not fluent in the windows tools to track this down and
>> don't have a system I can follow along with.  But on Linux one might
>> use the nm utility to examine a contents of library files for symbols.
>>
>> Regards,
>>
>> Amit
>>
>> On Sat, Feb 28, 2015 at 11:22 AM, Bart Smissaert
>>  wrote:
>> > Trying to compile a std_call dll to be used with VB6 and VBA.
>> > Using instructions and files from this website:
>> > https://sqliteforexcel.codeplex.com/
>> > All works well and have added a few SQLite functions that weren't in the
>> > compiled dll
>> > as in the download section from that website.
>> > However having problems adding sqlite3_db_filename.
>> >
>> > I added this to sqlite3.h:
>> >
>> > const char *sqlite3_db_filename(sqlite3 *db, const char *zDbName);
>> >
>> > and this to SQLite3_StdCall.c:
>> >
>> > SQLITE3_STDCALL_API const char * __stdcall
>> > sqlite3_stdcall_db_filename(sqlite3 *pDb, const char *zDbName)
>> > {
>> >  return sqlite3_db_filename(pDb, zDbName);
>> > }
>> >
>> > It doesn't compile and gives linking errors:
>> >
>> > Error 1 error LNK2001: unresolved external symbol
>> > _sqlite3_db_filename
>> > C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\SQLite3_StdCall.obj
>> > SQLite3_StdCall
>> > Error 2 error LNK1120: 1 unresolved
>> > externals
>> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
>> > 1 1 SQLite3_StdCall
>> >
>> > Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API but
>> > points to line 1.
>> > This is at the start of SQLite3_StdCall.c:
>> >
>> > #include "stdafx.h"
>> > #include "SQLite3_StdCall.h"
>> >
>> > Compiling this on Windows 7 with Microsoft Visual Studio Professional
>> > Studio 2013.
>> > Admittedly, don't know anything about C or C++ or compiling C code, so I
>> am
>> > sure I must
>> > be overlooking something simple and silly, but just can't figure it out.
>> > Any ideas what could be the problem here?
>> >
>> >
>> > RBS
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
BS> ___
BS> sqlite-users mailing list
BS> sqlite-users at mailinglists.sqlite.org
BS> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
 Tegmailto:Teg at djii.com

[sqlite] problem compiling std_call dll

[Amit Chaudhuri]

Bart,

you say "don't know anything about C or C++ or compiling C code"
Perhaps this will help.

The error message (unresolved) external means that the linker is
trying to find something (_sqlite3_db_filename in this case) but it
cannot.

Possible common causes include - it has been misspelt (I'm looking at
that underscore) or you have failed to point it at a library.

I'm afraid I'm not fluent in the windows tools to track this down and
don't have a system I can follow along with.  But on Linux one might
use the nm utility to examine a contents of library files for symbols.

Regards,

Amit

On Sat, Feb 28, 2015 at 11:22 AM, Bart Smissaert
 wrote:
> Trying to compile a std_call dll to be used with VB6 and VBA.
> Using instructions and files from this website:
> https://sqliteforexcel.codeplex.com/
> All works well and have added a few SQLite functions that weren't in the
> compiled dll
> as in the download section from that website.
> However having problems adding sqlite3_db_filename.
>
> I added this to sqlite3.h:
>
> const char *sqlite3_db_filename(sqlite3 *db, const char *zDbName);
>
> and this to SQLite3_StdCall.c:
>
> SQLITE3_STDCALL_API const char * __stdcall
> sqlite3_stdcall_db_filename(sqlite3 *pDb, const char *zDbName)
> {
>  return sqlite3_db_filename(pDb, zDbName);
> }
>
> It doesn't compile and gives linking errors:
>
> Error 1 error LNK2001: unresolved external symbol
> _sqlite3_db_filename
> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\SQLite3_StdCall.obj
> SQLite3_StdCall
> Error 2 error LNK1120: 1 unresolved
> externals 
> C:\Users\Bart\SQLiteForExcel\Source\SQLite3_StdCall\Release\SQLite3_StdCall.dll
> 1 1 SQLite3_StdCall
>
> Strangely it doesn't point to a line in the block SQLITE3_STDCALL_API but
> points to line 1.
> This is at the start of SQLite3_StdCall.c:
>
> #include "stdafx.h"
> #include "SQLite3_StdCall.h"
>
> Compiling this on Windows 7 with Microsoft Visual Studio Professional
> Studio 2013.
> Admittedly, don't know anything about C or C++ or compiling C code, so I am
> sure I must
> be overlooking something simple and silly, but just can't figure it out.
> Any ideas what could be the problem here?
>
>
> RBS
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] With recursive question

[Jean-Christophe Deschamps]

Once again thank you very much Igor. I was making my life miserable 
trying to scan the "tree" from the other end.

At 00:37 01/03/2015, you wrote:

>It's a bit unfortunate that you made the root a parent of itself. 
>Forces the query to make an extra check to avoid infinite recursion.
`---

Yeah I know it was bad, it was only a quick & dirty sample. My use case 
is pretty more complex but your advice works like a charm, which isn't 
surprising.

Without abusing your time and patience, do you see a simple solution 
more fitted to a case where there are zillions "directories" at varying 
depths but only very few "files", that is without listing all the 
possible paths first and finally matching them the the files' directories?

[sqlite] With recursive question (addendum)

[Jean-Christophe Deschamps]

Forgot to mention that I sees the issue as fundamentally distinct from 
the Mandelbrot example in the SQLite docs: it uses a construct 
-outlined in procedural statements- like:

For x in xRange
   For y in yRange
 compute something like f(x, y) -- here, y is independant of x
   Next y
Next x

Put in table form:
build a table for varying x's
build a table for varying y's
compute f(x, y) for the cross join

My issue is that yRange depends on the current x for its starting 
point, so in SQL WITH parlance, I can't have a table x built before 
entering the computation.

It must be simple but I lamentably fail to see the light.

[sqlite] With recursive question

[Jean-Christophe Deschamps]

Dear list,

After trying a number of ways I'm at loss solving the seemingly simple 
problem.

For a simplified example say I have a list of individual filesystem 
directories with FK pointing to their parent:

PRAGMA foreign_keys=ON;
CREATE TABLE "Dirs" (
   "DirID" INTEGER NOT NULL PRIMARY KEY,
   "DirName" CHAR,
   "ParentID" INTEGER NOT NULL CONSTRAINT "fkDirs" REFERENCES 
"Dirs"("DirID")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "Dirs" VALUES(0,'root',0);
INSERT INTO "Dirs" VALUES(1,'A',0);
INSERT INTO "Dirs" VALUES(2,'B',0);
INSERT INTO "Dirs" VALUES(3,'C',0);
INSERT INTO "Dirs" VALUES(4,'A1',1);
INSERT INTO "Dirs" VALUES(5,'A2',1);
INSERT INTO "Dirs" VALUES(6,'A3',1);
INSERT INTO "Dirs" VALUES(7,'B1',2);
INSERT INTO "Dirs" VALUES(8,'B2',2);
INSERT INTO "Dirs" VALUES(9,'C1',3);
INSERT INTO "Dirs" VALUES(10,'X1',3);
INSERT INTO "Dirs" VALUES(11,'Y1',10);
INSERT INTO "Dirs" VALUES(12,'Z1',11);

The depth of directories is (essentially) unbounded, just like is 
actual filesystems.

I also have a list of files with FK pointing to their hosting directory:

CREATE TABLE "Files" (
   "FileID" INTEGER NOT NULL PRIMARY KEY,
   "FileName" CHAR NOT NULL,
   "FileDirID" INTEGER NOT NULL CONSTRAINT "fkFileDir" REFERENCES 
"Dirs"("DirID")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO "Files" VALUES(1,'aaa1',1);
INSERT INTO "Files" VALUES(2,'aaa2',1);
INSERT INTO "Files" VALUES(3,'bbb1',2);
INSERT INTO "Files" VALUES(4,'bbb2',2);
INSERT INTO "Files" VALUES(5,'bbb3',2);
INSERT INTO "Files" VALUES(6,'ccc1',3);
INSERT INTO "Files" VALUES(7,'zzz1',12);

Until now everything looks pretty simple, right.

What I want to obtain is the list of all files (in random order but 
that's not the point) containing:
FileID
FileName
Directory path from root using some kind of group_concat(dir, '/')
other columns from table Files not mentionned in the example above.

I've a simple WITH RECURSIVE view able to get the wanted data, but only 
for a given FileID (literally fixed in both recursive clause and final 
select).

My myopia is how can I write a recursive clause (to group directories 
from bottom up) which refers to a varying starting repository directory.

I guess there must be a clever join needed but how and where?

Note that I don't want a complete list of hierarchical directories and 
files starting from root: just actual files with their path in natural 
order.

TIA for your advices.