Re: [sqlite] 64 bits Dll

[Bogdan Ureche]

You can also use SQLite.Interop.dll as a replacement SQLite library with
encryption support. It is part of the System.Data.SQLite distribution and
both 32 bit and 64 bit are included.

Bogdan


On Fri, May 9, 2014 at 9:56 AM, Carlos Ferreira wrote:

> Constantine, really useful information.
>
> Do you know if the compiled objs that may be available in Delphi are
> comparable in performance with the ones made by MS VS.
>
> I know there is a tool, IMPLIB to import dlls or libs that can be from
> Microsoft and create Libs to use in Delphi..Not sure how they did it in
> Embarcadero..
>
> Thanks
>
> Carlos
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Constantine Yannakopoulos
> Sent: sexta-feira, 9 de Maio de 2014 15:36
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] 64 bits Dll
>
> On Fri, May 9, 2014 at 5:20 PM, Ralf Junker  wrote:
>
> > FireDAC only, and outdated by 2 months at the day of release. Delphi
> > XE5 SQLite is still at 3.7.17, almost one year behind.
> >
>
> ​In Delphi XE5 FireDAC either links statically the object files ​
> ​sqlite3_x86.obj ​/​sqlite3_x64.obj or loads sqlite3.dll and links to its
> functions via
> GetProcAddress() depending to the compiler switch FireDAC_SQLITE_STATIC.
> So it should be possible to recompile the units FireDAC.Phys.SQLiteXXX.pas
> and either link in any later version of the sqlite3 object files or have it
> load the latest dll, provided that you have $(BDS)\source\data\firedac in
> your search path.
>
> --Constantine
> ___
> 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] 64bit DLL

[Bogdan Ureche]

In case you didn't find one by now, here's 3.8.3.1:

http://www.sqliteexpert.com/library/



On Mon, Mar 10, 2014 at 4:43 AM, RSmith  wrote:

>
> Have any of you kind folks a recent 3.8.3 (or newer) 64-bit DLL for
> SQLite3 perhaps?
>
>
>
> ___
> 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] sqlite_compileoption_get + cte

[Bogdan Ureche]

You are missing one value. To get all the values, start from 0:

with
Option( name, position )
as
(
  select  sqlite_compileoption_get( 0 ) as name,
  0 as position

  union all
  select  sqlite_compileoption_get( position + 1 ) as name,
  position + 1 as position
  fromOption
  where   sqlite_compileoption_get( position + 1 ) is not null
)
selectname
from  Option

order by  name

Bogdan Ureche



On Sat, Mar 1, 2014 at 9:01 AM, Petite Abeille <petite.abei...@gmail.com>wrote:

> Just because we can:
>
> with
> Option( name, position )
> as
> (
>   select  sqlite_compileoption_get( 1 ) as name,
>   1 as position
>
>   union all
>   select  sqlite_compileoption_get( position + 1 ) as name,
>   position + 1 as position
>   fromOption
>   where   sqlite_compileoption_get( position + 1 ) is not null
> )
> selectname
> from  Option
>
> order by  name
>
> > ENABLE_FTS3_PARENTHESIS
> > ENABLE_RTREE
> > ENABLE_STAT4
> > SYSTEM_MALLOC
> > THREADSAFE=1
>
>
>
> ___
> 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] latest sqlite 3 with Delphi 5 professional

[Bogdan Ureche]

If you want to support UTF-8 in Delphi 5 then you may want to take a look
at UTF-8VCL. I have been using with Delphi 2007 with no major issues.

http://sourceforge.net/projects/utf8vcl/

Bogdan Ureche


On Wed, Jan 22, 2014 at 5:11 PM, dean gwilliam
<mgbg25...@blueyonder.co.uk>wrote:

> On 22/01/2014 22:56, RSmith wrote:
>
>> Hope some of this helps!
>>
> Yes that's extremely helpful information Ryan.
> Thanks very much indeed.
>
> ___
> 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] inconsistent column names in queries containing views vs tables

[Bogdan Ureche]

I apologize if this has been asked before but I could not find relevant
answers - or maybe I didn't know how to search.

If a SELECT statement uses qualified column names, sqlite3_column_name()
returns qualified column names for views but not for tables. Shouldn't this
behavior be the same for tables and views?

Example:

SQLite version 3.8.2.

CREATE TABLE foo (col1 INTEGER PRIMARY KEY, col2 TEXT);
CREATE VIEW vfoo AS SELECT * FROM foo;
INSERT INTO foo(col1, col2) VALUES(1, 'a');
SELECT foo.col1, foo.col2 FROM foo;

