Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Automatically create table?

5 posts in General Discussion Last posting was on 2004-04-02 19:18:13.0Z
Sue Posted on 2004-04-01 16:26:14.0Z
Sender: 1b0e.406c3e8c.1804289383@sybase.com
From: Sue
Newsgroups: ianywhere.public.general
Subject: Automatically create table?
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406c42a2.1b53.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 1 Apr 2004 08:26:10 -0800, 10.22.241.42
Lines: 39
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 1 Apr 2004 08:26:11 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 1 Apr 2004 08:26:14 -0800
X-Trace: forums-1-dub 1080836774 10.22.108.75 (1 Apr 2004 08:26:14 -0800)
X-Original-Trace: 1 Apr 2004 08:26:14 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2915
Article PK: 6384

I'm using ASA 8.*.
How to automatically create a new table (table2) if a row is
added into another table (table1). The new table name will
come from the new row just added in table1.

Is that possible or not?
Currently I have to manually create the new table2 series by
looking at the rows in the table1. It's not very nice. Any
better way of doing this?

I tried to use trigger in table1:
=========================================
ALTER trigger cgstest_add_af after insert order 1 on
ttt.cgstest
referencing new as new_name
for each row
/* WHEN ( search_condition ) */
begin
declare strTable char(10);
if new_name.username is not null then
set strTable=new_name.username;
create table ttt.strTable(
col1 char(10) not null,
)
end if
end
==========================

Note: --- the ttt. (the database log on user id)is
automatically attacched in the create table statement when
I save the trigger, even though I don't

===============
Error: ASA error -267: COMMIT/ROLLBACK not allowed within
atomic operation.


Thanks
Sue


Reg Domaratzki Posted on 2004-04-01 17:50:41.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <406c42a2.1b53.846930886@sybase.com>
Subject: Re: Automatically create table?
Lines: 72
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <406c5671$1@forums-1-dub>
Date: 1 Apr 2004 09:50:41 -0800
X-Trace: forums-1-dub 1080841841 10.25.107.174 (1 Apr 2004 09:50:41 -0800)
X-Original-Trace: 1 Apr 2004 09:50:41 -0800, rdomarat-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2916
Article PK: 6382

Short Answer : No.

Longer Answer :

There are three problems with your trigger :

1) You cannot do a COMMIT in a trigger. All DDL (Data Definition Language)
commands have an implicit COMMIT.
2) Even if you could COMMIT in a trigger, your trigger, as coded would also
create a table called ttt.strTable. To use the value in the strTable
variable you would need to use EXECUTE IMMEDIATE

execute immediate 'CREATE TABLE ttt.' || strTable || ' ( col1 char(10) not
null )';

3) The comma after the last column definition would cause a syntax error.

This seems like a very non-relational solution to a problem. Why not have a
single table with a username column in it?

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Sue> wrote in message news:406c42a2.1b53.846930886@sybase.com...
> I'm using ASA 8.*.
> How to automatically create a new table (table2) if a row is
> added into another table (table1). The new table name will
> come from the new row just added in table1.
>
> Is that possible or not?
> Currently I have to manually create the new table2 series by
> looking at the rows in the table1. It's not very nice. Any
> better way of doing this?
>
> I tried to use trigger in table1:
> =========================================
> ALTER trigger cgstest_add_af after insert order 1 on
> ttt.cgstest
> referencing new as new_name
> for each row
> /* WHEN ( search_condition ) */
> begin
> declare strTable char(10);
> if new_name.username is not null then
> set strTable=new_name.username;
> create table ttt.strTable(
> col1 char(10) not null,
> )
> end if
> end
> ==========================
>
> Note: --- the ttt. (the database log on user id)is
> automatically attacched in the create table statement when
> I save the trigger, even though I don't
>
> ===============
> Error: ASA error -267: COMMIT/ROLLBACK not allowed within
> atomic operation.
>
>
> Thanks
> Sue


Sue Posted on 2004-04-01 19:15:07.0Z
Sender: 1d78.406c6720.1804289383@sybase.com
From: Sue
Newsgroups: ianywhere.public.general
Subject: Re: Automatically create table?
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406c6a34.1db7.846930886@sybase.com>
References: <406c42a2.1b53.846930886@sybase.com><406c5671$1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 1 Apr 2004 11:15:00 -0800, 10.22.241.42
Lines: 94
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 1 Apr 2004 11:15:06 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 1 Apr 2004 11:15:07 -0800
X-Trace: forums-1-dub 1080846907 10.22.108.75 (1 Apr 2004 11:15:07 -0800)
X-Original-Trace: 1 Apr 2004 11:15:07 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2917
Article PK: 6385

Thanks for the info.
The code I posted is just a test table. My actually data is
very big and cause performance issues. I want to segment the
data into differrent tables with the same table definitions.
So the main table only store the name of the segmented
tables and the actual data is in the segmented tables.

I tried to use 'execute immediate' (The comma is a typo
there)and still have the problem of COMMIT.

So, is trigger not a solution? What's other solutions
available?

Thanks for your help.

Sue

