Inserting into SQL Database, Formatting Issues

[Jon Melin]

Hello,

I am taking elements of a message and inserting it into a SQL database and am having a couple minor issues with how things are formatted.

First off, how are people dealing with commas in name fields. The destination database just has a full name column 9not fname, lname, etc). but I’m receiving a name with a comma in it such as:

Doe, John A

—-When Cloverleaf does it’s insert, it sees the comma and puts Doe in one column and John A in another. I could look for the comma and splice first and last together without a comma, but I think there is an easier solution I may be missing (I’ve tried quotes)

My second issue is, I am looking to insert the current date in the last column of the database, but I keep getting a random $ symbol at the end that causes to date to be unknown, thus SQL defaults it to 1900. I am not adding this symbol, I can’t trim it, so where is it coming from and does anyone know how I can prevent it. (global?)

I’m using this to get the date, which works exactly how I want it to:

Code:

set systemTime [clock seconds]
set xlateOutVals [clock format $systemTime -format %Y%m%d]

I get this: 20150312

But when I look at the outbound SMAT, Cloverleaf is sending 20150312$

Topic

[Brent Fenderson]

Jon, I run into the issue with commas in a field all the time. My fix for this is in the Database Schema Configurator.  Highlight your table, then in the options menu, select Table Schema Options. This will open up the schema properties. I then change the field separator from , to |.

For the Date issue, I like to format the date into an SQL date if it’s going to a date time field. Such as 03/13/2015 or 03/13/2015 09:15:00.

The $ sign is the default row separator. The , in your name field is throwing off the number of fields you’re trying to insert into your table.

[Peter Heggie]

Dates are tricky, at least it was with me and SQL Server. At first I just put the date into a VARCHAR format to avoid having to deal with dates, but that made date processing in stored procedures harder to deal with.

So now I define the column (and stored procedure argument)  as ‘datetime’ in SQL Server. I format the date before inserting as ‘YYYY-MM-DD HH:MM:SS’ or ‘YYYY-MM-DD HH:MM:SS.sss’.

I use a tcl to do that:

set visitdate [dateODBC “$visitdate”]

where dateODBC is

Code:

proc dateODBC {indate} {

  if {[string index “$indate” 4] eq “-“} {return “$indate”}

  set zero4 “0000”

  if {[string length $indate] < 8} {return ""}

  if {[string length $indate] eq 8} {set indate "$indate$zero4"}

  # remove timezone offset
  if {[string index "$indate" 14] eq "-"} {
    set indate [lindex [split "$indate" "-"] 0]
  }

  set date [string range $indate 0 7]
  set time [string range $indate 8 end]

  if {[string length $time] < 6} {
    set len [expr 6 – [string length $time]]
    set apd [string repeat "0" $len]
    set time "$time$apd"
  }

  if {$time eq "000000"} {set time "000001"}
 
  set outdate [clock format [clock scan "$date $time"] -format "%Y-%m-%d %H:%M:%S.000"]
  return $outdate

}

I always use stored procedures instead of direct queries (as recommended by many people here).

I use a generic tcl proc to create the connection

Peter Heggie

[Jon Melin]

Thank you both. I ended up changing the delimiter to a pipe and it seemed to solve my issues. When I get more time I’ll try your approach too Peter.

Slightly off topic, have either of you had a problem deleting a Database Schema (through Config in Site Options)? I created on an additional one by mistaken and just want to clean things up, when I go to the config screen and delete it, it disappears from the drop down, but when I come back, it’s there again?

Thoughts?

Thank you again.

[Author]

Replies