col1 col2
 
   1 a

SELECT vfoo.col1, vfoo.col2 FROM vfoo;

vfoo.col1 vfoo.col2
- -
1 a

The first SELECT statement selects from a table and the second one from a
view, and both use qualified column names. For the first SELECT statement,
the column names returned by sqlite3_column_name() are not qualified, but
for the second one they are.

Previously this behavior was determined by PRAGMA full_column_names and
PRAGMA short_column_names which are currently deprecated. Is there any way
to control this behavior in the current version, other than using column
aliases?

The documentation (http://www.sqlite.org/c3ref/column_name.html) says

"If there is no AS clause then the name of the column is unspecified and
may change from one release of SQLite to the next."

but shouldn't this be at least consistent for tables and views for the same
release of SQLite?


Thanks,

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ambiguous temporary trigger metadata

[Bogdan Ureche]

Sorry, I misread your reply. You are correct. In your scenario, after step
3 the trigger references a table that no longer exists. A similar issue
exists with foreign keys.

Bogdan




On Sun, Oct 20, 2013 at 10:11 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 20 Oct 2013, at 4:09pm, Bogdan Ureche <bogdan...@gmail.com> wrote:
>
> > I tried without success to reproduce this scenario.
> >
> > create table t1(c);
> > create temporary trigger tr1 after insert on t1 begin select raise(abort,
> > 'error'); end;
> > insert into t1(c) values(1); -- error is raised here
> > drop table t1;
> > create table t1(c);
> > insert into t1(c) values(1); -- no error here
> >
> > It seems that, when the table dies, it takes the associated trigger down
> > with it.
>
> Not that table.  The other table.  Suppose you had a trigger on table t1
> which inserted a row into t2.  Then you can DROP and reCREATE t2 while the
> trigger exists.
>
> Simon.
> ___
> 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] ambiguous temporary trigger metadata

[Bogdan Ureche]

> The problem is not that table names aren't qualified.  The problem is that
> information for setting up the schema (structural components) of a SQLite
> database are stored in the database as the SQL commands rather than a
> complicated internal format.  This makes it permissable to do this:
>
> 1. set up a trigger that refers to a table called 'books'
> 2. use that trigger a number of times
> 3. delete the table 'books'
> 4. create a new table 'books'
> 5. use that trigger more times.
>
> An iron-bound high-end SQL implementation would prevent step 3 from
> happening with an error message.
>
>
I tried without success to reproduce this scenario.

create table t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
insert into t1(c) values(1); -- error is raised here
drop table t1;
create table t1(c);
insert into t1(c) values(1); -- no error here

It seems that, when the table dies, it takes the associated trigger down
with it. Are you saying that the ghost of the trigger may still exist
somewhere in internal SQL commands and may come back once in a while to
haunt the unsuspecting new table?

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ambiguous temporary trigger metadata

[Bogdan Ureche]

Hi Richard,

Thank you for replying and for updating the documentation. I didn't realize
that the trigger may be unexpectedly reattached to a different table when
the schema changes. If this is the case then perhaps the creation of temp
triggers on non-temp tables using non-qualified table names should not be
allowed. Maybe this will be corrected in SQLite4?


> Who are your users that you allow them arbitrary control over the DDL and
> yet you feel the need to protect them from their own actions?
>

This is not about protecting users from their own actions, but rather about
providing them correct feedback as a result of these actions.

I am working on an administration tool for SQLite
<http://sqliteexpert.com/> that
allows the execution of user queries and facilitates the table
restructure using a visual editor. Extracting complete metadata information
even for corner cases is vital for the correct execution of the program.
The users are sometimes reporting all kinds of bizarre situations in which
my tool does not display correct information, and I have come to realize
that telling them "don't do this" has the tendency of making them feel
uncomfortable. I have found that a different approach is generally
preferable - that the tool should be able to handle anything the users may
throw at it.

Thank you.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ambiguous temporary trigger metadata

[Bogdan Ureche]

What would be a good way to extract metadata information about temporary
triggers? If there are multiple tables with the same name in main and temp
databases (or even attached databases), I could not find a way to determine
on which table a temporary trigger was created by examining the available
information in sqlite_temp_master, unless the table name was qualified in
the trigger creation SQL.

Example:

The main and temp databases both have a table called "t1".
Querying sqlite_temp_master by type 'trigger' returns a trigger called
"tr1" with the DDL:

CREATE TRIGGER tr1 after insert on t1 begin select 1, 2, 3; end

Was the trigger created on temp.t1 or on main.t1?