> Short Answer : No.
>
> Longer Answer :
>
> There are three problems with your trigger :
>
> 1) You cannot do a COMMIT in a trigger. All DDL (Data
> Definition Language) commands have an implicit COMMIT.
> 2) Even if you could COMMIT in a trigger, your trigger, as
> coded would also create a table called ttt.strTable. To
> use the value in the strTable variable you would need to
> use EXECUTE IMMEDIATE
>
> execute immediate 'CREATE TABLE ttt.' || strTable || ' (
> col1 char(10) not null )';
>
> 3) The comma after the last column definition would cause
> a syntax error.
>
> This seems like a very non-relational solution to a
> problem. Why not have a single table with a username
> column in it?
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer ASA Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <Sue> wrote in message
> > news:406c42a2.1b53.846930886@sybase.com... I'm using ASA
> > 8.*. How to automatically create a new table (table2) if
> > a row is added into another table (table1). The new
> > table name will come from the new row just added in
> table1. >
> > Is that possible or not?
> > Currently I have to manually create the new table2
> > series by looking at the rows in the table1. It's not
> > very nice. Any better way of doing this?
> >
> > I tried to use trigger in table1:
> > =========================================
> > ALTER trigger cgstest_add_af after insert order 1 on
> > ttt.cgstest
> > referencing new as new_name
> > for each row
> > /* WHEN ( search_condition ) */
> > begin
> > declare strTable char(10);
> > if new_name.username is not null then
> > set strTable=new_name.username;
> > create table ttt.strTable(
> > col1 char(10) not null,
> > )
> > end if
> > end
> > ==========================
> >
> > Note: --- the ttt. (the database log on user id)is
> > automatically attacched in the create table statement
> > when I save the trigger, even though I don't
> >
> > ===============
> > Error: ASA error -267: COMMIT/ROLLBACK not allowed
> > within atomic operation.
> >
> >
> > Thanks
> > Sue
>
>


Robert Waywell Posted on 2004-04-02 15:20:47.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <406c42a2.1b53.846930886@sybase.com><406c5671$1@forums-1-dub> <406c6a34.1db7.846930886@sybase.com>
Subject: Re: Automatically create table?
Lines: 123
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: 10.25.100.155
X-Original-NNTP-Posting-Host: 10.25.100.155
Message-ID: <406d84cf$1@forums-1-dub>
Date: 2 Apr 2004 07:20:47 -0800
X-Trace: forums-1-dub 1080919247 10.25.100.155 (2 Apr 2004 07:20:47 -0800)
X-Original-Trace: 2 Apr 2004 07:20:47 -0800, 10.25.100.155
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2919
Article PK: 6390

Partitioning the data into separate tables is unlikely to address your
performance issue. Have you tried running the Index Consultant in version 9?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Sue> wrote in message news:406c6a34.1db7.846930886@sybase.com...
> Thanks for the info.
> The code I posted is just a test table. My actually data is
> very big and cause performance issues. I want to segment the
> data into differrent tables with the same table definitions.
> So the main table only store the name of the segmented
> tables and the actual data is in the segmented tables.
>
> I tried to use 'execute immediate' (The comma is a typo
> there)and still have the problem of COMMIT.
>
> So, is trigger not a solution? What's other solutions
> available?
>
> Thanks for your help.
>
> Sue
>
>
> > Short Answer : No.
> >
> > Longer Answer :
> >
> > There are three problems with your trigger :
> >
> > 1) You cannot do a COMMIT in a trigger. All DDL (Data
> > Definition Language) commands have an implicit COMMIT.
> > 2) Even if you could COMMIT in a trigger, your trigger, as
> > coded would also create a table called ttt.strTable. To
> > use the value in the strTable variable you would need to
> > use EXECUTE IMMEDIATE
> >
> > execute immediate 'CREATE TABLE ttt.' || strTable || ' (
> > col1 char(10) not null )';
> >
> > 3) The comma after the last column definition would cause
> > a syntax error.
> >
> > This seems like a very non-relational solution to a
> > problem. Why not have a single table with a username
> > column in it?
> >
> > --
> > Reg Domaratzki, Sybase iAnywhere Solutions
> > Sybase Certified Professional - Sybase ASA Developer
> > Version 8 Please reply only to the newsgroup
> >
> > iAnywhere Developer Community :
> > http://www.ianywhere.com/developer ASA Patches and EBFs :
> > http://downloads.sybase.com/swx/sdmain.stm
> > -> Choose SQL Anywhere Studio
> > -> Set "Platform Preview" and "Time Frame" to ALL
> >
> > <Sue> wrote in message
> > > news:406c42a2.1b53.846930886@sybase.com... I'm using ASA
> > > 8.*. How to automatically create a new table (table2) if
> > > a row is added into another table (table1). The new
> > > table name will come from the new row just added in
> > table1. >
> > > Is that possible or not?
> > > Currently I have to manually create the new table2
> > > series by looking at the rows in the table1. It's not
> > > very nice. Any better way of doing this?
> > >
> > > I tried to use trigger in table1:
> > > =========================================
> > > ALTER trigger cgstest_add_af after insert order 1 on
> > > ttt.cgstest
> > > referencing new as new_name
> > > for each row
> > > /* WHEN ( search_condition ) */
> > > begin
> > > declare strTable char(10);
> > > if new_name.username is not null then
> > > set strTable=new_name.username;
> > > create table ttt.strTable(
> > > col1 char(10) not null,
> > > )
> > > end if
> > > end
> > > ==========================
> > >
> > > Note: --- the ttt. (the database log on user id)is
> > > automatically attacched in the create table statement
> > > when I save the trigger, even though I don't
> > >
> > > ===============
> > > Error: ASA error -267: COMMIT/ROLLBACK not allowed
> > > within atomic operation.
> > >
> > >
> > > Thanks
> > > Sue
> >
> >


