Postgres query into Omnis question

[Vik Shah]

Hey Bas,

Instead of for loop wouldn't this work too?

Do $cinst.$copydefinition(lvList)
Do $cinst.$cols.$sendall($ref.$colsublen.$assign(10000000),(($ref.$coltype=kCharacter)&($ref.$colsublen=0))

Then the merge...

Vik Shah
Keys2Solutions 

> On 29 Mar 2017, at 9:35 am, Bastiaan Olij <bastiaan at basenlily.me> wrote:
> 
> 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
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com