LIST data type in CREATE TABLE declaration

[Bastiaan Olij]

Hey Jean Marc,

Remember that SQL hasn't got an inkling what an omnis list is. The only
way you can store an Omnis list into the database is by storing it in a
binary column. Now using Omnis SQL to a datafile I have no idea how it
would handle that but I'm sure it is one of those exception cases.

That all said, if you have a column that is defined as a list, and you
select from a column which is a binary containing valid Omnis list data,
Omnis will properly load the list data (bar any bindings with file
classes, they are often lost).

So:
----
do lvList.$cols.$add("ID",kinteger,klongint)
do lvList.$cols.$add("List",klist)

if lvStatementObj.$execdirect("select MyIDColumn, MyListColumn from
MyTable")
  do lvStatementObj.$fetch(lvList,kFetchAll)
end if
----

Should just work fine.

Inserting data into this table btw, is just the opposite, if your data
in Omnis is a proper Omnis list, and your column in your database is a
binary column, and you issue an SQL command with bindings, the list gets
converted to a binary and store properly:
----
calculate lvList.$line as lvList.$add(123,pvDataList).$line
begin statement
sta: insert into MyTable (MyIDColumn, MyListColumn) values
(@[lvList.ID], @[lvList.MyListColumn])
end statement
do lvStatementObj.$execdirect()
----

Finally, create a schema class for your table where the list column is
defined as a list, and create a table class for this schema, and Omnis
will create the proper SQL for you.

Cheers,

Bas

Jean-Marc Voegeli wrote:
> Hello list,
>
> I do not know how to $define a list which is a field in a SQL schema.
>
> I add a new record with a list defined from sql class.
>
> This list has a column which is also a list.
>
> I tried $define, then SQL_List.COL_list.$cols.$add(...), a.s.o.
>
> I turned around the difficulty by doing :
>
> Calculate vFile as "Preferences.df1"
> Open data file (Do not close other data) {[vFile],Prefs_DF}
> Set main file {Preferences}    -> Preferences is a file format from the schema s_Preferences
> Prepare for insert
> Set current list Preferences.p_ChoixChapitres
> Define list {EnTete,Chapitre}
> Update files     ;; #F
> Close data file {Prefs_DF}
>
>
> then when I open the file through the SQL session, the column "p_ChoixChapitres" is defined and I am able to add lines.
>
> But it is just a turn around not very elegant either. 
>
> Thank you for your help.
>
> Best regards,
>
>
> Jean-Marc VOEGELI
>
> Tél.: 022/784.34.80
> Fax : 022/784.23.40
> Natel : 079/416.35.34
> eMail : jmvoegeli at bluewin.ch
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>
>   


-- 
Kindest Regards,

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