Insert into data comma separated decimals

[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

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

[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

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

[francof]

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

[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 "/")

[francof]

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 "/" ?

[AxWald]

Hi,

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!

[francof]

Hi AxWald,

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

[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

[AxWald]

Hi,

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.

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!

[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