Re: Creating Tables by running a method

2018-06-24 Thread Pat Bensky via 4D_Tech 
Interesting ...
Suppose I wanted to add a function to my 4D app to allow end-users to
create new tables. This presumably could be accomplished using XML and
METHOD SET CODE.
Now, we release a program update. Any tables added by the end-user could be
recovered using GET MISSING TABLE NAMES and REGENERATE MISSING TABLE.
If tables have been added to the structure, the recovered tables will have
new table numbers. Is that how it works? IE the tables are recovered by
name, not number. (I ran into problems with this when upgrading an older
database).

I will experiment with this but it would be helpful to know if others have
done it and what your experience was - did it work OK, any gotchas?
Pat

On Sat, 23 Jun 2018 at 21:58, John DeSoi via 4D_Tech <4d_tech@lists.4d.com>
wrote:

> Pat,
>
> Not in the XML, but you could import the table structure (with trigger
> properties enabled) and then set the trigger code using METHOD SET CODE. My
> approach would be to enable all the trigger properties even if there is not
> an immediate need to have a trigger. I don't think there is any other way
> to set the table trigger properties once the table is created. I don't
> believe there is any performance penalty unless the trigger method is
> defined for the table.
>
> John DeSoi, Ph.D.
>
>
>
>
> > On Jun 23, 2018, at 3:14 PM, Pat Bensky via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > John,
> > Can you set the trigger code that way as well (using XML)?
> > Pat
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **



-- 
*
CatBase - Top Dog in Data Publishing
tel: +44 (0) 207 118 7889
w: http://www.catbase.com
skype: pat.bensky
*
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-23 Thread John DeSoi via 4D_Tech 
Pat,

Not in the XML, but you could import the table structure (with trigger 
properties enabled) and then set the trigger code using METHOD SET CODE. My 
approach would be to enable all the trigger properties even if there is not an 
immediate need to have a trigger. I don't think there is any other way to set 
the table trigger properties once the table is created. I don't believe there 
is any performance penalty unless the trigger method is defined for the table.

John DeSoi, Ph.D.




> On Jun 23, 2018, at 3:14 PM, Pat Bensky via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> John,
> Can you set the trigger code that way as well (using XML)?
> Pat

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-23 Thread Pat Bensky via 4D_Tech 
John,
Can you set the trigger code that way as well (using XML)?
Pat

On Sat, 23 Jun 2018 at 21:04, John DeSoi via 4D_Tech <4d_tech@lists.4d.com>
wrote:

> The triggers won't run until you set the trigger properties on the table.
> I'm not aware there is any way to do that via SQL. You can set the trigger
> properties if you create the tables via XML and use IMPORT STRUCTURE.
>
> John DeSoi, Ph.D.
>
>
> > On Jun 23, 2018, at 11:52 AM, Jody Bevan via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > I am using 16R6. I tried creating a trigger in the tables that I created
> using SQL. I can go in and write code in the table triggers. They are
> saved. I didn’t see if they actually ran though. Maybe 4D resolved this
> issue.
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **



-- 
*
CatBase - Top Dog in Data Publishing
tel: +44 (0) 207 118 7889
w: http://www.catbase.com
skype: pat.bensky
*
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-23 Thread John DeSoi via 4D_Tech 
The triggers won't run until you set the trigger properties on the table. I'm 
not aware there is any way to do that via SQL. You can set the trigger 
properties if you create the tables via XML and use IMPORT STRUCTURE.

John DeSoi, Ph.D.


> On Jun 23, 2018, at 11:52 AM, Jody Bevan via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> I am using 16R6. I tried creating a trigger in the tables that I created 
> using SQL. I can go in and write code in the table triggers. They are saved. 
> I didn’t see if they actually ran though. Maybe 4D resolved this issue.

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Creating Tables by running a method

2018-06-23 Thread Randy Engle via 4D_Tech 
Jody,

Glad to hear of your progress!

Randy Engle, Director
XC2 Software LLC – XC2LIVE!

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Jody Bevan via 4D_Tech
Sent: Saturday, June 23, 2018 9:53 AM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Jody Bevan 
Subject: Re: Creating Tables by running a method

Randy:

So I have got this done using all SQL. At the end it would create the indexes 
for the first table I created, but then fail for al the next. Eventually I 
figured out that the index name (behind the scenes) need to be unique within 
the whole database. Therefore I just added a string of the table number into 
the middle of the Index name. Tried it at front and that failed.

