Insert into data comma separated decimals

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Insert into data comma separated decimals

Post by francof » Fri Mar 04, 2016 3:53 pm

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
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Insert into data comma separated decimals

Post by quailcreek » Fri Mar 04, 2016 4:55 pm

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"
Tom
MacBook Pro OS Mojave 10.14

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Re: Insert into data comma separated decimals

Post by francof » Fri Mar 04, 2016 5:26 pm

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
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Insert into data comma separated decimals

Post by quailcreek » Fri Mar 04, 2016 6:00 pm

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.
Tom
MacBook Pro OS Mojave 10.14

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Re: Insert into data comma separated decimals

Post by francof » Fri Mar 04, 2016 6:26 pm

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Insert into data comma separated decimals

Post by phaworth » Fri Mar 04, 2016 8:01 pm

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
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Re: Insert into data comma separated decimals

Post by francof » Sat Mar 05, 2016 11:56 am

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

AxWald
Posts: 361
Joined: Thu Mar 06, 2014 2:57 pm

Re: Insert into data comma separated decimals

Post by AxWald » Sat Mar 05, 2016 1:35 pm

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!
Livecode programming until the cat hits the fan ...

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Re: Insert into data comma separated decimals

Post by francof » Sat Mar 05, 2016 4:09 pm

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

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Insert into data comma separated decimals

Post by phaworth » Sat Mar 05, 2016 6:12 pm

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
Posts: 361
Joined: Thu Mar 06, 2014 2:57 pm

Re: Insert into data comma separated decimals

Post by AxWald » Mon Mar 07, 2016 11:02 am

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!
Livecode programming until the cat hits the fan ...

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am
Location: Genoa- Italy

Re: Insert into data comma separated decimals

Post by francof » Mon Mar 07, 2016 5:40 pm

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

Post Reply

Return to “Databases”