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