[sqlite] Compile error with certain compile time options
Hello, I am trying to build a specific static library for an embedded device running "arm-linux-gnueabi" sqlite 3.26.0 I have tried to try some compile time parameters first. I passed some omit options to autoconfig in order to reduce file size. I am getting warnings and finally an error. I cannot see if it is my mistake or not. Gcc version is 6.3.0. My configure command line is: ./configure --disable-threadsafe --disable-tcl CFLAGS="-g -Os -DSQLITE_OMIT_ALTERTABLE -DSQLITE_OMIT_AUTOVACUUM -DSQLITE_OMIT_EXPLAIN -DSQLITE_OMIT_FOREIGN_KEY -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_BLOB_LITERAL -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_ATTACH -DSQLITE_DOESNT_MATCH_BLOBS -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_OMIT_DEPRECIATED -DSQLITE_OMIT_DECLTYPE -DSQLITE_MAX_EXPR_DEPTH=0" I have passed " -DSQLITE_OMIT_ATTACH" in my configure parameters and even though one of the warnings I receive is: sqlite3.c:18985:21: warning: 'sqlite3Attach' used but never defined I did not want to paste all output in here. Complete output can be found: https://pastebin.com/qGaD0TkH I also want to disable all FTS and RTREE as I also see them compiled in, but cannot be sure what option I can use to disable. Any help is appreciated. Thanks & regards, Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 on WinCE platform
>From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On >Behalf Of Simon Slavin >Sent: Monday, December 11, 2017 12:50 PM >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] SQLite3 on WinCE platform > >On 11 Dec 2017, at 8:59am, Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr> wrote: > >> Error I am getting when application runs is: EInOutError: Can not load >> SQLite client library "sqlite3ce.dll". Check your installation. > > Given this page > > <https://www.freepascal.org/docs-html/rtl/sysutils/einouterror.html> > > "EInOutError is raised when a IO routine of Free Pascal returns an error. The > error is converted to an EInOutError only if the input/output checking > feature of FPC is turned on. The error code of the input/output operation is > returned in ErrorCode." > > Can you look at the error code in 'ErrorCode' ? > > Or can you turn off the input/output checking feature of FPC ? > > Simon. Finally mystery solved. Even Motorola device seems to be using more or less same version of WinCE, and having ARMV5 supported CPU it turned out that DLL has to be bare ARM compiled and not ARMV4i. Thanks for all the help. Ertan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3 on WinCE platform
Hello, I am using SQLite3 on handheld barcode scanners. Development platform of my choice is Lazarus (free pascal). These handheld devices run WinCE operating system. I have found a sqlite3ce.dll which I have downloaded from Lazarus forums. That DLL is version 3.9.2.0 compiled for WinCE 5+ (armv4i). I am happy that I can use sqlite on WinCE. However, WinCE platform has a lot of different versions and naming conventions are used. Moreover, I cannot use same DLL on every WinCE version. In particular: - DSC5 handheld device running Microsoft Windows CE Version 6.0 uses sqlite.dll version 3.9.2.0 - Intermec CK3R handheld device running CE OS 5.2.29354 uses sqlite.dll version 3.11.1.0 - Motorola MC55A handheld device running CE OS 5.2.29040: I cannot use any of above dlls. I get help from a person at Lazarus forum to compile sqlite3 for WinCE. I do not know how to do that myself. This person and I had a TeamViewer session for several hours. He tried a lot of different options for sqlite compilation parameters and none of them worked on that Motorola device. Error I am getting when application runs is: EInOutError: Can not load SQLite client library "sqlite3ce.dll". Check your installation. File access is not blocked at all. Application can read it for sure. It doesn't help trying to load the DLL in any way possible (static, dynamic). It seems like a operating system thing that we could not understand. I write in here with the hope that someone who knows what the problem is/might can help me out. Thanks & regards, Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Searching sqlite3dll for WinCE 5.2 aka Windows Embedded Handheld 6.5 Classic
Hello, I have an application developed with Lazarus. I already have a sqlite3.dll v3.9.2 for CE which works just fine under OS "Microsoft Windows CE Version 6.0" for several years. Now, I have to distribute same application on Windows Embedded Systems. But; 1) CE OS 5.2.29354 device: Intermec CK3R, runs fine for first time after a device boot and then gives me error saying "Can not load SQLite client library "sqlite3.dll", Check your installation.". 2) CE OS 5.2.29040 device: Motorola MC55A, runs fine for first time after a device boot. Then gives same error error for odd run trials number, and runs fine for even run trials. Both devices; if application is run. It runs perfectly. No issues at all. I am just searching for another sqlite3 DLL compiled for CE systems to understand if this problem is because of DLL, or Lazarus, or something else. Any help is appreciated. Thanks. Regards, Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] running queries on attached database tables
Hi, Assume that there are two databases as follows: DatabaseA -Table1 -Table2 DatabaseB -Table3 My application is normally working on DatabaseA. I run an attach command for DatabaseB like: Attach DatabaseB as source After a successful attach. If I want to select something from DatabaseB-Table3 is it enough to call below SQL? Select * from Table3 Or, even if there is no table names that overlap on each other, Do I still need to run something as follows: Select * from source.Table3 Thanks. Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL help on preparing a report
Hello, I am developing an application with SQLite. There is this report and my SQL knowledge is not good enough to cope with it. I do not want to do all these calculations by regular select statements and writing an application code. Details; I have below two tables in the same SQLite database file: CREATE TABLE IF NOT EXISTS FIS( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, UniqueIDChar(48) not null COLLATE NOCASE, EKU integer not null check (EKU > 0), ZNo integer not null check(ZNo > 0), FNo integer not null check(FNo > 0), Tarih date not null, Saattime not null, Zaman datetime not null, Barkod Char(10) COLLATE NOCASE NOT NULL, KdvOran Floatnot null check(KdvOran >= 0), Miktar Floatnot null, Birim Char(10) COLLATE NOCASE, OrjinalBirimFiyat Floatnot null check(OrjinalBirimFiyat >= 0), KartBirimFiyat Floatnot null check(KartBirimFiyat >= 0), TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0), IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0), PromosyonFiyat Floatnot null check(PromosyonFiyat >= 0), BirimFiyat Floatnot null check(BirimFiyat >= 0), KDVTutarFloatnot null, Tutar Floatnot null, Kasiyer Char(20) not null COLLATE NOCASE, OturumIDSmallInt not null, Vardiya SmallInt check(Vardiya > 0), VeresiyeKoduChar(20) COLLATE NOCASE, POSSeriNo Char(50) not null COLLATE NOCASE, POSFisYazmaSuresi integer not null check(POSFisYazmaSuresi >= 0) ); CREATE TABLE IF NOT EXISTS FISODEME( IDInteger NOT NULL PRIMARY KEY AUTOINCREMENT, EKU integer not null check (EKU > 0), ZNo integer not null check(ZNo > 0), FNo integer not null check(FNo > 0), Tarih date not null, Saat time not null, Zaman datetime not null, OdemeTuru Smallint not null check(OdemeTuru in (1, 2, 3)), KDVTutari Float not null check(KDVTutari >= 0), FisToplamifloat not null check(FisToplami >= 0), OdemeMiktari float not null check(OdemeMiktari >= 0), ParaUstu float not null check(ParaUstu >= 0), Banka Char(20) COLLATE NOCASE, VeresiyeKodu Char(20) COLLATE NOCASE, POSSeriNo Char(50) not null COLLATE NOCASE ); It is not easy for me to explain the report by written words. So, I prepared an excel file with 3 sheets. First two contains raw data from above tables. Last one is the report itself. Please ignore yellow marked columns in raw data sheets. I am quite open to add new table(s) for reporting purposes if that makes the report possible/easier. It doesn't need to be a single SQL. Running several SQL statements is not a problem at all. However, I do not want to deal with 10s of SQL statements if it can be done for example with only 3. It will be something as 10s of statements If I am to deal with it using my own knowledge. I appreciate any help. Regards, Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert into not inserting float values as expected
Hi everyone, It turns out to be my mistake. I did find out that Values in FIS table do really has a value of 6.0. Sorry for all the fuss. Thanks. -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ertan Küçükoğlu Sent: Saturday, July 16, 2016 12:32 PM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] insert into not inserting float values as expected Hello, I have below table definitons: CREATE TABLE IF NOT EXISTS FIS( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, Barkod Char(20) COLLATE NOCASE NOT NULL, BarkodAciklamasiChar(50) COLLATE NOCASE, UrunKoduChar(20) COLLATE NOCASE, UrunAciklamasi Char(50) COLLATE NOCASE, KisaAd Char(15) COLLATE NOCASE, GrupKoduChar(20) COLLATE NOCASE, KdvOran Integer not null check(KdvOran >= 0), Miktar Floatnot null, Birim Char(10) not null COLLATE NOCASE, OrjinalBirimFiyat Floatnot null check(OrjinalBirimFiyat >= 0), KartBirimFiyat Floatnot null check(KartBirimFiyat >= 0), TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0), IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0), PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0), BirimFiyat Floatnot null check(BirimFiyat >= 0), Tutar Floatnot null, Kasiyer Char(20) not null COLLATE NOCASE, OturumIDSmallInt not null ); CREATE TABLE IF NOT EXISTS FISYAZDIR( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, Barkod Char(20) COLLATE NOCASE NOT NULL, BarkodAciklamasiChar(50) COLLATE NOCASE, UrunKoduChar(20) COLLATE NOCASE, UrunAciklamasi Char(50) COLLATE NOCASE, KisaAd Char(15) COLLATE NOCASE, GrupKoduChar(20) COLLATE NOCASE, KdvOran Integer not null check(KdvOran >= 0), Miktar Floatnot null, Birim Char(10) not null COLLATE NOCASE, OrjinalBirimFiyat Floatnot null check(OrjinalBirimFiyat >= 0), KartBirimFiyat Floatnot null check(KartBirimFiyat >= 0), TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0), IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0), PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0), BirimFiyat Floatnot null check(BirimFiyat >= 0), Tutar Floatnot null, Kasiyer Char(20) not null COLLATE NOCASE, OturumIDSmallInt not null ); I have following records for FIS table: Barkod Miktar BirimFiyat Tutar 1005 1 6.5 6.5 1005 1 6.5 6.5 1005 1 6.5 6.5 1005 -1 6.5 -6.5 I am running below query when fisyazdir table is completely empty: insert into fisyazdir select null,Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran , Sum(Miktar), Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat, PromosyonBirimFiyat,BirimFiyat, sum(Tutar), Kasiyer,OturumID from FIS group by Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran, Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat, PromosyonBirimFiyat,BirimFiyat, Kasiyer,OturumID Result in fisyazdir is something like: Barkod Miktar BirimFiyat Tutar 1005 2 6.5 13 1005 -1 6 -6.5 I expected BirimFiyat to be 6.5, but it is not. Is it me doing something wrong? I cannot understand the reason. Thanks. Ertan Küçükoğlu ___ 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
[sqlite] insert into not inserting float values as expected
Hello, I have below table definitons: CREATE TABLE IF NOT EXISTS FIS( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, Barkod Char(20) COLLATE NOCASE NOT NULL, BarkodAciklamasiChar(50) COLLATE NOCASE, UrunKoduChar(20) COLLATE NOCASE, UrunAciklamasi Char(50) COLLATE NOCASE, KisaAd Char(15) COLLATE NOCASE, GrupKoduChar(20) COLLATE NOCASE, KdvOran Integer not null check(KdvOran >= 0), Miktar Floatnot null, Birim Char(10) not null COLLATE NOCASE, OrjinalBirimFiyat Floatnot null check(OrjinalBirimFiyat >= 0), KartBirimFiyat Floatnot null check(KartBirimFiyat >= 0), TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0), IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0), PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0), BirimFiyat Floatnot null check(BirimFiyat >= 0), Tutar Floatnot null, Kasiyer Char(20) not null COLLATE NOCASE, OturumIDSmallInt not null ); CREATE TABLE IF NOT EXISTS FISYAZDIR( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, Barkod Char(20) COLLATE NOCASE NOT NULL, BarkodAciklamasiChar(50) COLLATE NOCASE, UrunKoduChar(20) COLLATE NOCASE, UrunAciklamasi Char(50) COLLATE NOCASE, KisaAd Char(15) COLLATE NOCASE, GrupKoduChar(20) COLLATE NOCASE, KdvOran Integer not null check(KdvOran >= 0), Miktar Floatnot null, Birim Char(10) not null COLLATE NOCASE, OrjinalBirimFiyat Floatnot null check(OrjinalBirimFiyat >= 0), KartBirimFiyat Floatnot null check(KartBirimFiyat >= 0), TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0), IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0), PromosyonBirimFiyat Floatnot null check(PromosyonBirimFiyat >= 0), BirimFiyat Floatnot null check(BirimFiyat >= 0), Tutar Floatnot null, Kasiyer Char(20) not null COLLATE NOCASE, OturumIDSmallInt not null ); I have following records for FIS table: Barkod Miktar BirimFiyat Tutar 1005 1 6.5 6.5 1005 1 6.5 6.5 1005 1 6.5 6.5 1005 -1 6.5 -6.5 I am running below query when fisyazdir table is completely empty: insert into fisyazdir select null,Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran , Sum(Miktar), Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat, PromosyonBirimFiyat,BirimFiyat, sum(Tutar), Kasiyer,OturumID from FIS group by Barkod,BarkodAciklamasi,UrunKodu,UrunAciklamasi,KisaAd,GrupKodu,KdvOran, Birim,OrjinalBirimFiyat,KartBirimFiyat,TeraziBirimFiyat,IndirimliBirimFiyat, PromosyonBirimFiyat,BirimFiyat, Kasiyer,OturumID Result in fisyazdir is something like: Barkod Miktar BirimFiyat Tutar 1005 2 6.5 13 1005 -1 6 -6.5 I expected BirimFiyat to be 6.5, but it is not. Is it me doing something wrong? I cannot understand the reason. Thanks. Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Currency & SQLite
Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM, regular
Laptop HDD (no SSD). Time to time, there will be calculations, Network file
transfer, and DLL function calls (all three) will be simultaneously running.
Application has Timers and Socket listening components on it. My concern is to
have a situation which "seems" like an application freeze.
On the other hand, will it be any help to increase accuracy to use Currency (8
bytes fixed-point data type) in my application for Price and Total fields,
leave Amount field as Double?
Thanks.
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Wednesday, July 6, 2016 1:20 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Currency & SQLite
On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote:
> I am using Delphi 10 for programming. Current variables are defined as Double
> (8 bytes real), saved in Database using something
> "DataSet.FieldByName('Amount').AsFloat := My_double_variable" However, I am
> now considering to convert all Double datatypes to Single (4 bytes real) for
> performance issues.
Do NOT change to using Single, always use Double, end to end.
First of all, Double is what SQLite uses internally for floats, per its
documentation:
"The value is a floating point value, stored as an 8-byte IEEE floating
point number."
So if you're already using Double in your app then staying that way should
maintain accuracy, especially when you want accuracy for more than say 3-5
decimal digits in total.
Secondly, for financial applications, accuracy trumps performance. Converting
to Single at any time is just going to cause you trouble.
And really, are you sure using Single would actually help performance? Have
you measured it in a real workload? More likely on modern devices it won't be
any faster and you will have lost your accuracy for nothing.
-- Darren Duncan
___
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] Currency & SQLite
Hello Darren,
Thanks for your suggestions. Sorry, my e-mail client is not capable of
indenting messages the way yours does. Just bear with my e-mails, please.
I am communicating with a device which actually prints commercial receipt. That
device is already confirmed by Ministry of Finance. What I am doing will be
linked to VAT. Not that much directly, but still there will be a relation for
the company which uses my software.
This device, I am communicating thru a C# DLL. I have to provide amount and
price separately to the functions. Device uses banker's rounding to calculate
total which is printed on the receipt. Device stores values in itself, I store
my values in the SQLite database. My database stored values have to be
identical with the device stored values.
There are some sectors using that device which uses high decimal digits for
amount values. That's why I want to store amount with high decimal digits.
Price on the other hand is fixed with 2 decimal digits.
However, since I have to be identical with the device;
Amount_device * price_device = total_device
Total_device = banker's rounding to 2 decimal digits
Total_device have to be identical to total_SQLite_database
I am using Delphi 10 for programming. Current variables are defined as Double
(8 bytes real), saved in Database using something
"DataSet.FieldByName('Amount').AsFloat := My_double_variable" However, I am now
considering to convert all Double datatypes to Single (4 bytes real) for
performance issues.
There will be reports generated using data stored in SQLite database. I will
depend on using SQL, mostly SUM() will be run on these fields that is expected
to be same with device itself stored values.
I do want to store Dollars because, I realized that I might be doing something
wrong with SQLite field definitions, today. Application is about 95% complete
as to interaction with database. Unfortunately, converting everything in the
application code to use Cents instead of Dollars might introduce me quite a
good number of bugs.
Regards,
Ertan Küçükoğlu
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Wednesday, July 6, 2016 12:21 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Currency & SQLite
Hello Ertan,
On 2016-07-05 1:59 PM, Ertan Küçükoğlu wrote:
> I have following data to store:
>
> My currency values only has 2 decimal digits. (Sample: 1.23)
>
> My amount values goes around 5-6 decimal digits. (Sample: 1.123456)
So what do the 2 fields actually represent? Are Amount and Money both amounts
of currency? If so, why are they stored to different precisions? The field
names should be more precise.
> If I am to use below SQL command:
>
> CREATE TABLE IF NOT EXISTS ACCOUNTING(
>
>ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>
>AmountField float not null,
>
>MoneyField float not null
>
> );
>
> Will it be fine to store these values, and I will retrieve exactly
> what I save?
>
> Stored 1.123456 and retrieved 1.123456 exactly, no rounding or anything.
A float field will never store a fractional decimal value exactly, what it will
store is just a binary approximation. However, if the precision you need is
low enough relative to the size of the float then it may appear to work.
A key thing here is how you represent the values while they are being
transmitted between SQLite and your application. If you are storing them using
runtime-bound placeholders rather than literal SQL, and your placeholders are
also floats of the same precision SQLite uses, then you can be guaranteed that
whatever your application gave to SQLite, it will get back. If the values are
inserted as part of the SQL string then it will be rounded.
Its also important to know if you are doing any math with these values in SQL,
eg sums, or if you are just storing and retrieving. Inexact values would bite
you mainly if you do math.
> One thing I cannot do in my case is to store money values as integers
> multiplied by 100.
And why not? That often is still the best advice.
Really its a matter of conception, what is the unit of the currency?
If say your currency was dollars, just say that the field is storing the number
of cents rather than the number of dollars, and do all your math assuming it is
adding/multiplying/etc counts of cents. Nothing wrong with that. Cents are
still just as valid currency in the real world.
Another thing you can do is store the numbers as text fields instead, which
works perfectly with situations where you input them literally in a SQL string;
in appearance you can put the decimal point whereever you want and the database
will remember it exactly, this even works with hundreds of decimal digits or
something over the bounds of
[sqlite] Currency & SQLite
Hi, I read on the web, but cannot be sure about it. So, question goes here. I have following data to store: My currency values only has 2 decimal digits. (Sample: 1.23) My amount values goes around 5-6 decimal digits. (Sample: 1.123456) If I am to use below SQL command: CREATE TABLE IF NOT EXISTS ACCOUNTING( ID Integer NOT NULL PRIMARY KEY AUTOINCREMENT, AmountField float not null, MoneyField float not null ); Will it be fine to store these values, and I will retrieve exactly what I save? Stored 1.123456 and retrieved 1.123456 exactly, no rounding or anything. One thing I cannot do in my case is to store money values as integers multiplied by 100. I appreciate all advises. Thanks. Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding a single row
Hi Simon, Two or more rows return is an error on user definition. I will popup a message in this case. Thanks for the example, I didn't know <= can be used also for strings. Regards, Ertan Küçükoğlu -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, May 26, 2016 1:55 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Finding a single row On 26 May 2016, at 11:44am, Ertan Küçükoğlu <ertan.kucuko...@1nar.com.tr> wrote: > I need to find a single row in my table which begins as the complete > card number, or I need to know no match exists in my table. What happens if two rows match ? Ignoring that question for a while, what you're looking for is the row which sorts immediately before the complete card code you're searching for. SELECT Kodu FROM FIYATKODLARI WHERE Kodu <= '' ORDER BY Kodu DESC LIMIT 1 In your programming langauge take a look at the value returned and see if it is the same as the first n characters of the value you're searching for. If it is, you have a match. If not, you don't. 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
[sqlite] Finding a single row
Hi, I have a table where I keep some part from the beginning of discount card number. Length of beginning is variable. User provides a complete card number at runtime, I need to find a single row in my table which begins as the complete card number, or I need to know no match exists in my table. I might do it coding in my application, but that would be way too slow compared to an SQL statement I assume. Table is as follows. CREATE TABLE IF NOT EXISTS FIYATKODLARI( Kodu Char(30) COLLATE NOCASE NOT NULL PRIMARY KEY )WITHOUT ROWID; It contains something like: Kodu SA SB SSC SAA SBDE 8694 125 Complete card number may be something like: SB1 SA25 SA325 SB1054 SAA23 86941005 12504 Below didn't work for me because complete card number is longer, and I do lack SQL knowledge: select * from FIYATKODLARI where Kodu like 'SB1%' Any help is appreciated. Thanks. Ertan Küçükoğlu ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID
Hello, I wonder if there are any drawbacks/things to be aware of, etc. for tables created "WITHOUT ROWID". I am considering using such tables in another project. Thanks. Regards, Ertan K???ko?lu
[sqlite] Is it faster with descending index
I prepared a test application on Windows. Used sqlite 3.12.2 32bit DLL. - Just one table in a database. - Database1, Table created with below SQL and second database I included WITHOUT ROWID at the end when creating the test table. - Application filled in 500.000 random value generated records in first database1. - Application generated exactly field size random value strings for each field separately. (Char(30) field, value inserted with 30 characters, Char(60) field, value inserted with 60 characters and so on) - Application generated strings between Chr(32) - Chr(90) in ASCII table, so nothing non-printable inserted. - Application select all rows from database1 & inserted to database2. - Test showed me having insert values ready is faster than generating insert values. As a result database2 size is about 5.2% smaller. I do not know how faster queries will be in that small database but still a gain. Table SQL: CREATE TABLE URUN( Barkod Char(30) NOT NULL PRIMARY KEY, UrunKodu Char(50), UrunAciklamasi Char(60), UrunGrubu Char(30), Renk Char(20), Beden Char(20), Cup Char(20), OlcuBirimi Char(10), AlisFiyat Char(20), SatisFiyat Char(20) ); Thanks. Ertan K???ko?lu -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 6, 2016 12:51 AM To: SQLite mailing list Subject: Re: [sqlite] Is it faster with descending index On 5/5/16, Ertan K???ko?lu wrote: > > Table create SQL: > CREATE TABLE URUN( > Barkod Char(30) NOT NULL PRIMARY KEY, ... > ); > > My question is, if I add following index, will it make my searches faster? > > CREATE UNIQUE INDEX IDX_BARKOD_DESC ON URUN(BARKOD); No. But if you change your table schema to read: CREATE TABLE URUN( Barkod char(30) PRIMARY KEY, ) WITHOUT ROWID; Then it might well generate a smaller and faster database. It seems like it is worth a try, anyhow. -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it faster with descending index
Hi,
I am using SQLite version 3.9.2 on WinCE devices. I have below table in one
of my databases. Table sometimes holds 1.5 to 3 million records in it. That
database is reached for read-only purposes and created from scratch in a PC
if its contents needs to be updated. All database files, application, and
some application related files are on a microSD card. Brand and size is
changing from device to device.
Database opened using below pragmas:
PRAGMA page_size=4096;
PRAGMA journal_mode=MEMORY;
PRAGMA temp_store=2; // Memory temp store
PRAGMA locking_mode=EXCLUSIVE;
Table create SQL:
CREATE TABLE URUN(
Barkod Char(30) NOT NULL PRIMARY KEY,
UrunKodu Char(50),
UrunAciklamasi Char(60),
UrunGrubu Char(30),
Renk Char(20),
Beden Char(20),
Cup Char(20),
OlcuBirimi Char(10),
SeriNo Char(1),
Lot Char(1),
SKT Char(1)
);
My question is, if I add following index, will it make my searches faster?
CREATE UNIQUE INDEX IDX_BARKOD_DESC ON URUN(BARKOD);
Application is developed using Lazarus 1.6. Table is opened in a TSQLQuery
and searches coded using "TSQLQuery.Locate('Barkod', Search_Value, []);"
Thanks & Regards,
Ertan K???ko?lu
