SQLite Forum

Timeline
Login

40 forum posts by user oneeyeman

2022-01-16
20:32 Post: Get the default value fr the field (artifact: 443bab103c user: oneeyeman)

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.

2021-03-19
00:57 Reply: Cancelling the query (artifact: 2baf49c8a2 user: oneeyeman)

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.

00:44 Reply: Cancelling the query (artifact: a990fe9bd3 user: oneeyeman)

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.

2021-03-18
23:55 Post: Cancelling the query (artifact: 93ce7a6500 user: oneeyeman)

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.

13:24 Reply: Getting the error (artifact: cf98f29453 user: oneeyeman)

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.

2021-03-17
16:42 Reply: Getting the error (artifact: dfdca248ad user: oneeyeman)

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.

16:40 Reply: Getting the error (artifact: c6f8091325 user: oneeyeman)

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.

15:45 Edit reply: Getting the error (artifact: 59eb2f5751 user: oneeyeman)

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.

15:44 Reply: Getting the error (artifact: 78fd7a3ace user: oneeyeman)

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.

14:03 Reply: Getting the error (artifact: d924caf0b4 user: oneeyeman)

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.

06:40 Post: Getting the error (artifact: 7f4dda1460 user: oneeyeman)

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.

2021-03-12
22:50 Post: Inserting the image as BLOB (artifact: 7b9c2c823c user: oneeyeman)

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.

2021-03-10
02:10 Reply: Type of the column (artifact: 86af5d023a user: oneeyeman)

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

What is sqlite3_int64 typedef'ed from?

Thank you.

2021-03-09
18:12 Reply: Type of the column (artifact: 81868a6589 user: oneeyeman)

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.

16:24 Reply: Type of the column (artifact: 048f75063e user: oneeyeman)

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?

15:05 Reply: Type of the column (artifact: 22618f9295 user: oneeyeman)

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.

00:57 Reply: Type of the column (artifact: e1781d8a2c user: oneeyeman)
> I will assume _int4() is a typo and you meant _int64() there.

Yes sorry. My keyboard needs serious cleaning. ;-)

> The answer is No, it has nothing to do with whether you are running a 64-bit or 32-bit application. It has strictly to do with the size of the target variable/memory you want the value of the column to be written to.

OK.

> If you expect the values to always be smaller than 4-billion, then a 32-bit integer target will do just fine. (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.)

But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer?

> Some applications need values even bigger, like astronomy systems using perhaps 
> 80-bit integer variables. These applications have to provide their own 80-bit 
> variable spaces and perhaps their own code for doing calculation with them. The 
> point here is that the memory/variable sizes holding the data is dependent on 
> the use case of the data, NOT the bitness of the application, underlying CPU, or 
> Operating system, etc.

Does it still comes as SQLITE_INTEGER in this case?

Thank you.
2021-03-08
06:45 Post: Type of the column (artifact: 17f1f3304b user: oneeyeman)

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.

2020-12-26
00:44 Post: Creating an emty database (artifact: 62ccd96175 user: oneeyeman)

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.

2020-11-27
19:39 Reply: Insert Statement (artifact: fa7da1a43f user: oneeyeman)

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.

2020-10-20
21:31 Post: Warning compiling on Solaris (artifact: f664862bd6 user: oneeyeman)

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.

2020-10-13
00:06 Reply: Ubuntu SQLite 3.33 (artifact: 21b411eb2a user: oneeyeman)

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.

2020-10-12
23:39 Reply: Ubuntu SQLite 3.33 (artifact: b19fe43b91 user: oneeyeman)

Hi,

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

Thank you.

22:21 Reply: Most efficient way to tell if any database content has changed (artifact: d34ff56fbf user: oneeyeman)

Hi,

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

Thank you.

22:15 Reply: Ubuntu SQLite 3.33 (artifact: e677f3f997 user: oneeyeman)

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.

2020-10-08
23:09 Reply: SQLite on Android Devices (artifact: 22a8985e83 user: oneeyeman)

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.

18:10 Reply: SQLite on Android Devices (artifact: 020992df14 user: oneeyeman)

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.

2020-10-06
23:18 Post: Identifying attached database (artifact: 7675c744fc user: oneeyeman)

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.

22:45 Reply: Query 2 databases (artifact: d1530681a2 user: oneeyeman)

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

Thank you.

22:44 Reply: Query 2 databases (artifact: 00a8b34620 user: oneeyeman)

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.

21:58 Reply: Query 2 databases (artifact: b4b06959b9 user: oneeyeman)

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.

19:53 Reply: Query 2 databases (artifact: 037c5a4185 user: oneeyeman)

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.

19:38 Post: Query 2 databases (artifact: 0a3888254d user: oneeyeman)

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.

2020-09-19
23:36 Reply: Multithreading support (artifact: 219b0494ce user: oneeyeman)

@KeithMedcalf, Thank you for clarification!

15:45 Post: Multithreading support (artifact: 76574f0fff user: oneeyeman)

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.

2020-08-24
14:08 Reply: Weird warning on the Xcode/OSX (artifact: 321dd256c0 user: oneeyeman)

@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.

2020-08-23
16:47 Reply: Weird warning on the Xcode/OSX (artifact: 6d0e24a98e user: oneeyeman)

@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.

2020-08-22
20:13 Reply: Weird warning on the Xcode/OSX (artifact: b76bfec2d2 user: oneeyeman)

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.

19:51 Reply: Weird warning on the Xcode/OSX (artifact: fefbe2c6e0 user: oneeyeman)

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))

18:51 Post: Weird warning on the Xcode/OSX (artifact: 7975953ca4 user: oneeyeman)

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.