All these little gotchas. I really didn’t want to sit and read SQL books for a 
week that may or may not explain these issues.

Thanks for your help - it is working!!

Pat:

I am using 16R6. I tried creating a trigger in the tables that I created using 
SQL. I can go in and write code in the table triggers. They are saved. I didn’t 
see if they actually ran though. Maybe 4D resolved this issue.

Thanks all

Now on to having the method write all our standard code around a table that our 
shell has.


Jody Bevan
Developer

Argus Productions Inc. <https://www.facebook.com/ArgusProductions/>
+1 587-487-6120



> On Jun 22, 2018, at 3:18 PM, Jody Bevan  wrote:
> 
> Randy:
> 
> Thanks so much for this. Giving me two ways - 4D and SQL. I am going to work 
> on some code and interface for doing this.
> 
> Jody
> 
>> On Jun 22, 2018, at 12:09 PM, Randy Engle via 4D_Tech <4d_tech@lists.4d.com 
>> <mailto:4d_tech@lists.4d.com>> wrote:
>> 
>> Jody,
>> 
>> You can set/create an index in 4D Code
>> 
>>  ARRAY POINTER($fieldPtrArr;1)
>>  $fieldPtrArr{1}:=->[Facility]Facility_Name
>>  CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
>> index;"FacilityNameIDX")
>>  
>>// composite index
>>  ARRAY POINTER($fieldPtrArr;2)
>>  $fieldPtrArr{1}:=->[Facility]Service_Address_City
>>  $fieldPtrArr{2}:=->[Facility]Service_Address_Zip_Code
>>  CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
>> index;"CityZipIDX")
>> 
>> 
>> Primary Keys - Needs SQL
>> 
>> $sql:="ALTER TABLE "+$tableName
>> $sql:=$sql+" "+"ADD PRIMARY KEY ("+$fieldName+")"
>> Begin SQL
>>  EXECUTE IMMEDIATE: $sql;
>> End SQL
>> 
>> 
>> 
>> Randy Engle, Director
>> XC2 Software LLC – XC2LIVE!
>> 
> 

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-23 Thread Jody Bevan via 4D_Tech 
Randy:

So I have got this done using all SQL. At the end it would create the indexes 
for the first table I created, but then fail for al the next. Eventually I 
figured out that the index name (behind the scenes) need to be unique within 
the whole database. Therefore I just added a string of the table number into 
the middle of the Index name. Tried it at front and that failed.

All these little gotchas. I really didn’t want to sit and read SQL books for a 
week that may or may not explain these issues.

Thanks for your help - it is working!!

Pat:

I am using 16R6. I tried creating a trigger in the tables that I created using 
SQL. I can go in and write code in the table triggers. They are saved. I didn’t 
see if they actually ran though. Maybe 4D resolved this issue.

Thanks all

Now on to having the method write all our standard code around a table that our 
shell has.


Jody Bevan
Developer

Argus Productions Inc. 
+1 587-487-6120



> On Jun 22, 2018, at 3:18 PM, Jody Bevan  wrote:
> 
> Randy:
> 
> Thanks so much for this. Giving me two ways - 4D and SQL. I am going to work 
> on some code and interface for doing this.
> 
> Jody
> 
>> On Jun 22, 2018, at 12:09 PM, Randy Engle via 4D_Tech <4d_tech@lists.4d.com 
>> > wrote:
>> 
>> Jody,
>> 
>> You can set/create an index in 4D Code
>> 
>>  ARRAY POINTER($fieldPtrArr;1)
>>  $fieldPtrArr{1}:=->[Facility]Facility_Name
>>  CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
>> index;"FacilityNameIDX")
>>  
>>// composite index
>>  ARRAY POINTER($fieldPtrArr;2)
>>  $fieldPtrArr{1}:=->[Facility]Service_Address_City
>>  $fieldPtrArr{2}:=->[Facility]Service_Address_Zip_Code
>>  CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
>> index;"CityZipIDX")
>> 
>> 
>> Primary Keys - Needs SQL
>> 
>> $sql:="ALTER TABLE "+$tableName
>> $sql:=$sql+" "+"ADD PRIMARY KEY ("+$fieldName+")"
>> Begin SQL
>>  EXECUTE IMMEDIATE: $sql;
>> End SQL
>> 
>> 
>> 
>> Randy Engle, Director
>> XC2 Software LLC – XC2LIVE!
>> 
> 

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-22 Thread Jody Bevan via 4D_Tech 
Randy:

