Getting data prepped for database entry.

[Kay C Lan]

On 11/3/06, Dave Herndon <herndogy at sbcglobal.net> wrote:

>   Now if I can just check to see if the item is a number or a date and not do the single quote operation on those items I will be set.

See my recent post against your original post Subject: Stripping blank
spaces.....

It should give you another way to tackle this problem with less lines
of Rev code:-)

Also check out merge() in the Rev docs and search this list. It's a
better way to construct SQL statements (plus other uses). Thanks to
Jim Ault for giving me this tip, I'd just like to pass it on:-)

Oh OK, you twisted my arm. Try this, it uses SQL's TRIM() to remove
the unwanted spaces, SQL's UPPER() to capitalise and Rev's merge() to
make SQL statement construction easier. Also it uses repeat for each
which is faster than repeat with x =. This basically takes each line
of your field and INSERTS it, nicely formatted, into your SQL DB.

assuming the first 4 items are varchar
assuming item 5 is a number
assuming item 6 is a date
assuming all need to be trimmed of leading/trailing spaces
assuming only item 2 and 4 need capitalization

on mouseUp
  put field "data" into MyList
  repeat for each line tMyLine in MyList
    put merge("INSERT INTO test VALUES
    (TRIM('[[item 1 of tMyLine]]'),
    TRIM(UPPER('[[item 2 of tMyLine]]')),
    TRIM('[[item 3 of tMyLine]]'),
    TRIM(UPPER('[[item 4 of tMyLine]]')),
    TRIM([[item 5 of tMyLine]]),
    TRIM([[item 6 of tMyLine]]))") into tMySQL
    revExecuteSQL tMyDbID,tMySQL
  end repeat
end mouseUp

NOTE: there are only two lines of code inside the repeat loop. put
merge(....) into tMySQL is one line of code that I formatted in this
email  for ease of reading.
NOTE: merge() allows you to easily place single quotes only where needed.

HTH :-)