Page 1 of 1
Strange behavior in Sqlite
Posted: Wed Dec 18, 2013 6:26 pm
by Amarante
I created a table with a field:
lote_area DECIMAL(10, 2)
And I changed part of the SAMPLES\SQL\SQLITE\sql1.prg routine and includes the field type "DECIMAL" (see below)
Code: Select all
IF ! Empty( stmt )
for i := 1 to sqlite3_column_count( stmt )
type1 := sqlite3_column_decltype( stmt,i)
do case
case type1 == "INTEGER" .or. type1 == "REAL" .or. type1 == "FLOAT" .or. type1 == "DECIMAL" // Here includes testing for DECIMAL
aadd(typesarr,"N")
case type1 == "DATE"
aadd(typesarr,"D")
otherwise
aadd(typesarr,"C")
endcase
next i
endif
And when selecting the field it returns me the string content instead of numeric!
Somebody tell if the DECIMAL field type is not recognized?
Re: Strange behavior in Sqlite
Posted: Wed Dec 18, 2013 7:55 pm
by Amarante
Another test:
Insert values of integer and real type, and using the board administrator sqlite command:
SELECT typeof(lote_area) FROM MyBase
Got the results:
integer and
real, ie, the data are correct.
I believe
SQLite3_Column_DeclType function does not recognize the type of field DECIMAL.
Re: Strange behavior in Sqlite
Posted: Thu Dec 19, 2013 12:41 am
by IMATECH
How do I get this info:
Code: Select all
cSQL := "PRAGMA table_info(" + [ oTName ] + ")"
About
sqlite3_column_decltype
SQLite uses dynamic run-time typing. So just because a column is declared to contain a particular type does not mean that the data stored in that column is of the declared type. SQLite is strongly typed, but the typing is dynamic not static. Type is associated with individual values, not with the containers used to hold those values.
http://www.sqlite.org/c3ref/column_decltype.html
Regards
Re: Strange behavior in Sqlite
Posted: Thu Dec 19, 2013 11:30 am
by Amarante
Ronaldo, obrigado por responder.
Eu compreendo bem como o sqlite armezana informações, tanto que eu comentei isso quando falei que inseri valores do tipo inteiro e real.
O estranho é que criei uma tabela com um campo DECIMAL e usando a rotina contida em SAMPLES\SQL\SQLITE\sql1.prg e a função SQLite3_Column_DeclType não retorna nenhum tipo de dado NUMERIC, ou seja, INTEGER ou REAL do campo que declarei como DECIMAL, pelo menos nos meus testes.
Alterei a base para REAL e vou rodar a aplicação de teste e depois informo o resultado.
Abraços,
Daniel
---
[Google Translate]
Ronaldo, thanks for replying.
I understand and sqlite armezana much information I mentioned this when I talked to insert values of integer and real type.
The strange thing is that I created a table with a DECIMAL field using the routine contained in SAMPLES \ SQL \ SQLITE \ sql1.prg and SQLite3_Column_DeclType function does not return any data type NUMERIC, or INTEGER or REAL field declared as DECIMAL, at least in my tests.
I changed the basis for REAL and I will run the test application and then report the result.
hugs,
Daniel
Re: Strange behavior in Sqlite
Posted: Thu Dec 19, 2013 12:21 pm
by Amarante
A quem possa interessar.
Definitivamente a função sqlite3_column_decltype não reconhece um campo declarado como DECIMAL como um campo NUMERIC.
No meu caso eu resolvi declarando o campo como REAL.
---
To whom it may concern.
Definitely sqlite3_column_decltype function does not recognize a field declared as DECIMAL as NUMERIC field.
In my case I decided to declare the field as REAL.
Re: Strange behavior in Sqlite
Posted: Thu Dec 19, 2013 3:30 pm
by IMATECH
Hi Amarante
Using a tool like:
http://www.sqliteexpert.com/
This program show data in diferent colors, then you can see data types in a easy way ( visually )
Then... after this step, do you have the same info retrieved from your tests ?
Best regards
Re: Strange behavior in Sqlite
Posted: Thu Dec 19, 2013 4:01 pm
by Amarante
IMATECH wrote:Hi Amarante
Using a tool like:
http://www.sqliteexpert.com/
This program show data in diferent colors, then you can see data types in a easy way ( visually )
Then... after this step, do you have the same info retrieved from your tests ?
Best regards
Ronaldo,
Obrigado pelo link.
O problema está na função sqlite3_column_decltype() do HMG ou Harbour que não devolve corretamente o tipo de campo quando este está definido como DECIMAL.
Felizmente eu não estou com problemas com os dados, meu problema era que usando sqlite3_column_decltype() eu não recebia corretamente o tipo do campo, mas resolvi trocando o tipo do campo para REAL.
Vlw pela atenção.