Getting data prepped for database entry.
Kay C Lan
lan.kc.macmail at gmail.com
Sat Nov 4 11:28:47 EST 2006
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
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
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
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.
More information about the Use-livecode