Hi, ALL,

Is there a simple way to get the default value for the field in the table (with C API or plain SQLite dot command)?

Thank you.

Hi, Keith,

I understand.

The problem is that I run _prepare/_step/finalize in a thread lunched from GUI. And when the user press a {hot} key I want the thread terminated and statement gracefully closed.

Now what I was thinking is to call some kind of function to cancel statement processing when a thread is deleted.

Or am I looking at it wrong?

Thank you.

Hi, Larry,

The flag set by sqlite3_interrupt() is checked when the VDBE code loops, which is during sqlite3_step() operation. There will be no interruption of prepare and certainly not of finalize.

That's understandable.

Nothing untoward happens to SQLite library managed state. The usual rules apply to the user helping the library do so.

OK, so I will still have to finalize the statement right?

If you cannot be sure that a connection may become closed just before calling sqlite3_interrupt(), then using the sqlite3_progress_handler() interface would be a safer way to effect interruption. The progress callback can cause the VDBE execution in progress to be stopped, with no danger of the connection being closed while the callback runs.

Its not about closed connection.

Like I said I am running a thread checking if a new table is created by querying sqlite_master. And then I want to run another thread where I will execute SELECT FROM abc; When I hit ESC I want to interrupt the second SELECT statement. I want the first thread to keep running querying the sqlite_master.

So it looks like the way to go is to use progress_handler().

Thank you.

Hi, ALL,

There is sqlite3_interrupt() which should cancel the currently running operation. However I have questions about it:

1. Lets say I have a thread that shuold continuously running and query the DB for a new tables. And at some point of time I will run the SELECT statement with _prepare/_step/_finalize again in a thread. Now when I call sqlite3_interrupt - which operation will be cancelled?

2. What will happen with the statement handle after this call? Should there be a _finalize call?

3. Is there another SQLite API that will allow to cancel the running statement (see question 1)?

Thank you.

Hi, Gunter,

For a second lets forget about my last message.

This was my code:

if(  ( res = sqlite3_prepare_v2( m_db, "SELECT * FROM master.leagues", -1, &m_stmt, NULL ) ) != 0 )
{
    int code = sqlite_errcode( m_db );
}

This was my original code.

As you can see the _prepare call should fail and sqlite_errcode() line will be executed.

What I saw is the "code" variable will become 0 and therefore no error processing will occur.

When I changed that line to be:

char *message = sqlite_errmsg( m_db );

I did get an error message which said "No table master.leagues" and after I fixed the code everything started working correctly.

So, now the question is - are you able to get a 0 in my scenario #1?

I have MSVC 2017/Windows 8.1/SQLite 3.24.0 library embedded in the project.

Thank you.

P.S. Note - I hardcoded the query with the schema and table names to indicate a problem.

Gunter,

The thing about "master" vs "main" was surprising. I thought I queried the PRAGMA for proper schema name. Apparently not.

Nevertheless - the issue with the _errcode() vs _errmsg() is weird...

Thank you.

Gunter & David,

Thank you guys.

When I come home I will see where the "master" comes from.

I should have queried the appropriate PRAGMA for the proper schema name.

Will check.

Hi, Gunter,

The only reason I'm asking is because I tried to call

[code]

sqlite3_prepare_v2()

sqlite3_errcode()

[/code]

and first returned 1 while second returned 0.

Any suggestions on debugging?

This is under MSVC 2017 / Windows 8.1

Thank you.

Gunter, I tried to execute following:

[code]

SELECT * FROM master.leagues;

[/code]

I am connected to only 1 database (for the time being).

When I tried to use sqlite3_errmesg(), it returned:

[quote]

No such table exist: master.leagues

[/quote]

but the table "leagues" is definitely exist.

Besides the first API call shouldn't return 0 (SQLITE_OK) as you said...

Any help?

Thank you.

P.S.: I'm using 3.24.0 release.

Hi, ALL,

Is it better to call sqlite3_errcode() or sqlite3_errmsg()?

What is the difference between those 2?

I presume both return SQLITE_OK on success (meaning error is successfully retrieved).

Thank you.

Hi, ALL,

If I have a table:

CREATE TABLE images( id INTEGER PRIMARY KEY, image BLOB);

and I have an image:

~/Images/horse.png

what would be the proper syntax to insert that image into that table?

Thank you.

Hi guys, One more question, if I may to close this thread...

What is sqlite3_int64 typedef'ed from?

Thank you.

Hi, Keith,

So, even if I know that the value is and will be 1 or 0 (like boolean in C/C++), it will still better to use sqlite3_column_int64(), right? At least that's how I read your reply...

Keep in mind that 1 and 0 above are used as an example.

Thank you.

Hi, Ryan, Thank you for confirming that it will work as intended, even though you don't say what the intention is. ;-)

However, my thought was to write smth like:

case SQLITE_INTEGER: sqlite_column_int(....); break; case SQLITE_INTEGER64: sqlite3_column_int64(...); break;

because as you wrote originally, sqlite3_column_int() appears more efficient:

[quote] (These happens to be smaller space-wise, more space-efficient to transfer in say an internet stream, and also used to be faster to do calculations with on older 32-bit systems, hence still prevailing through esp. legacy code. [/quote]

Hopefully now you see the reason for my original question - I just wanted to make sure I'm not missing anything.

TY for sticking with me and sorry for poor explanation in the OP.

BTW, is there a reason code is not preformatted on this forum? Either with usual [code[[/code] tags, "{{{ }}}" or simply SO way with backticks?

BTW2, is there a simple way to quote you in my reply?

Hi, Ryan, I understand that SQLite is more like Perl/Python whereas MS SQL Server/PostgreSQL/mySQL/Sybase/Oracle are more like C/C++/Pascal.

I was acrually hoping to write something like:

[code] if( ( res = sqlite3_prepare_v2( m_db, sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), -1, &m_stmt, NULL ) ) != 0 ) { GetErrorMessage( res, errorMessage ); errorMsg.push_back( errorMessage ); } else { while( true ) { res = sqlite3_step( m_stmt ); if( res == SQLITE_ROW ) { std::vector<DataEditFiield> row; int count = sqlite3_column_count( m_stmt ); for( int i = 0; i < count; ++i ) { res = sqlite3_column_type( m_stmt, i ); switch( res ) { case SQLITE_INTEGER: row.push_back( DataEditFiield( sqlite3_column_int64( m_stmt, i ) ) ); break; case SQLITE_FLOAT: row.push_back( DataEditFiield( sqlite3_column_double( m_stmt, i ) ) ); break; case SQLITE_TEXT: row.push_back( DataEditFiield( sqlite_pimpl->m_myconv.from_bytes( reinterpret_cast<const char *>( sqlite3_column_text( m_stmt, i ) ) ) ); break; } } } } } [/code]

in order to kind of emulate all those "big DBMSes" and use it to further processing.

Am I wrong? Will it work like this?

Thank you.

Hi, ALL,

From the documentation:

[quote] The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value. The value returned by sqlite3_column_type() is only meaningful if no automatic type conversions have occurred for the value in question. After a type conversion, the result of calling sqlite3_column_type() is undefined, though harmless. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion. [/quote]

However, there are 2 types/functions there: sqlite3_column_int() and sqlite3_column_int64().

Do I understand correctly that the second one exist for 64-bit builds? If not - shouldn't there be SQLITE_INTEGER and SQLITE_INTEGER64?

Or is there a better way to distinguish? Or I should be using _int4() all the time?

Please advice.

Thank you.

Hi, ALL,

Is there a way to create an empty database on the disk?

I know that when you just start the CLI interface the empty "in-memory database" will be created. But is there a way to save this database on the disk?

Thank you.

Hi, Try this:

temp = "INSERT INTO PROJECT (pro_id, app_ID, Start, End, Output, "Log Type", Errors, Success, NOTES, Duration) VALUES(1,1, Date & time in quotes, Date & time in quotes, '1, 1, 0, 0, '0.0.0.0.000')";

Thank you.

Hi, ALL, I tried to compile the 3.24.0 on Oracle Solaris with Solaris Studio native compiler (not gcc).

I got 2 warnings:

line 29907: conversion to double is out of range line 52491: statement not reached

Just want to make people here aware of those 2.

Also, the number of warnings is small comparing to OSX Xcode compilation. ;-)

I'm not sure how the SQLite for Solaris is compiled (gcc or cc, what options).

Thank you.

Hi, Richard,

My question was not about "Copyright", but about "Licensing".

From experience I know that many pakcage prohibits static linking (until couple of years it was the case for Qt - you had to pay a significant amount of money for a license to do a static linking with Qt).

I also know that on Windows static linking is preferred method of building the application, while on *nix - it is generally frowned upon. I don't know what is the reason behind it - but that's how it is.

Now you answer makes it clear that SQLite (as some other libraries) can be statically linked to any program, without violation any laws.

Thank you.

Hi,

A general question about linking - does SQLite license allows static linking on *nix platforms where it is generally frown upon?

Thank you.

Hi,

And you can just run it in a secondary thread which shouldn't give any overhead on the main application...

Thank you.

Hi,

How did you compile the "server program"?

Did you include the sources in its project or use the OS pre-build library? (most likely the latter).

Thank you.

Hi,

I believe every Android device comes with the copy of the SQLite.

In regards to the verion - it is not the version of the API the database will be created with but the API that you will access the DB file with.

Now in terms of C/JAVA API - it is completely backwards compatible.

I presume you will utilize the JAVA API I refer you to.

And if you want the latest and greatest you can just grab the source code from the sqlite,org and include those .c/.h files in your project.

Thank you.

Hi,

If you have an Android device chances are you already have SQLite installed. Depending how updated you device is - it might be an older version, but from the development POV this shouldn't concern you.

Now since this is Android: For the JAVA you should look in the "How to Android JAVA SQLite". There are many tutorials on the Web including the newer Android interface for SQLite.

In terms of C++ - you can check the SQLIte C API and start there.

Thank you.

Hi, This question kind of relates to the one I just asked.

Is there a way from the C API to say that there is an attached database?

Thank you.

And yes - I do know this. Otherwise one will not be able to safely open the "main" database.

Thank you.

Keith, Thank you for the info. As I described in my message I simply stated the fact on how Warren put his statement in the answer to the OP.

However I never knew that the original schema is called "main".

Thank you.

Hi, Warren, From you first reply:

[quote] Are you looking for something other than ATTACH DATABASE? [/quote]

from which I presume there is an other way to attach the database than issue "ATTACH" statement.

Thank you.

Hi, Warren, Is there any other command by which I can "attach database"?

Now is the syntax I put in is correct?

Consider this scenario:

1. sqlite3 conn.db
2. attach att.db
3. SELECT....

Thank you.

Hi, ALL,

Lets say I opened a connection to a database and want to attach a second database.

Lets call connected DB a conn.db and attached one - att.db.

Will I be able to do an inter-DB query?

If yes - what would be the syntax?

[code] SELECT conn.table1.field1, att.table2.field2 FROM conn.table1, att.table2 WHERE conn.table1.id = att.table2.id; [/code]

Or something different?

Thank you.

@KeithMedcalf, Thank you for clarification!

Hi, In my application I use thread to query SQLite DB and I also query DB from the main application.

Should I turn on MT support in SQLite?

Thank you.

@sbooth, Good - it means I'm not the only one who sees them (on Xcode). Except I'm not on Swift - I'm doing Objective-C stuff.

I think the test I did producing sqlite3.o is not really accurate - "Wall" is not turning on "all" warnings (if my memory serves me right).

So apparently it is Xcode/clang thing and we have to live with that. ;-)

Thank you.

@AdrianHo,

Right on.

Doing this from the Terminal with that command doesn't produce any warnings. Does anybody know how do I see the command Xcode uses to compile the source?

Thank you.

Hi, Larry,

I believe that expansion goes to the system one, according to my post. The inclusion of the param.h happens here:

sqlite3.c@ 31564:

#if SQLITE_ENABLE_LOCKING_STYLE /* # include <sys/ioctl.h> */

include <sys/file.h>

include <sys/param.h>

#endif /* SQLITE_ENABLE_LOCKING_STYLE */

I have no idea what the #if condition should or shouldn't do. But I guess it is defined in my build...

Thank you.

Hi, Richard,

Clicking on the warning itself reveals:

line 27134 of sqlite3.c: if( MAX(e2,0)+(i64)precision+(i64)width > etBUFSIZE - 15 ){

Clicking on "Expanding this definition of MAX": line 218 of /usr/include/sys/param.h (MacOS 10.13):

#define MAX(a,b) (((a)>(b))?(a):(b))

Clicking on the line: "Other definition of MAX":

line 13457 of sqlite3.c:

define MAX(A,B) ((A)>(B)?(A):(B))

Hi, ALL, I understand that this will not be fixed (from the previous discussion), but I'm just curious for the reason.

When compiling my code that uses SQLite3 inside the Xcode, I see a following warning (among the others):

Ambiguous expansion of macro MIN Ambiguous expansion of macro MAX

Does this mean that SQLite3 provides its own implementation of them?

Thank you.