LiveCode Forums.

Stuffing a variable returned from a revDataFromQuery into a table field works really well - until you try to diplay data from a text column containing CRs or such.

Short of excluding text columns from my SQL queries, is there a feasible way to handle this?

TIA,
Kai

Hi Kai,

The easiest way is to use preserved characters as your row and column delimiters in your call to revDataFromQuery, then replace the tab and return characters with spaces, and replace the reserved characters with tabs and returns.
Here's a bit of code to get you going - you'll probably want to put this into a library for reuse: Hope this helped,

Jan Schenkel.

Thanks, Jan ~


this will do nicely.

Kai

Jan,

I have a similar problem. For example, I have a company name field where frequently there is a ", Inc." after the name. Wherever the comma is, it moves the "Inc." into the next field. In other words it is using the comma as the column delimiter. I tried the code snippet you provided to Kai, but it still does it. Is there any way to work around this?

Thanks,

Hi There,

I can't really think of why the comma would make the data move into the next column - unless you're not showing the vertical gridlines of the field.

The default column delimiter of the 'revDataFromQuery' function is a tab, so that shouldn't really interfere. Can you post the exact script that is causing this issue to appear?

Jan Schenkel.

Jan,

This is the current version (there have been many). notice the commented out line, where I experimented with replacing the comma with a tilde, to see if it made a difference. Yes, it did. The "~ Inc." was in the proper column. So the comma is acting like it's in an unquoted csv field.

One more thing ... I have another button that simply puts the query directly into the field. Everything goes into the columns correctly. It's only when I try to use the variable that this happens.

Also, as long as I'm asking questions, notice the 1st line opening the db. I have another button that opens the db and doesn't close it. The tConnectionID is designated a global variable, but if I don't have the open line in this button's script, it won't work. Shouldn't a global variable work in all scripts?

Anyway, here's the script from the button. Most of code is yours! I thank you very much and promise that I will try to do something for you one of these days!

put revOpenDatabase("sqlite","/users/martin/MB_Database.db", , , , ) into tConnectionID
put "SELECT * FROM Clients" into tQuery
put numToChar(30) into tRowDelim -- ASCII character for Record Separator
put numToChar(31) into tColDelim -- ASCII character for Unit Separator
put revDataFromQuery(tColDelim,tRowDelim,tConnectionID,tQuery) into tData
-- replace comma with "~" in tData -- just experimenting here
replace tab with space in tData
replace return with space in tData
replace tColDelim with tab in tData
replace tRowDelim with return in tData
put tData into field "clients"


Thanks Jan,

A global is only global in the sense that it is visibleto all scripts that declare a reference to the global variable. So you should insert either at the top of your script, or inside every handler or function that uses it.
Depending on the database driver, connectiions to the same database may or may not be pooled, so you should probably just connect once upon 'openStack' and then disconnect upon 'closeStack' - but I digress.

Back to your original question: there is no settable column delimiter in Revolution today (there's a keyword 'columnDelimiter' but that isn't implemented, just reserved for future use) so the comma shouldn't shift it to the next column. So apart from the overflow when you're not showing the vertical grid, I'm mystified as to why this would happen.
I don't have time to test it fully right now, but what happens if you replace the comma with "~" and then replace that back to comma?

Jan Schenkel.

Hi Jan,

We're on the same wavelength in our understanding of global variables. I will continue to experiment in this area.

Back to the original problem ... as I said, when I replaced the comma with the tilde, it worked fine. If I then replace the tilde with a comma, it reverts to the original problem display. As I said, if I place the query directly into the field, it displays correctly. It's only when I put the query into a variable and then put the variable into the field that the problem arises.

I'm not under a "deadline" or anything like that, so please don't feel any pressure. I'm grateful for your willingness to listen and help.

Martin

An idea that popped into my head (and yes, I should have thought of this one sooner): is the company name the last column?
The last column may bleed over intoà the next, unless you put another 'tab' after it. Jan Schenkel.

Actually, there's a faster way of doing this. Jan Schenkel.

Jan,

The company name is in the first column. Here is an example of the data, using the piping symbol as the column delimiter. So, the fields shown are company name, contact, address, city, state, zipcode

JJR Enterprises, Inc. |Jay Roberts |560 E. Carson Dr. |Raleigh |NC |27527

This line of data will display like this (notice the comma is gone):

JJR Enterprises |Inc. |Jay Roberts |560 E. Carson Dr. |Raleigh |NC |27527

As I've said, the crazy thing is that this only happens when putting the query into a variable and then placing the variable into the field, instead of putting the query directly into the field where everything comes out correctly.

I'm starting to think that this may be a Revolution bug?

Thank you for your efforts,

Martin

This is baffling. I'd be interested to find out where exactly this happens.
Could you try the following things?
- get just that single record and see if the same thing happens;
- step through the 'replace' commands one by one and use the variable watcher to see where exactly the comma turns into a tab;
- try and make an isolated stack + sqlite database to replicate the problem.

That way, everything is prepared to hand it over to the guys at RunRev HQ.

Jan Schenkel.

Could it be that when you put
"SomeCompany, Inc" into a variable
the Rev default delimiter "," comes
into effect?

Perhaps changing that will solve your
problem.

The problem is the varlist that is returned by revDataFromQuery. The varlist for each row seems to be hard-coded into Livecode as comma delimited. This varlist is the problem and seems to not be able to be controlled to my knowledge in Livecode. If I am wrong, please correct me and I will be glad to know there is way out of this problem.