After experimenting different scenarios - and applying the changes in the
checkin [56dca4a65c <http://www.sqlite.org/src/info/56dca4a65c>] without
which the first 2 test cases below are not even possible - I found out that
the table on which the trigger is created depends on the order of creation
of the objects. Consider the following test cases - the only difference
between them is the order of the first 3 lines:

Test case 1:

create table main.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
create table temp.t1(c);
insert into main.t1(c) values(1); -- error is raised here, confirming the
trigger was created on main.t1
insert into temp.t1(c) values(1);

Note: without applying checkin
[56dca4a65c<http://www.sqlite.org/src/info/56dca4a65c>],
a "malformed database schema error" occurs at line 3.

Test case 2:

create table temp.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
create table main.t1(c);
insert into main.t1(c) values(1);
insert into temp.t1(c) values(1); -- error is raised here, confirming the
trigger was created on temp.t1

Test case 3:

create table main.t1(c);
create table temp.t1(c);
create temporary trigger tr1 after insert on t1 begin select raise(abort,
'error'); end;
insert into main.t1(c) values(1);
insert into temp.t1(c) values(1); -- error is raised here, confirming the
trigger was created on temp.t1

However, all three cases above produce the same trigger DDL in
sqlite_temp_master:

typename tbl_name rootpage sql

---   
-

trigger tr1  t1  0 CREATE TRIGGER tr1 after insert on t1 begin
select raise(abort, 'error'); end

Unless I missed something, the information in sqlite_temp_master doesn't
seem to provide useful clues about the table the trigger was created on. Is
there another way?

And yes, I know these scenarios can be avoided simply by qualifying the
table name in the trigger declaration - which would change the DDL to
"CREATE TRIGGER tr1 after insert on .t1..." - but that is beside the
point. It is not always possible to be in control of the queries the users
may be executing.

Perhaps it's time to introduce a "pragma trigger_info()"? :)

Thank you for any insights on this issue.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error: malformed database schema

[Bogdan Ureche]

This was found on Windows 7 64 bit. The following statements return error:
malformed database schema:

e:\Code\sqlite>sqlite3 :memory:
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(c);
sqlite> create temp trigger t1 before insert on t begin select * from t;
end;
sqlite> create temp table t(c);
Error: malformed database schema (t1) - trigger t1 already exists
sqlite>


If lines 2 and 3 are reversed, no errors are returned:

sqlite> create table t(c);
sqlite> create temp table t(c);
sqlite> create temp trigger t1 before insert on t begin select * from t;
end;
sqlite>

Is this a bug?

Thanks.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] False Error 'no such collation sequence'

[Bogdan Ureche]

>
> I wonder, though, if there may still remain a problem for other shells
> (SQLite managers?) that do not support loadable extensions, e.g., free
> versions of SQLite Expert, SQLite Developer, ...


You are mistaken. The free version of SQLite Expert supports loadable
extensions.

Bogdan Ureche
author of SQLite Expert


On Fri, Jun 21, 2013 at 4:28 PM, Tom Holden <ve3...@gmail.com> wrote:

> Richard Hipp drh at sqlite.org
> Fri Jun 21 00:15:56 EDT 2013 wrote:
>
> Can you not compile your custom collation sequences into a loadable
> extension (a DLL or shared library), then load that extension into your
> SQLite shell?  
> http://www.sqlite.org/loadext.**html<http://www.sqlite.org/loadext.html>
>
> -
>
> Tom replies:
> With Ralf's help, that's what I have done with SQLiteSpy in 2011 after it
> started to support loadable extensions. While I see that SQLite3.exe
> supports loadable extensions, I am not a programmer in C and barely a
> novice in MS Visual Studio C#. The fake collation DLL for SQLiteSpy won't
> load into SQLite3.exe; it was  compiled in Delphi.
>
> Having a loadable extension for the custom collation sequence SQLite3.exe
> would get around the problem with some modification in batch scripts to
> cause it to load but would leave the SQL scripts as is. It would open up
> other uses for the command line shell to modify the custom-collated data.
> That would be good. If someone is willing to give me an extension for
> SQLite3.exe that gives the name RMNOCASE to a NOCASE equivalent collation
> sequence, that would be wonderful!
>
> I wonder, though, if there may still remain a problem for other shells
> (SQLite managers?) that do not support loadable extensions, e.g., free
> versions of SQLite Expert, SQLite Developer, ...
>
> Thanks for your responses, Richard.
>
>
> Tom
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Bogdan Ureche]

Hi Richard,

Thank you for taking the time to reply. Personally I never use
double-quoted strings as string literals, but this issue was reported by
users of a tool that generates table creation SQL based on user input, and
that encloses in parenthesis any default values entered by the user to
accommodate expressions. This is no longer an issue in this case as I
implemented a workaround.

Perhaps this could be fixed in SQLite 4 if backwards compatibility is less
a concern?

Regards,
Bogdan Ureche






On Sun, Feb 10, 2013 at 2:39 PM, Richard Hipp <d...@sqlite.org> wrote:

> The fact that SQLite will treat a double-quoted string as a string literal
> rather than as a quoted identifier is a horrible mis-feature.  It was added
> 10 years or so ago in an attempt to be more MySQL-compatible.  I have come
> to sorely regret that change.  I'd love to get rid of this mis-feature, but
> cannot do so now, since there are millions of applications in the wild that
> use SQLite and some percentage of those (hopefully a very small percentage,
> but still non-zero) will break if I remove the mis-feature.
>
> The point is that allowing double-quoted strings is a tragic design error.
>
> You should never make use of this feature.  Ever.  All of your string
> literals should use the SQL-standard single-quote notation.
>
> You are correct that the mis-feature is inconsistent in its application.
> But remember that it is a mis-feature.  You shouldn't be using it, and so
> inconsistencies in its implementation shouldn't matter to you.
> Furthermore, since the only reason for preserving this mis-feature is for
> backwards compatibility in legacy applications, I see no need to try to
> make it more consistent.  Any attempts at making it consistent would likely
> just break a few legacy applications, and if I were willing to do that I
> would simply remove the mis-feature all together.
>
> On Sun, Feb 10, 2013 at 2:52 PM, Bogdan Ureche <bogdan...@gmail.com>
> wrote:
>
> > Using SQLite 3.7.15.2. The following statements execute with no error:
> >
> > CREATE TABLE [test1] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT 'test');
> >
> > CREATE TABLE [test2] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT ('test'));
> >
> > CREATE TABLE [test3] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT "test");
> >
> >
> > However, the following returns "default value of column [name] is not
> > constant."
> >
> > CREATE TABLE [test4] (
> >   [id] INTEGER,
> >   [name] CHAR DEFAULT ("test"));
> >
> >
> > There are no identifiers in this context named "test" - no other tables
> or
> > columns, and "test" is not a keyword. Changing "test" to a keyword like
> > "key" or "glob" returns the same error.
> >
> > The SQLite documentation reads:
> >
> > "If a keyword in double quotes (ex: "key" or "glob") is used in a context
> > where it cannot be resolved to an identifier but where a string literal
> is
> > allowed, then the token is understood to be a string literal instead of
> an
> > identifier."
> >
> > So in the last statement, "test" (or "key") is used in a context where it
> > cannot be resolved to an identifier but the token is *not* understood to
> be
> > a string literal.
> >
> >  To resume: while "test" is evaluated correctly as a string literal,
> after
> > enclosing it in parenthesis ("test") it no longer is.
> >
> > Does this work as intended?
> >
> > BTW, in my opinion the above quote should be changed to:
> >
> > "If a string in double quotes (be it keyword or not) is used in a context
> > where it cannot be resolved to an identifier but where a string literal
> is
> > allowed, then the token is understood to be a string literal instead of
> an
> > identifier."
> >
> > Regards,
> > Bogdan Ureche
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "default value of column [name] is not constant." error in table creation when using double quoted string literal in parenthesis

[Bogdan Ureche]

Using SQLite 3.7.15.2. The following statements execute with no error:

CREATE TABLE [test1] (
  [id] INTEGER,
  [name] CHAR DEFAULT 'test');

CREATE TABLE [test2] (
  [id] INTEGER,
  [name] CHAR DEFAULT ('test'));

CREATE TABLE [test3] (
  [id] INTEGER,
  [name] CHAR DEFAULT "test");


However, the following returns "default value of column [name] is not
constant."

CREATE TABLE [test4] (
  [id] INTEGER,
  [name] CHAR DEFAULT ("test"));


There are no identifiers in this context named "test" - no other tables or
columns, and "test" is not a keyword. Changing "test" to a keyword like
"key" or "glob" returns the same error.

The SQLite documentation reads:

"If a keyword in double quotes (ex: "key" or "glob") is used in a context
where it cannot be resolved to an identifier but where a string literal is
allowed, then the token is understood to be a string literal instead of an
identifier."

So in the last statement, "test" (or "key") is used in a context where it
cannot be resolved to an identifier but the token is *not* understood to be
a string literal.

 To resume: while "test" is evaluated correctly as a string literal, after
