Page 1 of 1
Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 3:53 pm
by francof
Hi all,
I can have in some fields and variables numeric values with comma separated decimals insted by dot (I begin to think this is not a great idea).
the code below puts the values into a variable to use in the creation of the sql for insert into operation:
Code: Select all
put gCodViti, fld "fldDate", gAlcolProb into tColumnItems
ie: if gCodViti = 1
fldDate = 04/03/2016
gAlcolProb = 12,5
tColumnItems contain 4 items (1,04/03/2016,12,5) considering the value of gAlcolProb as 2 different items (12 and 5)
I've tried to use quotes or single quote to group, delimit this data without success.
my only idea is to replace all commas with dots
franco
Re: Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 4:55 pm
by quailcreek
So, you're wanting to write tColumnItems to the Db as a single string? This is what I use to write to the DB using an array. You should be abbe to adapt it to your needs.
Code: Select all
put theDescription into SQLArray[1]
put theCode into SQLArray[2]
put theManuf into SQLArray[3]
put theNotes into SQLArray[4]
put "INSERT into " & theCollection & "(theDescription, bCodeNum, manufNum, Notes) "&\
"VALUES (:1,:2,:3,:4)" into tSQLStatement
revExecuteSQL the uDatabaseID of this stack, tSQLStatement, "SQLArray"
Re: Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 5:26 pm
by francof
Hi Tom,
thanks for your code.
I've adapted it to my data and works fine.
I found my version in a LC lesson and that problem drives me crazy.
ciao
franco
Re: Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 6:00 pm
by quailcreek
You're welcome, franco. I learned this technique from the other members on the forum. If you do a lot of sql code you will also want to learn about BEGIN, ROLLBACK and COMMIT.
Re: Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 6:26 pm
by francof
quailcreek wrote:You're welcome, franco. I learned this technique from the other members on the forum. If you do a lot of sql code you will also want to learn about BEGIN, ROLLBACK and COMMIT.
I will take a look at these.
going a bit OT, I've read that, working with SQLite, is a good thing to save dates in yyyy/mm/dd format to be able to take advantage of SQLite functionality.
I'm just started to create my db and I could change the date formate without problem
ciao
franco
Re: Insert into data comma separated decimals
Posted: Fri Mar 04, 2016 8:01 pm
by phaworth
You'll have to be careful when SELECTing your data since the default separator for the columns in revDataFromQuery is a comma so your 12,5 will look like two columns in the data coming back from revDataFromQuery. Fortunately, you can specify the column delimiter to use in revDataFromQuery:
put revDataFromQuery(tab,,.....
That will separate the column data coming back from the SELECT with tabs instead of a comma.
Also, the SQLite date format you want is YYYY-MM-DD ("-" as the separator not "/")
Re: Insert into data comma separated decimals
Posted: Sat Mar 05, 2016 11:56 am
by francof
phaworth wrote:You'll have to be careful when SELECTing your data since the default separator for the columns in revDataFromQuery is a comma so your 12,5 will look like two columns in the data coming back from revDataFromQuery. Fortunately, you can specify the column delimiter to use in revDataFromQuery:
put revDataFromQuery(tab,,.....
That will separate the column data coming back from the SELECT with tabs instead of a comma.
Also, the SQLite date format you want is YYYY-MM-DD ("-" as the separator not "/")
Hi phaworth,
You saw in the future, thanks. indeed, I do not have still thought at the read of data from the db. and yes, you are right, I will have the same trouble of false items due to the comma.
I must see more about "put revDataFromQuery(tab,,....."
about date format, do you tell me why "-" as the separator instead of "/" ?
Re: Insert into data comma separated decimals
Posted: Sat Mar 05, 2016 1:35 pm
by AxWald
Hi,
francof wrote:about date format, do you tell me why "-" as the separator instead of "/" ?
SQLite likes it this way ;-) Check
the doku.
I recently worked with SQLite & datetimes, and found it a lot of hassle. Especially since I'm in Europe, and LC's date & time functions are mostly anglo-centric. I ended in using the dateItems, and in converting forth and back.
To get the dateItems from SQLite:
Code: Select all
"SELECT strftime('%Y,%m,%d,%H,%M,%S,%w',Table.Field) FROM Table"
Hint: don't rely on the last item, numeric day - different in EU/Anglo too!
For the conversions I used this:
Code: Select all
function DI2Sys DI
-- DateItems -> 23.03.2016 12:44
set the itemdelimiter to ","
return item 3 of DI & "." & item 2 of DI & "." & item 1 of DI & " " & item 4 of DI & ":" & item 4 of DI
end DI2Sys
function Sys2DI MyTime
-- 23.03.2016 12:44 -> DateItems
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "," & item 2 of TheDate & "," & item 1 of TheDate & "," into MyVar
set the itemdelimiter to ":"
put item 1 of TheTime & "," & item 2 of TheTime & ",00,0" after MyVar
return MyVar
end Sys2DI
function Sys2SQL MyTime
-- 23.03.2016 12:44 -> 2016-03-23 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "-" & item 2 of TheDate & "-" & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end Sys2SQL
function SQL2SYS MyTime
-- 2016-03-23 12:44:00 -> 23.03.2016 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "-"
put item 3 of TheDate & "." & item 2 of TheDate & "." & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end SQL2Sys
So I do this in my stack:
- I show the system date/time (european, SYS)
- I calculate with the dateItems (DI)
- And when writing to SQLite, I use a format it likes (SQL) :)
I'm sure there's a more elegant way, though ...
Have fun!
Re: Insert into data comma separated decimals
Posted: Sat Mar 05, 2016 4:09 pm
by francof
Hi AxWald,
AxWald wrote:...
LC's date & time functions are mostly anglo-centric...
I saw!
I was thinking to store dates into db in YYYY-MM-DD format and converting them when back into DD/MM/YYYY (my usual format).
the code
Code: Select all
"SELECT strftime('%Y,%m,%d,%H,%M,%S,%w',Table.Field) FROM Table"
is it used in a common SELECT statement, together others data fields?
ciao
franco
Re: Insert into data comma separated decimals
Posted: Sat Mar 05, 2016 6:12 pm
by phaworth
AxWald - nice handlers. Might need to take account of single digit day and month numbers unless you always force them to have leading zeros internally.
Definitely in favor of using strftime in your SELECT statements, make SQLite do the work for you instead of writing LC code to do it.
Pete
Re: Insert into data comma separated decimals
Posted: Mon Mar 07, 2016 11:02 am
by AxWald
Hi,
francof wrote:Code: Select all
"SELECT strftime('%Y,%m,%d,%H,%M,%S,%w',Table.Field) FROM Table"
is it used in a common SELECT statement, together others data fields?
For sure. I omitted the "WHERE" part due to sheer laziness ;-)
Btw, be careful here not to use:
Code: Select all
get revDataFromQuery(comma, return, tConnectionId, tQuery)
as in the dictionary - Comma will fail in the above example, and Return doesn't work well with multi-line text records.
Code: Select all
get revDataFromQuery(numtochar(1), Numtochar(2), tConnectionId, tQuery)
seems to work quite well and should be rather robust.
phaworth wrote:Might need to take account of single digit day and month numbers unless you always force them to have leading zeros internally.
Hehe, this is part of a Q&D job, and I left it as soon as it worked on my machine - Win7-64 german. You have a point here, I should use something like:
Code: Select all
put format(%02s, item 3 of TheDate) & "." & format(%02s, item 2 of TheDate) & "." & item 1 of TheDate & " " into TheDate
(from: 2016-03-23 12:44:00 -> 23.03.2016 12:44)
Have fun!
Re: Insert into data comma separated decimals
Posted: Mon Mar 07, 2016 5:40 pm
by francof
Hi AxWald,
I will made some tests on that string
about of single digit day and month I used this:
Code: Select all
set the itemDelimiter to "/"
if the number of chars OF item 1 of tData = 1 then
put 0 BEFORE item 1 of tData
end if
if the number of chars OF item 2 of tData = 1 then
put 0 BEFORE item 2 of tData
end if
best
franco