Re: [sqlite] Weird issue with the query

[Igor Korot]

Simon,

On Fri, Dec 2, 2016 at 9:47 PM, Simon Slavin  wrote:
>
> On 3 Dec 2016, at 2:44am, Igor Korot  wrote:
>
>> Ok so in order to fix it I should assign the result of conversion to some
>> variable?
>
> It might be simpler to use SQLITE_TRANSIENT instead of SQLITE_STATIC.
>
> 

Yes, that fixed it.

And sorry for the double e-mail. For some reason sending it from the phone
didn't update the mailbox immediately.

Thank you.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Weird issue with the query

[Igor Korot]

Igor
Ok so in order to fix it I should assign the result of conversion to some
variable?

Thank you.

On Dec 2, 2016 9:07 PM, "Igor Tandetnik"  wrote:

On 12/2/2016 8:39 PM, Igor Tandetnik wrote:

> On 12/2/2016 6:56 PM, Igor Korot wrote:
>
>> res = sqlite3_bind_text( stmt, 1,
>> sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
>> SQLITE_STATIC );
>>
>
> SQLITE_STATIC tells SQLite that the string will outlive the statement
> handle. But in fact, you are passing a temporary buffer, deallocated at
> the end of the statement. Your program exhibits undefined behavior.
>

Upon re-reading, that sounds ambiguous. What I meant was, the temporary
string is deallocated at the end of the C++ statement in which it was
created - in other words, at the nearest semicolon. It gets pulled right
from under a live SQLite statement.

-- 
Igor Tandetnik

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

Re: [sqlite] Weird issue with the query

[Simon Slavin]

On 3 Dec 2016, at 2:44am, Igor Korot  wrote:

> Ok so in order to fix it I should assign the result of conversion to some
> variable?

It might be simpler to use SQLITE_TRANSIENT instead of SQLITE_STATIC.



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

Re: [sqlite] Weird issue with the query

[Igor Korot]

Igor
Ok so in order to fix it I should assign the result of conversion to some
variable?

Thank you.

On Dec 2, 2016 9:07 PM, "Igor Tandetnik"  wrote:

> On 12/2/2016 8:39 PM, Igor Tandetnik wrote:
>
>> On 12/2/2016 6:56 PM, Igor Korot wrote:
>>
>>> res = sqlite3_bind_text( stmt, 1,
>>> sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
>>> SQLITE_STATIC );
>>>
>>
>> SQLITE_STATIC tells SQLite that the string will outlive the statement
>> handle. But in fact, you are passing a temporary buffer, deallocated at
>> the end of the statement. Your program exhibits undefined behavior.
>>
>
> Upon re-reading, that sounds ambiguous. What I meant was, the temporary
> string is deallocated at the end of the C++ statement in which it was
> created - in other words, at the nearest semicolon. It gets pulled right
> from under a live SQLite statement.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Weird issue with the query

[Igor Tandetnik]

On 12/2/2016 8:39 PM, Igor Tandetnik wrote:

On 12/2/2016 6:56 PM, Igor Korot wrote:

res = sqlite3_bind_text( stmt, 1,
sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
SQLITE_STATIC );


SQLITE_STATIC tells SQLite that the string will outlive the statement
handle. But in fact, you are passing a temporary buffer, deallocated at
the end of the statement. Your program exhibits undefined behavior.


Upon re-reading, that sounds ambiguous. What I meant was, the temporary 
string is deallocated at the end of the C++ statement in which it was 
created - in other words, at the nearest semicolon. It gets pulled right 
from under a live SQLite statement.

--
Igor Tandetnik

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

Re: [sqlite] Weird issue with the query

[Igor Tandetnik]

On 12/2/2016 6:56 PM, Igor Korot wrote:

res = sqlite3_bind_text( stmt, 1,
sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
SQLITE_STATIC );


SQLITE_STATIC tells SQLite that the string will outlive the statement 
handle. But in fact, you are passing a temporary buffer, deallocated at 
the end of the statement. Your program exhibits undefined behavior.


--
Igor Tandetnik

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

Re: [sqlite] Weird issue with the query

[Igor Korot]