enclosing it in parenthesis ("test") it no longer is.

Does this work as intended?

BTW, in my opinion the above quote should be changed to:

"If a string in double quotes (be it keyword or not) is used in a context
where it cannot be resolved to an identifier but where a string literal is
allowed, then the token is understood to be a string literal instead of an
identifier."

Regards,
Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [ANN] SQLiteDoctor

[Bogdan Ureche]

Not sure about OS X, but definitely not the only
way<http://www.sqliteexpert.com/features.html> on
Windows.

Bogdan Ureche
http://www.sqliteexpert.com


On Thu, Sep 27, 2012 at 7:58 AM, Marco Bambini <ma...@sqlabs.net> wrote:

> Announcing SQLiteDoctor, the only way to recover your corrupted sqlite
> databases.
>
> Details available from:
> http://www.sqlabs.com/sqlitedoctor.php
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
>
>
> ___
> 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] A simple SELECT

[Bogdan Ureche]

I think this line is correct as it is:

INVOKE sqlite3_prepare_v2,__hSql,__lpszQuery,-1,eax,edx

The second parameter is __lpszQuery (the text of the query UTF-8 encoded).
The statement handle is returned at the address stored in eax and is
subsequently used when calling sqlite3_step.


Bogdan Ureche


On Mon, Jun 13, 2011 at 10:11 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:

> You do this:
>
> INVOKE sqlite3_prepare_v2,__hSql,__lpszQuery,-1,eax,edx
>
> Then this:
>
> INVOKE sqlite3_step,_lpSQLStatment
>
> You probably want
>
> INVOKE sqlite3_prepare_v2,__hSql,__lpSQLStatement,-1,eax,edx
>
> At least from what I tell of your logic.
>
>
>
> You're not using the same variable fo the statement handle in both calls.
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ____
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Bogdan Ureche [bogdan...@gmail.com]
> Sent: Monday, June 13, 2011 9:19 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] A simple SELECT
>
> Sorry, please disregard my previous message. It seems I experienced a page
> refresh issue and I didn't see your full message on the forum.
>
> Bogdan
>
> On Mon, Jun 13, 2011 at 9:16 AM, Bogdan Ureche <bogdan...@gmail.com>
> wrote:
>
> >
> >
> > On Mon, Jun 13, 2011 at 8:44 AM, Moi (Ph RIO Biz) <m...@phrio.biz> wrote:
> >
> >> You are right, I corrected it but this is not the problem.
> >> The problem is coming from only ONE table : LibLogInf.
> >> I exported it, destroyed it, re-created it and re-entered the datas the
> >> problem always exists !
> >>
> >
> > You mean you don't get any records when running your query?
> >
> >
> >> If I made the same query with just changing the table name there is no
> >> problem !
> >> Generaly, I use SqlExpert to create the query, like this I am sure of
> the
> >> syntax, then I copy the query to my assembler program.
> >> I removed the index too.
> >> Sincerally, I don't understand.
> >> I have the last SqLite version too.
> >> I made a vacuum, a repair, I re-indexed all the table, no errors found.
> >> Is this table too small ?
> >>
> >>
> > By the way, I hope you realize that anyone reading the messages on the
> > forum is totally confused because they are not aware of the assembler
> code
> > you sent me. If you expect helpful replies from other forum members then
> you
> > should post the full description of the problem there including the
> > assembler code and the expected result. Otherwise we can just continue
> the
> > discussion by email.
> >
> > So if I understand correctly, you don't get any records when running your
> > assembler code, but you do get one record when running the query in
> SQLite
> > Expert. If this is correct, can you please send me the assembler code
> with
> > your new corrections?
> >
> > Bogdan
> >
> ___
> 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] A simple SELECT

[Bogdan Ureche]

Sorry, please disregard my previous message. It seems I experienced a page
refresh issue and I didn't see your full message on the forum.

Bogdan

On Mon, Jun 13, 2011 at 9:16 AM, Bogdan Ureche <bogdan...@gmail.com> wrote:

