Cannot write to table. URGENT HELP REQUIRED.

Joe Boyle atla38 at dsl.pipex.com
Sat Jul 9 12:47:20 CDT 2005 

I also found the notes below, but without access to either SQLserver or
Axiant I am not able to recall much more in the way of detail, but I am sure
that Cognos support will be happy to provide further information.

The steps below illustrate how to define an identity column in SQLServer
along with the modifications required in Axiant. Having said that, my notes
arn't the clearest ever, but the details should get you some way along the
track.


MS SQL Server

1) Create a table with whatever columns you require (e.g. lastname,
firstname, address, etc.)
2) When defining the identity column, the column name must be IDENTITY_COL 
3) For the identity column ensure that the Identity column is checked off.
You can optionally specify the Identity Seed and Increment value.
4) Ensure you specify IDENTITY_COL as the Primary Key for the table
5) Declare any additional indexes you require
6) Save the table

Axiant
1) Import the table definition into Axiant via Import ODBC
2)On the Columns tab for the table, ensure that IDENTITY_COL has Key set to
"True"
3)On the Key tab ensure that IDENTITY_COL is defined as the Primary key
4)On the Indexes table ensure that there is a unique index with IDENTITY_COL
as the segment
5)Create a screen that uses the table
6)Once the screen is created make the following changes to the IDENTITY_COL
field on the Layout tab
  - Validation tab - Lookup should be "empty", Required should be "false"
  - Processing tab - Display should be "always", NoChange should be "false"
7) Build the screen and execute it.  You should be able to add, change and
find records.

Regards, Joe.


-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Syed Shahul Hameed Mustaffa
Sent: 09 July 2005 17:49
To: Joe Boyle
Cc: powerh-l at lists.sowder.com
Subject: Re: Cannot write to table. URGENT HELP REQUIRED.

Dear Joe,

Is just adding a autonumber field and setting it as a unique key enough?
I tried it and I could update the table without any change to the code.
Do u see any concern here?

Regards,
SYED.

