Please Clarify about table identity
Andreas Pfeiffer
apfeiffer at familiepfeiffer.de
Fri Jan 26 09:25:23 EST 2018
Hi Das,
Almost correct, yes. The list or row variable that you define from a table class BECOMES the instance of the table class. Since you may have bound a schema class to the table class the list or row also uses its definition.
And yes - if using a table class that is bound to a schema class only one server table can be used in this list or row. However you may limit the columns in your definition if you want:
i.e. Do myList.$definefromsqlclass($tables.taMyTableClass,’col1’,’col2’,’col3)
.. will only use col1, col2 and col3 from the attached schema class even if there are more columns in the schema.
If you want to have a result that uses more than one server table you can use a table class that is connected to a query class instead. What you do is to define your join in the query class. Then when you fire up a $select to your list it will use the join that you defined in the query class and use those tables there.
Finally for more complex joins I would recommend using the Query Builder that we have in Omnis Studio 8.1.2.4. This allows to generate more complex joins. You basically drag the tables from the table list in the Query Builder onto the white space on the right. Then you drag a line from the primary key column of the father table to the foreign key column of the child. Then you select the columns you want to display and click Build&Run. You may want to manually change your SQL until you are satisfied with the result. Check with Build then. Finally there is a button „Other“ that allows to build a table class from the generated statement. If you do so you will receive a new table class with a method $load that contains your statement. Then make a window or remote form and define a list variable and assign this table class as the sub type. Finally you can call the method like this: Do myList.$load() - voila, the list will automatically be defined from the result of your statement and filled with the data.
Hope this helps.
Best regards,
Andreas
> Am 26.01.2018 um 03:12 schrieb Das Goravani <das at goravani.com>:
>
> I may have figured this out for myself by reading the manual again about Table Classes. So now tell me if I’m correct:
>
> When you define a list from a Schema or Query class, an instance of an automatic hidden table class is created and associated with the defined list, so that it has the methods of a table class, which are a bunch regarding basic SQL functions like insert, update, delete etc…and this table class takes from the Schema or Query class the name of the actual server table it the table class is pointed at, so when you work the defined list, the hidden table class inside it is pointing to a certain server table, and that’s why you don’t need to specify the table in code like that shown below…because it’s already associated.
>
> I think that’s right and that I thus now get it. Wow, seems flimsy and limited to whole tables, one at a time.
>
> When you do the kind of SQL commands that return lists of combined tables, how then do you specify the tables since it’s not just one. I realize Query classes can have columns from different tables, but I haven’t even looked at the interface for a query class yet. I was surprised to find table classes have only code like objects. I thought they have a column list like Schema classes. I’m assuming Query classes have a columns list otherwise how would they know which fields to combine? Stiff confusing these three Schema, Table and Query. And if I should care, use Tables or not, I’ve been told both, to forget them and to use them, both.
>
> I’ve been solidly reading the Postgres Manual and my head is exploding, man SQL has tons in it, tons… it’s so vast. So I had to turn my head back to pragmatic code and my real questions about it. Researching on that in the Omnis manuals.. etc…
>
>
>
>> On Jan 25, 2018, at 5:35 PM, Das Goravani <das at Goravani.com> wrote:
>>
>>
>> The following code works, but nowhere is the table specified, except only in that it is typed into the Schema window as the associated table. Here the list is defined from the schema class. Is it true that the list stores the table name from the schema class? That would be the only connection possible going forward to the other code, which works, in that the customers table is accessed, written to, updated to, all correctly, but nowhere is the table name said, this confuses me. How do I switch now to another table… by redefining the list to the desired schema name ? That would be the only connection given this code.
>>
>> In window construct we have
>>
>> Do iSqlRow.$definefromsqlclass('sCustomers')
>> Do iSqlRow.$sessionobject.$assign($root.$sessions.[lSessionName].$sessionobject) Returns #F
>>
>> In “Next” button we have
>>
>> On evClick
>> Do iSqlRow.$fetch() Returns lStatus
>> If lStatus=kFetchFinished|lStatus=kFetchError
>> Do iSqlRow.$select()
>> Do iSqlRow.$fetch() Returns lStatus
>> End If
>> Calculate iOldRow as iSqlRow
>> Do $cwind.$redraw()
>>
>> In Save Button we have
>>
>> On evClick
>> If iNew
>> Do iSqlRow.$insert() Returns lReturnFlag
>> If lReturnFlag
>> Calculate iOldRow as iSqlRow
>> End If
>> Else
>> Do iSqlRow.$update(iOldRow) Returns lReturnFlag
>> Do $ctask.tPGSessObj.$commit Returns lReturnFlag
>> If lReturnFlag
>> Calculate iOldRow as iSqlRow
>> End If
>> Do $ctask.tPGSessObj.$transactionmode.$assign(kSessionTranAutomatic) Returns lReturnFlag
>> End If
>>
>> Thank you, best wishes,
>>
>> Das Goravani
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
More information about the omnisdev-en
mailing list