>
>
> On Mon, Jun 13, 2011 at 8:44 AM, Moi (Ph RIO Biz) <m...@phrio.biz> wrote:
>
>> You are right, I corrected it but this is not the problem.
>> The problem is coming from only ONE table : LibLogInf.
>> I exported it, destroyed it, re-created it and re-entered the datas the
>> problem always exists !
>>
>
> You mean you don't get any records when running your query?
>
>
>> If I made the same query with just changing the table name there is no
>> problem !
>> Generaly, I use SqlExpert to create the query, like this I am sure of the
>> syntax, then I copy the query to my assembler program.
>> I removed the index too.
>> Sincerally, I don't understand.
>> I have the last SqLite version too.
>> I made a vacuum, a repair, I re-indexed all the table, no errors found.
>> Is this table too small ?
>>
>>
> By the way, I hope you realize that anyone reading the messages on the
> forum is totally confused because they are not aware of the assembler code
> you sent me. If you expect helpful replies from other forum members then you
> should post the full description of the problem there including the
> assembler code and the expected result. Otherwise we can just continue the
> discussion by email.
>
> So if I understand correctly, you don't get any records when running your
> assembler code, but you do get one record when running the query in SQLite
> Expert. If this is correct, can you please send me the assembler code with
> your new corrections?
>
> Bogdan
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A simple SELECT

[Bogdan Ureche]

On Mon, Jun 13, 2011 at 8:44 AM, Moi (Ph RIO Biz)  wrote:

> You are right, I corrected it but this is not the problem.
> The problem is coming from only ONE table : LibLogInf.
> I exported it, destroyed it, re-created it and re-entered the datas the
> problem always exists !
>

You mean you don't get any records when running your query?


> If I made the same query with just changing the table name there is no
> problem !
> Generaly, I use SqlExpert to create the query, like this I am sure of the
> syntax, then I copy the query to my assembler program.
> I removed the index too.
> Sincerally, I don't understand.
> I have the last SqLite version too.
> I made a vacuum, a repair, I re-indexed all the table, no errors found.
> Is this table too small ?
>
>
By the way, I hope you realize that anyone reading the messages on the forum
is totally confused because they are not aware of the assembler code you
sent me. If you expect helpful replies from other forum members then you
should post the full description of the problem there including the
assembler code and the expected result. Otherwise we can just continue the
discussion by email.

So if I understand correctly, you don't get any records when running your
assembler code, but you do get one record when running the query in SQLite
Expert. If this is correct, can you please send me the assembler code with
your new corrections?

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A simple SELECT

[Bogdan Ureche]

Hello Philippe,

My assembler is a bit rusty, but it looks like you perform the jump to
@ReadyToGetData when the return code is either SQLITE_ROW or SQLITE_DONE.
You should only attempt to get the data when the return code is SQLITE_ROW.
Bogdan


On Mon, Jun 13, 2011 at 2:47 AM, Moi (Ph RIO Biz)  wrote:

> I encounter a big problem with a simple query. Here is the query
>
> SELECT LTRIM(RTRIM(_Libelle)) FROM LibLogInf WHERE ((_Index = 3) AND
> (_Langue = 1))
>
> When I run it from SqLiteExpertPro there is no problem and I get my result.
> Into my program I get a NULL pointer.
>
> The table is defined like this :
>
> CREATE TABLE "LibLogInf" (
> [_Index] INTEGER COLLATE BINARY DEFAULT (0),
> [_Langue] INTEGER COLLATE BINARY DEFAULT (0),
> [_Libelle] VARCHAR(255) COLLATE BINARY,
> CONSTRAINT [sqlite_autoindex_LibLogInf_1] PRIMARY KEY ([_Index],
> [_Langue]));
>
> Here is the program code in assembly language :
>
> SqLite_GetText PROC __hSql:HANDLE,__lpszQuery:LPSTR,__lpszResult:LPSTR
> LOCAL _lpSQLStatment:DWord
> LOCAL _lpszErrorMessage:LPSTR
>
> lea edx,_lpszErrorMessage
> lea eax,_lpSQLStatment
>
> mov DWord Ptr [edx],0
> mov DWord Ptr [eax],0
>
> INVOKE sqlite3_prepare_v2,__hSql,__lpszQuery,-1,eax,edx
>
> test eax,eax
> jz @Success
>
> xor eax,eax
>
> stc
> ret
>
> ALIGN 16
>
> @Success :
>
> INVOKE sqlite3_step,_lpSQLStatment
>
> cmp eax,SQLITE_ROW
> je @ReadyToGetData
>
> cmp eax,SQLITE_DONE
> je @ReadyToGetData
>
> INVOKE sqlite3_finalize,_lpSQLStatment
>
> mov edx,__lpszResult
> xor eax,eax
> mov [edx],eax
>
> stc
> ret
>
> ALIGN 16
>
> @ReadyToGetData :
>
> INVOKE sqlite3_column_text,_lpSQLStatment,0
> cmp __lpszResult,NULL
> jne @CopyString
>
> xor eax,eax
>
> stc
> ret
>
> ALIGN 16
>
> @CopyString :
>
> test eax,eax
> jnz @Copy
>
> INVOKE sqlite3_finalize,_lpSQLStatment
>
> mov eax,__lpszResult
> mov Byte Ptr [eax],0
>
> xor eax,eax
>
> stc
> ret
>
> ALIGN 16
>
> @Copy :
>
> INVOKE lstrcpy,__lpszResult,eax
> INVOKE sqlite3_finalize,_lpSQLStatment
>
> mov eax,__lpszResult
>
> clc
> ret
> SqLite_GetText ENDP
>
> Can someone help me ?
> ___
> 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] pragma foreign_key_list deprecated in 3.7.4?

