revDataFromQuery

[Wally]

The revDataFromQuery command is suddenly not returning exactly what is in the table I'm querying.

SOME rows of the returned table have been shifted over 1 column to the right while other rows are returned as exactly what is in the table.

I'm using Livecode 4.5.1

[bangkok]

You should post your SQL query, so we can check.

But I would say that your problem is simple : within the records in your DB, you probably have a text column that contains... a TAB character !


In order to check, there is a very simple test to do :

instead of :

Code: Select all

   put revDataFromQuery(,,dbIDMY,dbSQL) into myData

[that will use a TAB as column delimiter]

try

Code: Select all

   put revDataFromQuery("*",,dbIDMY,dbSQL) into myData

Then copy paste the result in Excell.

[Wally]

I have:
put revDataFromQuery(tab,return,dbID,dbQuery) into holder

Where dbQuery is: SELECT * FROM cb1_players;

Thanks for the help!

[Wally]

I exported the table, opened it in excel and the table has the correct data.
example in Exel:
1 3 2 1

When I use:
put revDataFromQuery(tab,return,dbID,dbQuery) into holder

The data returned in holder shows a shift of one column to the right of a few records.
example:
1 3 2 1
becomes:
empty column1 3 2 1

I did all these operations on the table via phpMyAdmin:
Check table
Analyze table
Repair table
Optimize table
Flush the table (FLUSH)

Could the table be corrupt?
Is this bug in revDataFromQuery?

[bangkok]

You have only 1 record in your DB ? What is the structure of your table ?

[Wally]

The table has about 60 records right now but as time goes by there will be 100's. Each record has about 250 columns.

The database has 11 tables constructed like the above table.

I have to find the answer to this quickly or I'll lose my customer!!

[bangkok]

I have to find the answer to this quickly or I'll lose my customer!!

Have you done the test i talked about ?

put revDataFromQuery("*",return,dbID,dbQuery) into holder

I understand that you can't give us the 250 columns structure.

But you have to be more precise :

does the empty column is coming after... a text column ? If yes, then you should explore my advice : a TAB character might be hidden at the end of the data in one record.

I use intensively revDataFromQuery with mySQL, the only times i had this extra column problem, was because a text column was "corrupted" with a TAB char at the end of the data. This TAB char was of course interpreted by LiveCode as a "column delimiter". Hence, the empty column.

[Wally]

THANK YOU!

Your question "does the empty column is coming after... a text column ?" was the key. Yes, there was a tab character after the text column of the faulty rows. Should be easy to track that down.

Thanks again, these forums are a great resource!

[bangkok]

You see. I gave you the clue in my first answer.

You lost some precious time by reading it too fast.

Anyway, happy that you can keep your customer !

[Wally]

I did the: put revDataFromQuery("*",,dbIDMY,dbSQL) into myData but I didn't know how to interpret the results after I pasted into Excel.

Anyway, thanks again!!