working with multiline addresses in the database

[dochawk]

I expect that this is a rtfm question, but my googling hasn't led to the right fm.

I need to store multi-line addresses in the database, and I'd really rather do it as a single multiline field rather than adr1, adr2, adr3, and adr4.

I can get multiline storage of items if I use tab instead of comma as the data delimiter in my query, but then when I use SELECT I get too many rows.

I could SELECT just the unique key for matching items, I suppose, and then loop through those with repeated queries, but that seems drastic/wasteful/pounding.

Is there a different delimiter I should be using to end the records, perhaps (other than return)?

I assume the answer is obvious, but I'm out of my element.

thanks

hawk

[BvG]

You can store any data in a database. There's no need to replace the returns. To search for anything in a field, you use escape chars, for example in sqlite it's the percentage symbol:

SELECT * WHERE address like '%steve%'

[dochawk]

Thanks.

But it's not so much the storage or retrieval, as getting it back in a usable format.

if I have, for example, key, name, and address,
and

Code: Select all

  put revDataFromQuery(tab,,myDb,"SELECT * FROM people ")  into theData
end repeat  

I end up with a great many lines in theData. Something linke

Code: Select all

1    George   123 E. Est St
Las Vegas, NV
2   Fred    432 West E. St, 
NLV, NV

which has 4 lines--or could have 2, or 7, or whatever, depending upon what went into the field that created the addresses.

I'm not seeing a good way to do this other than something like,

Code: Select all

put revDataFromQuery(tab,,myDb,"SELECT key FROM people")  into theData
repeat with i = 1 to the number of lines in theData
   put revDataFromQuery(tab,,myDb,"SELECT * FROM people  WHERE key='" & key & "'")  into theData
end repeat

which will result in a great many queries. (This is the simplified version; I'm not looking to pull the whole database, but filtered off of more fields I've left out for simplicity


thanks

hawk

[phaworth]

Hi,
Couple of possibilities.

First one is to use revQueryDatabase instead of revDataFromQuery. revQueryDatabase returns a recordset ID. If you only SELECT one row from the database, just use revDatabaseColumnNamed or revDatabaseColumnNumbered to get the value of the individual columns from the row. revDatabaseColumnNamed/Numbered puts the data into a variable so it will have the carriage returns in it and won't cause any problems resulting in the wrong number of rows. If your SELECT returns more than 1 row, you can use revMoveToPrev/Next/First/LastRecord to move through the rows in the recordset ID.

I haven't tried the other solution but I think it will work. You'd need to specify a different row terminator to revDataFromQuery but it would need to be one you're sure doesn't occur in your data. You could try numToChar(11) - that's a vertical tab character. To loop through the rows (if you SELECT multiples), set the itemdelimiter to numToChar(11) and base the loop on items rather than lines.

I'd probably go for the first idea!

Pete