Reading integers sometimes truncated

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
benr_mc
Posts: 26
Joined: Mon Apr 17, 2006 9:22 pm

Reading integers sometimes truncated

Post by benr_mc » Sun Mar 27, 2022 8:02 am

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9580
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Reading integers sometimes truncated

Post by dunbarx » Sun Mar 27, 2022 5:36 pm

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
Posts: 26
Joined: Mon Apr 17, 2006 9:22 pm

Re: Reading integers sometimes truncated

Post by benr_mc » Sun Mar 27, 2022 7:03 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9580
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Reading integers sometimes truncated

Post by dunbarx » Sun Mar 27, 2022 9:21 pm

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
Posts: 26
Joined: Mon Apr 17, 2006 9:22 pm

Re: Reading integers sometimes truncated

Post by benr_mc » Mon Mar 28, 2022 10:04 am

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
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 9287
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: Reading integers sometimes truncated

Post by richmond62 » Mon Mar 28, 2022 10:56 am

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. 8)

benr_mc
Posts: 26
Joined: Mon Apr 17, 2006 9:22 pm

Re: Reading integers sometimes truncated

Post by benr_mc » Mon Mar 28, 2022 11:55 am

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
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 9287
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: Reading integers sometimes truncated

Post by richmond62 » Mon Mar 28, 2022 12:15 pm

thanks for engaging
I am so sorry a wedding didn't take place. 8)

Post Reply

Return to “Databases”