Thanks so much for this. Giving me two ways - 4D and SQL. I am going to work on 
some code and interface for doing this.

Jody

> On Jun 22, 2018, at 12:09 PM, Randy Engle via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Jody,
> 
> You can set/create an index in 4D Code
> 
>   ARRAY POINTER($fieldPtrArr;1)
>   $fieldPtrArr{1}:=->[Facility]Facility_Name
>   CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
> index;"FacilityNameIDX")
>   
> // composite index
>   ARRAY POINTER($fieldPtrArr;2)
>   $fieldPtrArr{1}:=->[Facility]Service_Address_City
>   $fieldPtrArr{2}:=->[Facility]Service_Address_Zip_Code
>   CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
> index;"CityZipIDX")
> 
> 
> Primary Keys - Needs SQL
> 
> $sql:="ALTER TABLE "+$tableName
> $sql:=$sql+" "+"ADD PRIMARY KEY ("+$fieldName+")"
> Begin SQL
>   EXECUTE IMMEDIATE: $sql;
> End SQL
> 
> 
> 
> Randy Engle, Director
> XC2 Software LLC – XC2LIVE!
> 
> -Original Message-
> From: 4D_Tech <4d_tech-boun...@lists.4d.com 
> <mailto:4d_tech-boun...@lists.4d.com>> On Behalf Of Jody Bevan via 4D_Tech
> Sent: Friday, June 22, 2018 10:45 AM
> To: 4D iNug Technical <4d_tech@lists.4d.com <mailto:4d_tech@lists.4d.com>>
> Cc: Jody Bevan mailto:jody.be...@gmail.com>>
> Subject: Creating Tables by running a method
> 
> I know that this feature has been around for a long time  ~ 4D 2011.
> 
> I am now using 16R6. I now have a method that creates a table with the fields 
> I want added.
> 
> The challenge I am having is setting indexes (and index types), as well as 
> Primary Keys. 
> 
> I looked in the Knowledge Base for code examples but do not see any - hmmm.
> 
> I am not a SQL person, so reading the 4D SQL Reference on this is a bigger 
> challenge to me.
> 
> Can anyone pass along a point to example code, or reference for this.
> 
> Thanks
> 
> 
> 
> 
> Jody Bevan
> Developer
> 
> Argus Productions Inc. <https://www.facebook.com/ArgusProductions/ 
> <https://www.facebook.com/ArgusProductions/>>
> +1 587-487-6120
> 

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-22 Thread Jody Bevan via 4D_Tech 
Pat:

We have rarely used a trigger, but it is an important thing to know in going 
into it. Thanks for telling me. At least I will not spend a week trying to get 
a trigger into a table I have created this way. One never knows what they will 
be doing, or need in the future. Life has certainly taught me that.

Jody


> On Jun 22, 2018, at 3:14 PM, Pat Bensky via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> One gotcha with creating tables this way is that you cannot add triggers.
> That may not be important for you but it's worth knowing ...
> 
> Pat
> 

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Creating Tables by running a method

2018-06-22 Thread Pat Bensky via 4D_Tech 
One gotcha with creating tables this way is that you cannot add triggers.
That may not be important for you but it's worth knowing ...

Pat

On Fri, 22 Jun 2018 at 19:11, Randy Engle via 4D_Tech <4d_tech@lists.4d.com>
wrote:

> Just in case you "really" want to use SQL to create the index:
>
> $statement_t:=""
> $statement_t:=$statement_t+"CREATE INDEX "+$index_Name
> $statement_t:=$statement_t+" "  // Need a space
> $statement_t:=$statement_t+"ON "+$tableName_t+" ("+$fieldName+")"
>
>   //example:  $statement_t:="CREATE INDEX CITY_IDX ON ADDRESSES (City)"
>
> Begin SQL
> EXECUTE IMMEDIATE :$statement_t;
> End SQL
>
>
> Randy Engle, Director
> XC2 Software LLC – XC2LIVE!
>
> -Original Message-
> From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Jody Bevan via
> 4D_Tech
> Sent: Friday, June 22, 2018 10:45 AM
> To: 4D iNug Technical <4d_tech@lists.4d.com>
> Cc: Jody Bevan 
> Subject: Creating Tables by running a method
>
> I know that this feature has been around for a long time  ~ 4D 2011.
>
> I am now using 16R6. I now have a method that creates a table with the
> fields I want added.
>
> The challenge I am having is setting indexes (and index types), as well as
> Primary Keys.
>
> I looked in the Knowledge Base for code examples but do not see any - hmmm.
>
> I am not a SQL person, so reading the 4D SQL Reference on this is a bigger
> challenge to me.
>
> Can anyone pass along a point to example code, or reference for this.
>
> Thanks
>
>
>
>
> Jody Bevan
> Developer
>
> Argus Productions Inc. <https://www.facebook.com/ArgusProductions/>
> +1 587-487-6120
>
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **



-- 
*
CatBase - Top Dog in Data Publishing
tel: +44 (0) 207 118 7889
w: http://www.catbase.com
skype: pat.bensky
*
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Creating Tables by running a method

2018-06-22 Thread Randy Engle via 4D_Tech 
Just in case you "really" want to use SQL to create the index:

$statement_t:=""
$statement_t:=$statement_t+"CREATE INDEX "+$index_Name
$statement_t:=$statement_t+" "  // Need a space
$statement_t:=$statement_t+"ON "+$tableName_t+" ("+$fieldName+")"

  //example:  $statement_t:="CREATE INDEX CITY_IDX ON ADDRESSES (City)"

Begin SQL
EXECUTE IMMEDIATE :$statement_t;
End SQL


Randy Engle, Director
XC2 Software LLC – XC2LIVE!

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Jody Bevan via 4D_Tech
Sent: Friday, June 22, 2018 10:45 AM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Jody Bevan 
Subject: Creating Tables by running a method

I know that this feature has been around for a long time  ~ 4D 2011.

I am now using 16R6. I now have a method that creates a table with the fields I 
want added.

The challenge I am having is setting indexes (and index types), as well as 
Primary Keys. 

I looked in the Knowledge Base for code examples but do not see any - hmmm.

I am not a SQL person, so reading the 4D SQL Reference on this is a bigger 
challenge to me.

Can anyone pass along a point to example code, or reference for this.

Thanks




Jody Bevan
Developer

Argus Productions Inc. <https://www.facebook.com/ArgusProductions/>
+1 587-487-6120



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Creating Tables by running a method

2018-06-22 Thread Randy Engle via 4D_Tech 
Jody,

You can set/create an index in 4D Code

ARRAY POINTER($fieldPtrArr;1)
$fieldPtrArr{1}:=->[Facility]Facility_Name
CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
index;"FacilityNameIDX")

  // composite index
ARRAY POINTER($fieldPtrArr;2)
$fieldPtrArr{1}:=->[Facility]Service_Address_City
$fieldPtrArr{2}:=->[Facility]Service_Address_Zip_Code
CREATE INDEX([Facility];$fieldPtrArr;Standard BTree 
index;"CityZipIDX")


Primary Keys - Needs SQL

$sql:="ALTER TABLE "+$tableName
$sql:=$sql+" "+"ADD PRIMARY KEY ("+$fieldName+")"
Begin SQL
EXECUTE IMMEDIATE: $sql;
End SQL



Randy Engle, Director
XC2 Software LLC – XC2LIVE!

-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Jody Bevan via 4D_Tech
Sent: Friday, June 22, 2018 10:45 AM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Jody Bevan 
Subject: Creating Tables by running a method

I know that this feature has been around for a long time  ~ 4D 2011.

I am now using 16R6. I now have a method that creates a table with the fields I 
want added.

The challenge I am having is setting indexes (and index types), as well as 
Primary Keys. 

I looked in the Knowledge Base for code examples but do not see any - hmmm.

I am not a SQL person, so reading the 4D SQL Reference on this is a bigger 
challenge to me.

Can anyone pass along a point to example code, or reference for this.

Thanks




Jody Bevan
Developer

Argus Productions Inc. <https://www.facebook.com/ArgusProductions/>
+1 587-487-6120



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Creating Tables by running a method

2018-06-22 Thread Jody Bevan via 4D_Tech 
I know that this feature has been around for a long time  ~ 4D 2011.

I am now using 16R6. I now have a method that creates a table with the fields I 
want added.

The challenge I am having is setting indexes (and index types), as well as 
Primary Keys. 

I looked in the Knowledge Base for code examples but do not see any - hmmm.

I am not a SQL person, so reading the 4D SQL Reference on this is a bigger 
challenge to me.

Can anyone pass along a point to example code, or reference for this.

Thanks




Jody Bevan
Developer

Argus Productions Inc. 
+1 587-487-6120



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**