Postgres query into Omnis question

[Bastiaan Olij]

Hey Steve,

Pesky bug that one, character fields with a length over a certain number
of character (including fields of type text) for some reason get a
length of 0 in Omnis.
It pulls the data in just fine but the zero length will eventually
truncate and thus clear the fields.

Casting the field to something smaller will solve it which I believe is
the solution Mayada offered but off course if your data is more then
that number of characters and you wish to not loose that, it'll be too
little.

I've overridden the $fetch in my base table class to fetch into a
temporary list if no columns are defined, I then loop through the
columns of that temporary list to add the columns to my real list and
make alterations as necessary, then do a merge. The overhead is negligible.

So something in the line of this:
---
tBase.$fetch(pvClearList)
if $cinst.$colcount=0
    Do $cinst.$statementobject.$fetch(lvList,kFetchAll)
    For lvColNo from 1 to lvList.$colcount
        If
lvList.$cols.[lvColNo].$coltype=kCharacter&lvList.$cols.[lvColNo].$colsublen=0
            Do $cinst.$cols.$add(lvList.$cols.[lvColNo].$name,
lvList.$cols.[lvColNo].$coltype, lvList.$cols.[lvColNo].$colsubtype,
10000000)
        Else
            Do $cinst.$cols.$add(lvList.$cols.[lvColNo].$name,
lvList.$cols.[lvColNo].$coltype, lvList.$cols.[lvColNo].$colsubtype,
lvList.$cols.[lvColNo].$colsublen)
        End if
    End for

    Do $cinst.$merge(lvList,kFalse,kFalse,pvClearList)

    Quit method kTrue
Else
    quit method $inherited.$fetch(pvClearList)
End if
---

Forgive any typos..

Cheers,

Bas

On 29/03/2017 6:52 AM, Steve Finger wrote:
> Hi,
>
> I’m using Studio 6.1.3 to do a query to Postgres. To simplify, here is an example of what I’m trying to do:
>
> select order_number, order_comments
> from order_forecast
> UNION
> select order_number, null as order_comments
> from orders
>
>
> Then I fetch the result into an undefined Omnis list. Currently only the orders table has values.
>
> The problem is what Omnis brings back into the list. The order_comments column doesn’t have a length. So in an Omnis window which has a complex grid to display and enter values into the order_comments field, nothing can be typed in.
>
> If I change null to ‘ ‘ (1 space) in the query then the column is defined with a length of 1.
>
> The database table is set to 2000 characters.
>
> How can I modify the query so Omnis knows that the length of the column that is returned from the query should allow 2000 characters? Or am I going to have to redefine the list in Omnis?
>
> Thanks,
> Steve Finger
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij