Reading integers sometimes truncated

[benr_mc]

Reading a bunch of tables from an Oracle database over ODBC, on Mac, using revOpenDatabase and revDatabaseColumnNumbered (Livecode 9.6.6).

Many of the relationships as you'd expected connected by integer ids on the tables.

For some tables when I read the data I consistently get these integer ids truncated. So e.g. if it should read

Code: Select all

Key    Title
95     Grapes
1457   Oranges
1458   Pears
1592   Apples
1593   Bananas

I get instead

Code: Select all

Key  Title
9    Grapes
14   Oranges
14   Pears
15   Apples
15   Bananas

Meanwhile some other similar tables are fine, and link tables are fine, e.g.

Code: Select all

RecipeKey  FruitKey
1002       1457
1003       1593

I actually emailed the administrator of this database saying that there was something wrong before I thought of trying another database admin tool to look at the same table, and it was perfect sensible.

I've found a work around: by asking the database to convert the integer to char, e.g.
SELECT to_char(Key) as Key,Title ...

with this modification, I get the expected data.

Has anyone else come across this?

[dunbarx]

Hi.

I do not use databases, but it seems that this is not a LC issue, since changing to another system seemed to work. Can you break into the stream of data and track those integers? This to try to find where they lose some of their digits.

Craig

[benr_mc]

Hi Craig, I think it is an LC issue, precisely because changing to another app worked!

I think I wasn't very clear. I didn't change the database or the ODBC connection; I downloaded a couple of apps which read from ODBC and Oracle databases, and did a basic query on the table; and in both cases got the full unique IDs. That's what made me realise that the problem wasn't bad data in the table, but that my (LiveCode) app was failing to read it correctly.

[dunbarx]

Ah. OK.

The dictionary says:

"If you specify a holderVariable, the data is placed in that variable. Otherwise, the data is returned by the function. In order to retrieve binary data, a holder variable must be specified, otherwise the revDatabaseColumnNumbered function may return truncated data. This is due to limitations in the current architecture."

What about that "holder variable" thing?


Craig

[benr_mc]

Hi Craig,

Thanks for taking an interest.

I've tried it both ways -
put revDatabaseColumnNumbered(cursorID, c) into tRawData

get revDatabaseColumnNumbered(cursorID, c, "tRawData")

in both cases, tRawData has the truncated number.

Has anyone else seen something like this? I have a faint memory of having come across it before - I've been targeting variations of this database, for different clients, for well over a decade - but I don't remember the details! I suspect it might be an Oracle thing, because I think not many people around here are using Oracle. The weird thing is that it's only on certain tables.

[richmond62]

a holder variable must be specified

I'll bet your code just 'sucks' out the data from the DB and plops it directly into a listField.

Reading the above, it would seem you need an intermediate stage where you put each item of data into a
variable, and then plop the variable into the listField.

Give it a try.

[benr_mc]

Hi Richmond, thanks for engaging, but that's not the case.

It all goes through variables
(see above:

Code: Select all

put revDatabaseColumnNumbered(cursorID, c) into tRawData

get revDatabaseColumnNumbered(cursorID, c, "tRawData")

)
(and as a matter of fact never goes into a field) - and I've inspected in the debugger, and both ways it's truncated as soon as it hits the variable.

[richmond62]

thanks for engaging

I am so sorry a wedding didn't take place.