On Fri, Dec 2, 2016 at 6:56 PM, Igor Korot  wrote:
> Hi, ALL,
> I wrote following code in C++ (error checking removed for clarity):
>
> [code]
> const std::wstring ::GetTableComments(const
> std::wstring , std::vector )
> {
> std::wstring comment = L"";
> sqlite3_stmt *stmt = NULL;
> std::wstring errorMessage;
> std::wstring query = L"SELECT \"abt_cmnt\" FROM \"sys.abcattbl\"
> WHERE \"abt_tnam\" = ?;";
> int res = sqlite3_prepare_v2( m_db,
> sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(),
> query.length(), , 0 );
> if( res == SQLITE_OK )
> {
> res = sqlite3_bind_text( stmt, 1,
> sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
> SQLITE_STATIC );
> if( res == SQLITE_OK )
> {
> res = sqlite3_step( stmt );
> if( res == SQLITE_ROW )
> {
> comment = sqlite_pimpl->m_myconv.from_bytes( (const
> char *) sqlite3_column_text( stmt, 0 ) );
> }
> [/code]
>
> The trouble is that sqlite3_step() returns 101 (SQLITE_DONE) and not
> 100 (SQLITE_ROW).
>
> Does anyone see an issue with this code:

Also just to clarify:

std::wstring_convert m_myconv;

>
> Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Weird issue with the query

[Igor Korot]

Hi, ALL,
I wrote following code in C++ (error checking removed for clarity):

[code]
const std::wstring ::GetTableComments(const
std::wstring , std::vector )
{
std::wstring comment = L"";
sqlite3_stmt *stmt = NULL;
std::wstring errorMessage;
std::wstring query = L"SELECT \"abt_cmnt\" FROM \"sys.abcattbl\"
WHERE \"abt_tnam\" = ?;";
int res = sqlite3_prepare_v2( m_db,
sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(),
query.length(), , 0 );
if( res == SQLITE_OK )
{
res = sqlite3_bind_text( stmt, 1,
sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1,
SQLITE_STATIC );
if( res == SQLITE_OK )
{
res = sqlite3_step( stmt );
if( res == SQLITE_ROW )
{
comment = sqlite_pimpl->m_myconv.from_bytes( (const
char *) sqlite3_column_text( stmt, 0 ) );
}
[/code]

The trouble is that sqlite3_step() returns 101 (SQLITE_DONE) and not
100 (SQLITE_ROW).

Does anyone see an issue with this code:

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Warren Young]

On Nov 30, 2016, at 6:53 PM, Keith Medcalf  wrote:
> 
> sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)

What’s feq()?  I don’t see it in the SQLite documentation.

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

[sqlite] Scope of sqlite3_update_hook?

[Jens Alfke]

Does a registered sqlite3_update_hook get called when _any_ SQLite connection 
modifies the database, or just the connection it's registered with?

If so, then does that include connections in other OS processes? (I'm looking 
for a way to detect this.)

--Jens [via iPhone]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Chris Locke]

PHP will easily display a value with trailing zeros - you don't add '00'
programmatically.

eg:   $number = number_format(1234, 2, '.', '');

On Thu, Dec 1, 2016 at 8:08 AM, Werner Kleiner 
wrote:

> As I can see storing prices is a topic with different ways and
> different solutions.
>
> The advice to store prices in Cent or Integer:
> Yes you can do: but how will you sore hundredth cents amounts or tenth
> cent prices?
> I have prices like 0,0020 or 0,0008 Euro
>
> I think I have to manipulate the prices for viewing in the app with PHP.
> Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
> stores a price 1.500 from a textfield exact so.
> If you want to show the price again in the app, there is nothing to do.
> But switching to SQLite the price is viewed as 1.5 (and stored)
> I know this is no error of SQLite. But I have now to differ between
> Sqlite and MySQL and have to optimize the SELECT and adding 00
> programmatically to view correct if using Sqlite.
>
> My original post was if there is a way for both DBs with same
> behavior, but it seems not.
>
> Thanks to all for help.
>
> Werner
>
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Stephen Chrzanowski]

Werner ;

In whatever language you use, how do you store a number?  How do you
display that number?  How do you take that set of bytes at a particular
memory location and get it to the users eyeballs?  You have to format it.
Your software converts that list of bytes into something human readable,
because, just looking at #3fc0, I'd have NO clue what that means.

Any language I've used, if you have a float type, and output, just that raw
number, it'll output the minimal, formatted *human readable *number, unless
you use some kind of formatting routine.  var a:real;A:=1.5;writeln(A);
--- Outputs 1.5.

SQLite stores any float/real/decimal number/integer as bytes in a row.  You
won't find "1.5" or "1.500" if you look at the SQLite file in a hex editor
unless you specifically stored it as text, but even then that might not be
true, because the DB engine might be intelligent enough to say "Hey!  This
is a number!  I'll store it as 4 bytes."  SQLite3, will quite literally in
that case, store the number as 4 bytes instead of 6-12 bytes for the
ASCII/UNICODE text version.

#3fc0 is what 1.5 or 1.500 or 1.500 may look
like stored in the database. (That hex number grabbed from
https://www.h-schmidt.net/FloatConverter/IEEE754.html )

From *ANY* tool, what comes OUT of the database to your screen or printer
is a REPRESENTATION of what you want to see.  Its a User Interface thing.
Its making the number "Look Pretty".  Its 100% Aesthetic, for looks,
EXACTLY nothing more, and EXACTLY nothing less.

Whatever you are using, be it the MySQL client, or PHP, or Java, or QBASIC
or whatever you use, when you go and look at that number and that 'tool'
shows you that number, there are a LOT of things that are going on in the
background to show you what #3fc0 is.  In MySQLs case, it apparently
looks at the definition of the column, sees that you've defined it as
something, then will PRESENT you with something that LOOKS like what it is
defined as.  It does NOT change how the number is stored, but just how you
want the output.  Its a superficial thing.

When you do calculations, say, 1.5*1.3, the computer is doing the float
math against 8 bytes, not "1.5" and "1.3".  (Float math breaks my brain,
like Pentiums F-Div bug)

On Fri, Dec 2, 2016 at 7:49 AM, Dominique Devienne 
wrote:

> On Fri, Dec 2, 2016 at 1:01 PM, Werner Kleiner 
> wrote:
>
> > @Darren,
> >
> > my "problem" is, that the both trailing zeros 00 after 1.5 are
> > missing in sqlite. In MySQL the price is stored as 1.500
> >
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Dominique Devienne]

On Fri, Dec 2, 2016 at 1:01 PM, Werner Kleiner 
wrote:

> @Darren,
>
> my "problem" is, that the both trailing zeros 00 after 1.5 are
> missing in sqlite. In MySQL the price is stored as 1.500
>

Do the printf transparently via a view in front of your table? --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (price number);
sqlite> insert into t values (1.5), (0.1), (2.2);
sqlite> create view v as select printf('%7.3f', price) as price from t;
sqlite> select * from v;
  1.500
  0.100
  2.200
sqlite> select typeof(price), price from t;
real|1.5
real|0.1
real|2.2
sqlite> select typeof(price), price from v;
text|  1.500
text|  0.100
text|  2.200
sqlite> drop view v;
sqlite> create view v as select printf('%.3f', price) as price from t;
sqlite> select typeof(price), price from v;
text|1.500
text|0.100
text|2.200
sqlite> select sum(price) from v;
3.8
sqlite> select sum(price) from t;
3.8
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] *** suspected spam or bulk *** Re: Datatype for prices (1, 500)

[Hick Gunter]

Assuming the OPs native language is German:

Lieber Freund, Du hast kein Problem mit der Speicherung sondern mit der 
Darstellung der Werte.

MySQL übernimmt aufgrund der Typangabe "decimal(7,4)" die 
Darstellung/Formatierung der Werte. Vor dem Speichern wird der Wert so 
skaliert, dass immer ganzzahlige Werte entstehen, im konkreten Fall also mal 
1. Aus dem Wert 1.5 wird so 15000. Bei der Anzeige wird der gespeicherte 
Wert durch den Skalierungsfaktor dividiert und an der entsprechenden Stelle ein 
Dezimalpunkt eingefügt.

SQLite hat keinen Typ "decimal". Daher musst Du in Deiner Anwendung diese 
Umrechnung selbst durchführen. Also vor dem speichern mit 1 multiplizieren 
und nach dem lesen durch 1 dividieren.

Da ist keine Hexerei im Spiel. Man muss nur sauber zwischen Wert und 
Darstellung/Formatierung trennen.

Wenn man für mehrere Plattformen mit unterschiedlichen eingebauten 
Funktionalitäten programmiert, dann muss man entweder eine Abstraktionsebene 
einführen, die die Unterschiede verbirgt (d.h. konkret die 
Umrechnung/Formatierung selbst programmieren, falls SQLite als Datenbank 
benutzt wird), oder aber nur die gemeinsamen, auf beiden Plattformen 
verfügbaren Funktionalitäten benutzen (d.h. konkret das Datenmodell in mySQL 
ändern und für beide Fälle immer selbst umrechnen).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Werner Kleiner
Gesendet: Freitag, 02. Dezember 2016 13:01
An: SQLite mailing list 
Betreff: *** suspected spam or bulk *** Re: [sqlite] Datatype for prices (1,500)

@Darren,

my "problem" is, that the both trailing zeros 00 after 1.5 are missing in 
sqlite In MySQL the price is stored as 1.500

I do not undestand exact what you mean with multiply the number?

If I multiply 1.5 x 1 with PHP before storing in sqlite then I get 15000?

best regards
Werner

2016-12-01 11:46 GMT+01:00 Darren Duncan :
> Look, you want to store the same level of detail that a decimal(7,4) does?
>
> Easy, you just multiply the conceptual number by 10,000 and it
> represents hundredths of a cent, the exact same precision you are using in 
> MySQL.
>
> Your examples would then be stored as 20 or 8 respectively.  And every
> other possible value you could store in the MySQL you can now store in
> SQLite, consistently.
>
> -- Darren Duncan
>
> On 2016-12-01 12:08 AM, Werner Kleiner wrote:
>>
>> As I can see storing prices is a topic with different ways and
>> different solutions.
>>
>> The advice to store prices in Cent or Integer:
>> Yes you can do: but how will you sore hundredth cents amounts or
>> tenth cent prices?
>> I have prices like 0,0020 or 0,0008 Euro
>>
>> I think I have to manipulate the prices for viewing in the app with PHP.
>> Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
>> stores a price 1.500 from a textfield exact so.
>> If you want to show the price again in the app, there is nothing to do.
>> But switching to SQLite the price is viewed as 1.5 (and stored) I
>> know this is no error of SQLite. But I have now to differ between
>> Sqlite and MySQL and have to optimize the SELECT and adding 00
>> programmatically to view correct if using Sqlite.
>>
>> My original post was if there is a way for both DBs with same
>> behavior, but it seems not.
>>
>> Thanks to all for help.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

@Darren,

my "problem" is, that the both trailing zeros 00 after 1.5 are
missing in sqlite
In MySQL the price is stored as 1.500

I do not undestand exact what you mean with multiply the number?

If I multiply 1.5 x 1 with PHP before storing in sqlite then I get 15000?

best regards
Werner

2016-12-01 11:46 GMT+01:00 Darren Duncan :
> Look, you want to store the same level of detail that a decimal(7,4) does?
>
> Easy, you just multiply the conceptual number by 10,000 and it represents
> hundredths of a cent, the exact same precision you are using in MySQL.
>
> Your examples would then be stored as 20 or 8 respectively.  And every other
> possible value you could store in the MySQL you can now store in SQLite,
> consistently.
>
> -- Darren Duncan
>
> On 2016-12-01 12:08 AM, Werner Kleiner wrote:
>>
>> As I can see storing prices is a topic with different ways and
>> different solutions.
>>
>> The advice to store prices in Cent or Integer:
>> Yes you can do: but how will you sore hundredth cents amounts or tenth
>> cent prices?
>> I have prices like 0,0020 or 0,0008 Euro
>>
>> I think I have to manipulate the prices for viewing in the app with PHP.
>> Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
>> stores a price 1.500 from a textfield exact so.
>> If you want to show the price again in the app, there is nothing to do.
>> But switching to SQLite the price is viewed as 1.5 (and stored)
>> I know this is no error of SQLite. But I have now to differ between
>> Sqlite and MySQL and have to optimize the SELECT and adding 00
>> programmatically to view correct if using Sqlite.
>>
>> My original post was if there is a way for both DBs with same
>> behavior, but it seems not.
>>
>> Thanks to all for help.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[R Smith]

On 2016/12/01 10:08 AM, Werner Kleiner wrote:

As I can see storing prices is a topic with different ways and
different solutions.

The advice to store prices in Cent or Integer:
Yes you can do: but how will you sore hundredth cents amounts or tenth
cent prices?
I have prices like 0,0020 or 0,0008 Euro

I think I have to manipulate the prices for viewing in the app with PHP.
Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
stores a price 1.500 from a textfield exact so.
If you want to show the price again in the app, there is nothing to do.
But switching to SQLite the price is viewed as 1.5 (and stored)
I know this is no error of SQLite. But I have now to differ between
Sqlite and MySQL and have to optimize the SELECT and adding 00
programmatically to view correct if using Sqlite.

My original post was if there is a way for both DBs with same
behavior, but it seems not.


To add to the excellent explanations of others, firstly, to store exact 
figures you can use Integer and integer divisions or a ULP mechanism of 
the sort Keith mentioned, but there is no native data type that has that 
in SQLite.
Note that MySQL and others that implement the decimal type do not 
actually store values like that, there is no way to store values like 
that, they simply do the formatting for you (pre-calculation, which is 
handy).
That means they store the values as Integer or Float (or perhaps string 
even) and then use internal functions to value/shape it prior to output 
or calculation. It makes those engines "Heavier" than SQLite, and adding 
it here will kill some of the "Lite" in SQLite.


Best advice we can give is: Use floats shaped with Printf() functions to 
display these values, and use a difference epsilon of your comfort level 
in comparisons... And if you insist to have the engine do some of that 
for you, use MySQL in stead.


Good luck!
Ryan

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