[sqlite] string is converted into a number
Hello,
I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems
to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich
is a nice tool.
When I do this:
insert into Queue (NetworkID) values ("200907130833123740007")
Then the result of the field NetworkID is:
2.00907130833124E20
To solve this I add a blank in front of the ID, like this:
insert into Queue (NetworkID) values (" 200907130833123740007")
But is this a good method, and is this a known issue?
The table is created like this:
'create table Queue (' +
'[NetworkID] string, ' +
'[State] integer, ' +
// Etc...
--
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
Hi,
It seems that if I create the table as a field type 'char' instead of
'string' then the issue is solved. But I thought the field type was of non
importance?
rgds, Wilfried
2009/7/13 Wilfried Mestdagh
> Hello,
>
> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems
> to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich
> is a nice tool.
>
> When I do this:
> insert into Queue (NetworkID) values ("200907130833123740007")
>
> Then the result of the field NetworkID is:
> 2.00907130833124E20
>
> To solve this I add a blank in front of the ID, like this:
> insert into Queue (NetworkID) values (" 200907130833123740007")
>
> But is this a good method, and is this a known issue?
>
> The table is created like this:
> 'create table Queue (' +
>'[NetworkID] string, ' +
>'[State] integer, ' +
>// Etc...
>
> --
> mvg, Wilfried
> http://www.mestdagh.biz
>
--
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] string is converted into a number
Hi Dan,
Thank you for your reply. I read:
> Under circumstances described below, the database engine may convert
> values between numeric storage classes (INTEGER > and REAL) and
>TEXT during query execution
But the circumstances are not really described (possible I cannot read
between the lines as my English is not perfect). So as far as I understand
the page if I want to store / retrieve a string (which can be a numeric
string) I have to create my field as "char", "text" or as "none". Is this
correct?
thanks, Wilfried
2009/7/13 Dan
>
> On Jul 13, 2009, at 3:38 PM, Wilfried Mestdagh wrote:
>
> > Hi,
> >
> > It seems that if I create the table as a field type 'char' instead of
> > 'string' then the issue is solved. But I thought the field type was
> > of non
> > importance?
>
> Details here:
>
> http://www.sqlite.org/datatype3.html#affinity
>
>
> >
> >
> > rgds, Wilfried
> >
> > 2009/7/13 Wilfried Mestdagh
> >
> >> Hello,
> >>
> >> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a
> >> string seems
> >> to be converted to a number. To test I use SQLiteSpy from Ralf
> >> Junker wich
> >> is a nice tool.
> >>
> >> When I do this:
> >> insert into Queue (NetworkID) values ("200907130833123740007")
> >>
> >> Then the result of the field NetworkID is:
> >> 2.00907130833124E20
> >>
> >> To solve this I add a blank in front of the ID, like this:
> >> insert into Queue (NetworkID) values (" 200907130833123740007")
> >>
> >> But is this a good method, and is this a known issue?
> >>
> >> The table is created like this:
> >> 'create table Queue (' +
> >> '[NetworkID] string, ' +
> >> '[State] integer, ' +
> >> // Etc...
> >>
> >> --
> >> mvg, Wilfried
> >> http://www.mestdagh.biz
> >>
> >
> >
> >
> > --
> > mvg, Wilfried
> > http://www.mestdagh.biz
> > ___
> > 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
>
--
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] null values
Hello, Just trying sqlLite in Delphi whith the libraries of Tim Anderson. Looks all extremely good to me :) One question: Am I right that the only 'forbitten' value in a stringfield is a NULL ? Probably because the parser only know the end of an SQL statement if it is NULL. I also see I can choose for stringfields to put them between ' and ". Which one is preferable, or is it a matter of taste ? --- Mvg, Wilfried http://www.mestdagh.biz
[sqlite] fragmented tables
Hello, I read in FAQ that SQLite will re-use deleted record space. But what about fragmenting? I mean suppose a record of 1 kb is deleted and a while later a record of 2 kb is add, whill this record then append to the file, or will it fragment partly into the free space ? A question related: To gain the most speed I assume it is best to have most tables continues. Can I reserve space for a table? If no then solution is maybe to create data after creating database and delete all records? Or does this not gain mutch for speed ? 3th and last (for the moment :) I have set page_size to 4 KB as I have read in a wiki article. Does this gain a lot ? --- Mvg, Wilfried http://www.mestdagh.biz
[sqlite] getting only count of records
Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? --- Mvg, Wilfried http://www.mestdagh.biz
Re: [sqlite] getting only count of records
Hi Guillaume and Martin, Thanks for quick responce. Studying the count() function if I use the method of Guillaume I dont need the 'where' clause: 'select count(VehicleID) from tx' But I assume all 3 methods will do internal exact the same. Result is also 1 field with the count in it. --- Mvg, Wilfried http://www.mestdagh.biz
Re[2]: [sqlite] getting only count of records
Hi Arjen, AM> No, they will probably not all do the same internally: the result AM> may be the same, but the "virtual machine" that runs the SQL statements AM> will very probably do different things - this depends on optimisation AM> and so on. But for most of us it is mainly the result that counts AM> (and the time it takes for that result) But what should then be the best one in speed ? --- Rgds, Wilfried http://www.mestdagh.biz
Re[2]: [sqlite] getting only count of records
Hi Guillaume, > if restricting in the where clause on VehicleID, then you should create > an index on VehicleID. I' not sure I understeand correct. Does this mean that if I uses 'where' clauses it is not needed to make indexes and it make index itself when needed ? By now I have made an index on every 'search thing' I use in where clause. Since I dont find instructions to say to the database which index to use, I assume it uses the right ones itself. But when it autocreate indexes... hmm should be nice thing :) Unless of course I misinterprete :( --- Rgds, Wilfried http://www.mestdagh.biz
[sqlite] begin transaction and commit
Hello, Do I need the 'begin transaction' and 'commit' ? It seems it works without them also. I'm not sure I fully understeand wy I need them. If someone can explain in simple English ? I understeand that if I use them, and some serious error occures then there is a rollback so that my application can know the place where is not updated , but do I need it for a simple insert or update of 1 record ? And yes I have read all documentation and FAQ but still have the question... --- Mvg, Wilfried http://www.mestdagh.biz
Re[2]: [sqlite] Time scale on INSERT and UPDATE
Hi, > with updates and selects, i would expect that the time of finding the > record(s) (to update or select) (the where-clause) depends on the size > of the table and on whether indexes are used. With indexing if the database has eg 65535 records, then maximum 17 comparisations. it is one more for power of 2 every time. eg 128 KRecords maximum 18 comparisations etc. --- Rgds, Wilfried http://www.mestdagh.biz
Re: [sqlite] Dotnet C# support
Hi Darren, > Is there a version which will work for dotnot? If not then you can use every win32 dll in C# using P/Invoke --- Rgds, Wilfried http://www.mestdagh.biz
[sqlite] query to get count of fields with different values
Hello, I'm not sure if subject make sence (English is not my mothers tongue), I try to explain: I have a table and a field called 'Name' can have a lot of the same but also different values. I want (if exists of course) to do a query where I only get as result the exclusive names. I give example to make more sence: in table 'Name' field: a a a b b b b c c c In result of query I only want the records (so in this case only 3 records with the 'Name' field: a b c Is this possible ? --- Mvg, Wilfried http://www.mestdagh.biz
Re[2]: [sqlite] query to get count of fields with different values
Hi Guillaume, > select distinct name from table_name; This does exacly what I want ! Thx :) --- Rgds, Wilfried http://www.mestdagh.biz
[sqlite] index question
Hi, I'm a little confused how to create indexes. (beginners question, but I could not find or understeand clearly in FAQ). I have on a table following 3 selects: 'select distinct Name ' + 'from Rx ' + 'where RxDT >= ' + DT + ' ' + 'order by Name' 'select * ' + 'from Rx ' + 'where RxDt >= ' + DT + ' ' + 'order by ID, RxDT desc' 'select * ' + 'from Rx ' + 'where RxDt >= ' + DT + ' and ReportType = ' + RT + ' ' + 'order by ID, RxDT desc' Can you please teach me whitch indexes I exacly have to make ? RxDT is everywhere but I'm not sure if I have to make a separate index for it or that I have to make just 3 separate indexes as I would do with a common TDataSet for example ? Do I have to make separate index for the DESC ? --- Mvg, Wilfried http://www.mestdagh.biz
[sqlite] how make a case insensitive index
Hi, How to make a case insensitieve index ? I tryed with [lower(fieldname)] and other combinations but this give me a syntax error. The select will work with 'order by lower(fieldname)' but it should use the right index. -- View this message in context: http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5047266 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] how make a case insensitive index
Hi, Made an extra field with a lowercase portion of the field I wants to sort on. I think it is the most fast. Of course when someone can give better advice it is very welcome :) rgds, Wilfried -- View this message in context: http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5048042 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] how make a case insensitive index
Thank you for the advice and the url :) -- View this message in context: http://www.nabble.com/how-make-a-case-insensitive-index-t1849023.html#a5049996 Sent from the SQLite forum at Nabble.com.
[sqlite] default value in hex
Hi, How to specify a hexadecimal value in the default by create of a table ? I try thinks like: create table Test ( [Name] char, [Color] integer default 0xFF ); But have syntax errors. When I leave out the 0 then the xFF seems to be stored as a string. it is just that 0xFF makes more sence that 16777215, specially if it is a color. -- View this message in context: http://www.nabble.com/default-value-in-hex-tf1882855.html#a5147011 Sent from the SQLite forum at Nabble.com.