[Bogdan Ureche]

Now that foreign key constraints are enforced natively, why would you want

> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?
>
>
>
It would make life easier for developers of administration tools for SQLite,
for displaying/editing foreign key constraints visually. The alternative
would be to parse the CREATE TABLE statements.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in sum function

[Bogdan Ureche]

>
>
> SQLite Expert (which I use extensively in the Pro version) enforces
> displaying types as declared.  So if you declare your column as INTEGER
> (or INT, ...) then it will display integers regardless of the actual
> individual data type using common conversions.  This is a side effect
> of the Delphi grid component that Bogdan uses.
>
>
This is the default behavior in SQLite Expert but can be overridden. If you
store floating-point values in columns declared as INTEGER, you might want
to change the default type mappings to INTEGER -> Float (or WideString) and
you will see the floating-point values correctly in the grid.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestion - make pragma case_sensitive_like queryable

[Bogdan Ureche]

>
>
> The setting is detectable:
>
>
Simon, thank you for the reply. It's not exactly the answer I was hoping
for, but I suppose fixing this is a low priority if there is a workaround.

Regards,

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] suggestion - make pragma case_sensitive_like queryable

[Bogdan Ureche]

The pragma case_sensitive_like is not queryable, unlike other pragmas. Is
there any reason why it's not?

If not, I would like to suggest to be made queryable, for consistency.

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column constraint diagram

[Bogdan Ureche]

Jay, thank you for the reply, this confirms what I suspected. Incidentally,
SQLite also accepts a conflict clause here although it doesn't make sense:

CREATE TABLE Test(
  TestId INTEGER NULL ON CONFLICT ABORT,
  TestName TEXT NULL);

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] column constraint diagram

[Bogdan Ureche]

SQLite seems to accept the following column declaration syntax without
errors:

CREATE TABLE Test(
  TestId INTEGER NULL,
  TestName TEXT NULL);

However, there is no branch in the column constraint
diagram
describing
this syntax. In the NOT NULL branch, the NOT keyword is not optional. Is
this case described on another diagram?


Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is ticket 4037 not a bug?

[Bogdan Ureche]

Allan,

Thank you very much for the reply. You are correct when stating that,
according to SQLite documentation this SQL syntax is not expected to work.
Therefore, it is not a bug. Perhaps it should be added to the list of SQL92
features unsupported  by SQLite.

But I am asking myself: was that omission deliberate or just an oversight?
One possible answer would be that, when designing the attaching feature, the
developers started from the following requirement: "We want the queries in
attached databases to behave exactly like in the main database, with one
exception: no support for the syntax [database_name].[table_name].* because
it's too much work or for some other reason. We will support this later if
users ask for it."

If that was the case, then by all means ticket 4037 is a feature request.

But as you pointed out, it seems logical for this to work so I tend to
believe it was an oversight. In which case one might argue it's still a
feature request, however it would be closer to the grey area between bugs
and feature requests.

If this is a feature request, I would give it a higher priority than 'nice
to have'. I suppose it would be much easier to add this feature than it was
to add, say, foreign key support. I still can't explain why this syntax
error was not reported earlier, as I ran into this issue shortly after
adding support for attached databases in my project. Perhaps the usage of
attached databases in SQLite is not as widespread as one might expect?

Again, many thanks.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is ticket 4037 not a bug?

[Bogdan Ureche]

>
>
>
> I can imagine query generators tend to use more, possibly
> redundant, qualifiers than a human programmer would.
> --
>  (  Kees Nuyt
>  )
> c[_]
>


That may be true but sometimes the qualifiers are needed to avoid ambiguity,
in which case they are not redundant.

Bogdan



> ___
> 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] is ticket 4037 not a bug?

[Bogdan Ureche]

