Dear B.A.T.
On 06 April 2004 at 14:40:29 +0200 (which was 13:40 where I live) Svensson,
B.A.T. (HKG) might have written
> Haven't tested your suggestion yet, but just look through it more
> carefully now. (Comments follow below).
> In general:
> The only special thing I can see that you are using in your code,
> that I am not using in my stored procedures are transactions
I always use transactions because I don't want information in the tables with
out the correct links in it.
> I think so at least. Except for that I am using all other stuff, and have
> no problem with it.
>>> So I am curious about this. Would you like to explain this a little bit more
>>> in details?
< ... >
>> set @PerId = @@identity
> This looks strange, id must be an identity column for this to work,
The SQL was slight incorrect sorry about that. Below is the full SQL I need
to insert.
=============8<=============================================================
begin transaction
set nocount on
declare
@PerId int
, @ConId int
, @DelID int
, @BilID int
insert into
WebItem (ItemTypeid,Name,LastUpdated,LegacyId)
Values(42,'',Current_timestamp,'090974248')
set @PerId = @@identity
insert into
WebItem (ItemTypeid,Name,LastUpdated,LegacyId)
Values(48,'xxxxx',Current_timestamp,'090974248')
set @ConId = @@identity
insert into
WebAddress
(ItemId,CompanyName,Bldg,Street,Area,District,Town,County,PCode,CountryId,Phone,AttLine)
Values
(@ConId,'xxxxx','xxxxx','xxxxx',''
,'','','','xxxxx','258'
,'','')
insert into
WebItem (ItemTypeid,Name,LastUpdated,LegacyId)
Values(50,'xxxxx',Current_timestamp,'090974248')
set @DelID = @@identity
insert into
WebAddress
(ItemId,CompanyName,Bldg,Street,Area,District,Town,County,PCode,CountryId,Phone,AttLine)
Values
(@DelId,'xxxxx','xxxxx','xxxxx',''
,'','','','xxxxx','258'
,'','')
insert into
WebItem (ItemTypeid,Name,LastUpdated,LegacyId)
Values(49,'xxxxx',Current_timestamp,'090974248')
set @BilID = @@identity
insert into
WebAddress
(ItemId,CompanyName,Bldg,Street,Area,District,Town,County,PCode,CountryId,Phone,AttLine)
Values
(@BilId,'xxxxx','xxxxx','xxxxx',''
,'','','','xxxxx','258'
,'xxxxxx','')
insert into
WebPerson
(ItemId,CompanyName,FirstName,LastName,Dear,TitleID,EmailAddress,Phone1,Phone2,Fax,PWord
,PwordPhrase,VatNO,ContID,BillID,DeliID,DistId,ContTyp,CustTyp,MailList,UpdateMe)
Values
(@PerId,'xxxxx','David','Elliott'
,'','141','b@xxx','xxxxxx',''
,'','password','','drff'
,@ConId,@BilId,@DelId,'50482','email','Other','','')
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50385,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50386,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50393,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50395,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50399,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50403,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50406,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50409,@PerId,current_timestamp)
insert into WebItemlink
(itemlinktypeid,itemid,ite_itemid,lastupdated)
values
(52,50410,@PerId,current_timestamp)
set nocount off
commit
select
@PerId PersId
,@ConId ContId
,@BilId BillId
,@DelId DeliId
=============8<=============================================================
> and since you have not turned on identity insert I assume id is not an
> identity column.
< ... >
>> set nocount off
> Why do you turn on row counts just before you return the result?
> This might create problems in the receiving end.
This way you get a record count of 1 for that select statement you are just
about to execute.
>> commit
>>
>> select
>> @PerId PersId
>> ,@ConId ContId
>> ,@BilId BillId
>> ,@DelId DeliId
>>
>> =============8<=============================================================
>>
>> 2) You sometimes (i.e. not always therefore not reproducible) get a CGI error
>>
>> if you use header("Location:http://xxx.xxx.xxx.xx";) to redirect address
>> after a database query, you will sometimes get CGI Error message.
>>
>> Here is the CGI Error message source code:
>>
>> =============8<=============================================================
>>
>> <head><title>Error in CGI Application</title></head>
>> <body><h1>CGI Error</h1>The specified CGI application misbehaved by not
>> returning a complete set of HTTP headers. The headers it did return
>> are:<p><p><pre></pre>
> I recognize this error, but I get it in another context, namely when
> stderr is not redirect to stdout when I execute a shell command with
> popen(). It might be that the SQL server sends an error message form time
> to time(?) (would it help to do a "set ansi_warnings off"?)
Good pointer. I will try that.
> The other reason why you might get this error is - but I am not 100%
> sure about this, since I got it some times when I worked with db calls,
> and just fixed the fault but forgot to document the fault it self -
> because some of the return values are nulls. You can fix this by
> typically doing like this:
> SELECT
> isnull(@PerdId, -1) PersId,
> isnull(...),
> etc, etc..
> Might be worth to test?
I might have a go at that as well
--
Bye, _______________________________________________
David | David Elliott | Software Engineer |
_________________________| list@xxxxxxxxxxxxxx | PGP Key ID 0x650F4534 |
| Demon Spawn Graphix (C) 1995 by McFly |
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