On 7/9/05, Joe Boyle <atla38 at dsl.pipex.com> wrote:
> One other thought Syed, if you are going to use the SQL approach to update
> rows which are displayed on screen after retrieval via file
> statements/buffers, then you will probably need to add the 'autocommit'
> option to the file statement; the reason being that sqlserver probably
uses
> a holdlock which in turn will probably block the attempt to update the row
-
> the autocommit option will release any such lock (but you must map the
file
> as a reference file for this to apply).
> 
> As for the earlier comment regarding care with 'conventional updates',
this
> is now redundant because they are not possible without unique indexes :)
> 
> 
> Regards, Joe.
> 
> 
> -----Original Message-----
> From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
> [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf
Of
> Syed Shahul Hameed Mustaffa
> Sent: 09 July 2005 16:23
> To: Joe Boyle
> Cc: powerh-l at lists.sowder.com
> Subject: Re: Cannot write to table. URGENT HELP REQUIRED.
> 
> Dear Joe,
> 
> You're correct. I couldn't update.
> It says 'not all records are updated'.
> 
> I'll try to use the sql solution you've recommended.
> I'll let you know the results.
> 
> Best Regards,
> SYED
> 
> On 7/9/05, Joe Boyle <atla38 at dsl.pipex.com> wrote:
> > Could it be that the screen which did not error was actually adding new
> rows
> > to the database rather than updating existing rows ?
> >
> > Regards, Joe.
> >
> >
> > -----Original Message-----
> > From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
> > [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
Behalf
> Of
> > Syed Shahul Hameed Mustaffa
> > Sent: 09 July 2005 14:58
> > To: Joe Boyle
> > Cc: powerh-l at lists.sowder.com
> > Subject: Re: Cannot write to table. URGENT HELP REQUIRED.
> >
> > Dear Joe,
> >
> > Thanks again and it is great to hear from you.
> >
> > Are you sure about mandatory unique indexes?
> > I'm asking this question just because I could update the table using a
> > screen that I created with Axiant screen builder wizard.
> >
> > According to your statement, the update should have failed. Am I
correct?
> >
> > In PH(MPEIX), the same code writes to a table even without unique index.
> >
> > Regards,
> > SYED.
> >
> > On 7/9/05, Joe Boyle <atla38 at dsl.pipex.com> wrote:
> > > Example of embedded Sql approach is below, where t_hm and t_order are
> > > temporary items,
> > >
> > > let t_hm = truncate (concat_log ) + "add date"   &
> > > + ' JOTIME ' + d_hm[1:8]
> > >
> > > sql  in mydb transaction update &
> > > update concat_row                       &
> > > set concat_log = : t_hm where order_no = :t_order
> > >
> > >
> > > Regards, Joe.
> > >
> > >
> > > -----Original Message-----
> > > From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
> > > [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
> Behalf
> > Of
> > > Joe Boyle
> > > Sent: 09 July 2005 13:49
> > > To: 'Syed Shahul Hameed Mustaffa'; powerh-l at lists.sowder.com
> > > Subject: RE: Cannot write to table. URGENT HELP REQUIRED.
> > >
> > > You have to have a unique key/index for updates with each of RDBs
> > sqlserver
> > > sqlanywhere, and DB2. So either add an index or, use a SQL call with
> > insert
> > > or update.
> > >
> > > Regards, Joe.
> > >
> > >
> > > -----Original Message-----
> > > From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
> > > [mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
> Behalf
> > Of
> > > Syed Shahul Hameed Mustaffa
> > > Sent: 09 July 2005 09:57
> > > To: powerh-l at lists.sowder.com
> > > Subject: Cannot write to table. URGENT HELP REQUIRED.
> > >
> > > Hello gurus,
> > >
> > > I am using AXIANT 4GL with SQL Server 2000 backend.
> > > I am migrating the code from MPEIX to the above environment.
> > >
> > > In one screen, when I try to update, the update fails with no clear
> > message.
> > > It just says 'File cannot be updated' when it tries to write to a
> > > table without an unique index.
> > >
> > > It is not issuing any sql statements to sql server (i watched from sql
> > > profiler).
> > >
> > > What could be the possible reasons?
> > >
> > > Regards,
> > > SYED
> > >
> > > --
> > > = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > > Mailing list: powerh-l at lists.sowder.com
> > > Subscribe: "subscribe" in message body to
> > powerh-l-request at lists.sowder.com
> > > Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
> > > powerh-l-request at lists.sowder.com
> > > http://lists.sowder.com/mailman/listinfo/powerh-l
> > > This list is closed, thus to post to the list you must be a
subscriber.
> > >
> > > --
> > > = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > > Mailing list: powerh-l at lists.sowder.com
> > > Subscribe: "subscribe" in message body to
> > powerh-l-request at lists.sowder.com
> > > Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
> > > powerh-l-request at lists.sowder.com
> > > http://lists.sowder.com/mailman/listinfo/powerh-l
> > > This list is closed, thus to post to the list you must be a
subscriber.
> > >
> > >
> >
> > --
> > = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > Mailing list: powerh-l at lists.sowder.com
> > Subscribe: "subscribe" in message body to
> powerh-l-request at lists.sowder.com
> > Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
> > powerh-l-request at lists.sowder.com
> > http://lists.sowder.com/mailman/listinfo/powerh-l
> > This list is closed, thus to post to the list you must be a subscriber.
> >
> >
> 
> --
> = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> Mailing list: powerh-l at lists.sowder.com
> Subscribe: "subscribe" in message body to
powerh-l-request at lists.sowder.com
> Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
> powerh-l-request at lists.sowder.com
> http://lists.sowder.com/mailman/listinfo/powerh-l
> This list is closed, thus to post to the list you must be a subscriber.
> 
>

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.

More information about the powerh-l mailing list