Many thanks to all who replied, that was very helpful. So the ticket was
closed because the old CVSTRACKER was closed, and a new ticket should be
created with the replacement mechanism, after being discussed in the mailing
list. @Roger: I apologize for the misunderstanding.

> If
> it alters the behaviour compared to previous SQLite versions then breaking
> that backwards compatibility would require exceptionally compelling
evidence.
>
In my opinion fixing this error should not break compatibility, because I
can't imagine a legitimate usage case that relies on a syntax error in order
to work correctly. Breaking compatibility in this case would mean that some
old code that didn't work suddenly starts working. Would that be so bad?

Also, I was unable to find a practical workaround for this issue. Since in
my case the queries are auto-generated, the only thing that comes close
would be to parse the output and replace the '*' with the full list of
columns in the table. I tried to feed a COCO/R generated parser with the
SQLite grammar but it choked and died miserably, so maybe I'll have better
luck with lexx/yacc.

Personally I am inclined to re-open this ticket with the new bug tracker,
unless someone could give a reason why not to.

Many thanks.

Bogdan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] is ticket 4037 not a bug?

[Bogdan Ureche]

Hi,

A few weeks ago I created ticket 4037 with the description:


The following query

select databasename.tablename.* from databasename.tablename

returns:

near "*": syntax error

However, the following query returns no error:

select databasename.tablename.fieldname from databasename.tablename


I just noticed the ticket was closed without being fixed, with the following
remark:

"Please discuss this on the sqlite-users mailing list, especially including
what the SQL standard says and what other databases say."

Now, I have to admit that English is not my first language, nor the second,
but the way I see it this remark means something like: "This is not a bug,
and if you want to find out why please ask this question in the mailing
list. "

So here I am asking the question here: is this a bug or not?

More info:

I found this issue while using a query builder component available for
Delphi (the Active Query Builder) which may generate the above query based
on the user selection. I suppose I will have a hard time trying to convince
the developers of Active Query Builder to "fix" their tool because it
generates a query that SQLite does not support.

Regarding "what other databases say": the only other database that I
currently have access to is SQL Server, and it supports the syntax without
complaining.

I also downloaded the SQL92 standard, and I didn't find anything relevant
but maybe I didn't know where to look so I would appreciate any pointers.

I am perfectly willing to accept an explanation like: "yes, it is a bug but
it's a corner case and we have better things to do, so stop bugging us and
get a life". In which case I will probably have to find a workaround like
parsing the query and stripping the "databasename" to get something like:

select tablename.* from databasename.tablename

which works fine. However, the following scenario still does not work:

1. The "main" database and the attached database "databasename" both contain
a table called "tablename"
2. The tables with duplicate names from both databases participate in the
generated query, which looks like

select databasename.tablename.*, main.tablename.fieldname
from databasename.tablename, main.tablename

In this case we cannot strip the "databasename" because the syntax would be
ambiguous.

Could anybody help me with this? Basically what I'm looking for is the
answers to these questions:

1. Is this a bug or not? If not, any reason why not?
2. Are other databases supporting this syntax?
3. Is this an invalid syntax according to the SQL standard?
4. Is there a workaround?

I would appreciate any help.

Bogdan Ureche
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name

[Bogdan Ureche]

Unless the user is a paying customer.

On Fri, Nov 13, 2009 at 8:37 AM, Jean-Christophe Deschamps 
wrote:

>
>
> >Unfortunately I cannot modify the query... it is supplied by an user.
>
> Well, what about upgrading the user?
>
> Sorry coul'd resist ... I'm already out!
>
>
>
> ___
> 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] NEWBY Question: What would be the best way to get data from a DBF4 database to a SQLite SQB?

[Bogdan Ureche]

You could try:

www.sqliteexpert.com

Data transfer wizard -> Import from ADO data source -> Microsoft OLE DB
provider for ODBC drivers -> dBase files

should do the job, though I never tried with DBF databases.

Bogdan

-Original Message-
From: Graham Wickens [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 02, 2006 2:59 PM
To: SQLite Forum
Subject: [sqlite] NEWBY Question: What would be the best way to get data
from a DBF4 database to a SQLite SQB?

Hi All,

I am a newcomer to SQLite and would like to know the best way to get 
data from my DBF4 database to a SQLite SQb. I need to do this because 
SQLite is used by a USB Device I recently purchased. I am at hobby level 
for DBF4 but have no exposure to SQLite before.

TIA

Sorry if this is a repeat message, I am not sure if the first request  
reached this list.

-- 
  ¿¿¿
 (ô ô)   [EMAIL PROTECTED]
ooO-(_)-Ooo     



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-