Sue Posted on 2004-04-02 19:18:13.0Z
Sender: 2ca2.406dbc0e.1804289383@sybase.com
From: Sue
Newsgroups: ianywhere.public.general
Subject: Re: Automatically create table?
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <406dbc70.2ca4.846930886@sybase.com>
References: <406c42a2.1b53.846930886@sybase.com><406c5671$1@forums-1-dub> <406c6a34.1db7.846930886@sybase.com><406d84cf$1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 2 Apr 2004 11:18:08 -0800, 10.22.241.42
Lines: 131
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 2 Apr 2004 11:18:09 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 2 Apr 2004 11:18:13 -0800
X-Trace: forums-1-dub 1080933493 10.22.108.75 (2 Apr 2004 11:18:13 -0800)
X-Original-Trace: 2 Apr 2004 11:18:13 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2920
Article PK: 6389

Thanks for all your help. I will give up my previous
thought.

Sue

> Partitioning the data into separate tables is unlikely to
> address your performance issue. Have you tried running the
> Index Consultant in version 9?
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> ug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer
>
> <Sue> wrote in message
> > news:406c6a34.1db7.846930886@sybase.com... Thanks for
> > the info. The code I posted is just a test table. My
> > actually data is very big and cause performance issues.
> > I want to segment the data into differrent tables with
> > the same table definitions. So the main table only store
> > the name of the segmented tables and the actual data is
> in the segmented tables. >
> > I tried to use 'execute immediate' (The comma is a typo
> > there)and still have the problem of COMMIT.
> >
> > So, is trigger not a solution? What's other solutions
> > available?
> >
> > Thanks for your help.
> >
> > Sue
> >
> >
> > > Short Answer : No.
> > >
> > > Longer Answer :
> > >
> > > There are three problems with your trigger :
> > >
> > > 1) You cannot do a COMMIT in a trigger. All DDL (Data
> > > Definition Language) commands have an implicit COMMIT.
> > > 2) Even if you could COMMIT in a trigger, your trigger
> > > , as coded would also create a table called
> > > ttt.strTable. To use the value in the strTable
> > > variable you would need to use EXECUTE IMMEDIATE
> > >
> > > execute immediate 'CREATE TABLE ttt.' || strTable || '
> > > ( col1 char(10) not null )';
> > >
> > > 3) The comma after the last column definition would
> > > cause a syntax error.
> > >
> > > This seems like a very non-relational solution to a
> > > problem. Why not have a single table with a username
> > > column in it?
> > >
> > > --
> > > Reg Domaratzki, Sybase iAnywhere Solutions
> > > Sybase Certified Professional - Sybase ASA Developer
> > > Version 8 Please reply only to the newsgroup
> > >
> > > iAnywhere Developer Community :
> > > http://www.ianywhere.com/developer ASA Patches and
> > > EBFs : http://downloads.sybase.com/swx/sdmain.stm
> > > -> Choose SQL Anywhere Studio
> > > -> Set "Platform Preview" and "Time Frame" to ALL
> > >
> > > <Sue> wrote in message
> > > > news:406c42a2.1b53.846930886@sybase.com... I'm using
> > > > ASA 8.*. How to automatically create a new table
> > > > (table2) if a row is added into another table
> > > > (table1). The new table name will come from the new
> > > row just added in table1. >
> > > > Is that possible or not?
> > > > Currently I have to manually create the new table2
> > > > series by looking at the rows in the table1. It's
> > > > not very nice. Any better way of doing this?
> > > >
> > > > I tried to use trigger in table1:
> > > > =========================================
> > > > ALTER trigger cgstest_add_af after insert order 1 on
> > > > ttt.cgstest
> > > > referencing new as new_name
> > > > for each row
> > > > /* WHEN ( search_condition ) */
> > > > begin
> > > > declare strTable char(10);
> > > > if new_name.username is not null then
> > > > set strTable=new_name.username;
> > > > create table ttt.strTable(
> > > > col1 char(10) not null,
> > > > )
> > > > end if
> > > > end
> > > > ==========================
> > > >
> > > > Note: --- the ttt. (the database log on user id)is
> > > > automatically attacched in the create table
> > > > statement when I save the trigger, even though I
> > > don't >
> > > > ===============
> > > > Error: ASA error -267: COMMIT/ROLLBACK not allowed
> > > > within atomic operation.
> > > >
> > > >
> > > > Thanks
> > > > Sue
> > >
> > >
>
>