Re: Update Problem

Eric Pearson

Re: Update Problem
(in response to Mike Bruzdzinski)
varchar
or
long varchar

If the former,
the message is telling you the truth.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 1:25 PM
To: [login to unmask email]
Subject: Update Problem


Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System








Eric Robida

Re: Update Problem
(in response to Michael Dempsey)
I do not use varchar much but don't you have to tell DB2 the length of the
text?




"Dempsey, Michael" <[login to unmask email]> on 01/09/2001 01:24:35 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Eric Robida/BCBSME)
Subject: Update Problem



Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System








Mike Bruzdzinski

Re: Update Problem
(in response to Eric Robida)
I suspect you need to update the length of the varchar variable witin the
program. You may even want to consider setting it to max of 1800 (by your
definition) prior to issuing the UPDATE.


-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 1:25 PM
To: [login to unmask email]
Subject: Update Problem


Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System








Michael Dempsey

Update Problem
Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System



Scott Trometer

Re: Update Problem
(in response to Eric Pearson)

I thought that the max string constant was 255. So wouldn't you have to do
this update in 2 or more steps, concating the first 255 bytes with the next,
and so on...Or does that only apply to inserts?

Update tab1
set c_text = 'first 255'
where Key = 'xxxx'



Update tab1
set c_text = C_text || 'next 255 bytes'
Where key = 'xxxx'

Etc...

Rajeev P. Dhanawade

Update Problem
(in response to Scott Trometer)
Michael,

My guess is that this is a static SQL where the string that you have
typed/pasted in your post is actually a host variable. If it were a dynamic
SQL built and executed in the program, you would not have a problem.

My suggestion would be either to define a group variable with S9(4) COMP as
the first element in your group and your text variable as the second element
in the group. Use the group variable name in your SQL instead of your text
variable. DO NOT FORGET to populate the first element with the length of the
text that you want to update into the column.

Hope this helps.


Regards,

Rajeev P. Dhanawade
Phone: 302-577-4659 ext. 221
Fax : 302-577-4454



Kirk Hampton

Re: Update Problem
(in response to John Taylor)
The max length of a character literal is 255, and the literal you have
posted here is 330+. Also, there is an apostrophe in the 5th line "get PFT's"
which would cause you a premature termination of the literal and get you a
syntax error even if the overall length was valid. So this statement is not
executable as coded. What tool is being used to execute this ?





"Dempsey, Michael" <[login to unmask email]> on 01/09/2001 12:24:35 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: Update Problem



Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System








John Taylor

Re: Update Problem
(in response to Rajeev P. Dhanawade)
I am not a DB2 expert but it looks like you have a premature ' embedded in
your text string after get PFT - marked red below.

-----Original Message-----
From: Dempsey, Michael [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 1:25 PM
To: [login to unmask email]
Subject: Update Problem


Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System








Michael Dempsey

Re: Update Problem
(in response to Kirk Hampton)
Hey Kirk.. Thanks.. The tool they're using is the command line processor
on their windows workstation. There's a function that allows you to create
update statemnets under the DB2 for Window's 98 Toolset....

-----Original Message-----
From: Kirk Hampton [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 2:24 PM
To: [login to unmask email]
Subject: Re: Update Problem


The max length of a character literal is 255, and the literal you have
posted here is 330+. Also, there is an apostrophe in the 5th line "get
PFT's"
which would cause you a premature termination of the literal and get you a
syntax error even if the overall length was valid. So this statement is
not
executable as coded. What tool is being used to execute this ?





"Dempsey, Michael" <[login to unmask email]> on 01/09/2001 12:24:35 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: Update Problem



Hey Folks...
I have an application person trying to execute an update statement to
replace a varchar column of 1800 bytes called c_text. Their sql is as
follows and the error follows that... any help would be appreciated..

UPDATE tab1
SET c_text = 'oday because she had some hyperkeratosis^on her previous
reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
thyroid function tests today. 2. RTC in late Fall for^influenza shots, etc.
3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000 23:01
DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by M.D.^'

WHERE cfk_mrno = 'xxxxxxxx'
AND c_discharg_date = 'xxxx-xx-xx'
AND c_udocnum = '01007519'
AND c_doc_type = 'g2'
AND c_text_seq = 2;


DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:
SQL0102N The string constant beginning with "oday becau" is too long.
SQLSTATE=54002


Michael Dempsey
UNC Health Care System













Steven Mallett

Re: Update Problem
(in response to Michael Dempsey)
This might look rather inelegant and cumbersome but we've got around similar
problems in the past by segregating the update statement into a series of
update statements along the lines of

update tab1 set c_text = ' first n characters '
where predicates . . . ;
update tab1 set c_text = c_text CONCAT ' next n characters ' where
predicates . . . ;
update tab1 set c_text = c_text CONCAT ' next n characters ' where
predicates . . . ;
. . .

where n is whatever works for you.

regards,
Steve

I 2nd Floor 484 St Kilda Rd, Melbourne

' (03) 9865 8557 7 (03) 9804 5368
* <mailto: [login to unmask email]>



> -----Original Message-----
> From: Dempsey, Michael [SMTP:[login to unmask email]
> Sent: Wednesday, January 10, 2001 6:42 AM
> To: [login to unmask email]
> Subject: Re: Update Problem
>
> Hey Kirk.. Thanks.. The tool they're using is the command line processor
> on their windows workstation. There's a function that allows you to
> create
> update statemnets under the DB2 for Window's 98 Toolset....
>
> -----Original Message-----
> From: Kirk Hampton [mailto:[login to unmask email]
> Sent: Tuesday, January 09, 2001 2:24 PM
> To: [login to unmask email]
> Subject: Re: Update Problem
>
>
> The max length of a character literal is 255, and the literal you
> have
> posted here is 330+. Also, there is an apostrophe in the 5th line "get
> PFT's"
> which would cause you a premature termination of the literal and get you a
> syntax error even if the overall length was valid. So this statement is
> not
> executable as coded. What tool is being used to execute this ?
>
>
>
>
>
> "Dempsey, Michael" <[login to unmask email]> on 01/09/2001 12:24:35 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Kirk Hampton/Texas Utilities)
> Subject: Update Problem
>
>
>
> Hey Folks...
> I have an application person trying to execute an update statement to
> replace a varchar column of 1800 bytes called c_text. Their sql is as
> follows and the error follows that... any help would be appreciated..
>
> UPDATE tab1
> SET c_text = 'oday because she had some hyperkeratosis^on her previous
> reading. Her bronchitis may actually be allergic.^^PLAN: 1. Will check
> thyroid function tests today. 2. RTC in late Fall for^influenza shots,
> etc.
> 3. Trial albuterol inhaler. If not improving in a few^weeks, will need to
> get PFT's.^^^^, MD^^^^TSC:EDiX13180^D: 08/22/2000 12:28 T: 08/22/2000
> 23:01
> DOCUMENT: 200008221210751900^^^Electronically signed on 08/23/2000 by
> M.D.^'
>
> WHERE cfk_mrno = 'xxxxxxxx'
> AND c_discharg_date = 'xxxx-xx-xx'
> AND c_udocnum = '01007519'
> AND c_doc_type = 'g2'
> AND c_text_seq = 2;
>
>
> DB21034E The command was processed as an SQL statement because it was not
> a
>
> valid Command Line Processor command. During SQL processing it returned:
> SQL0102N The string constant beginning with "oday becau" is too long.
> SQLSTATE=54002
>
>
> Michael Dempsey
> UNC Health Care System
>
>
>
> the
>
>
>
>
>
> the
>
>
>
>
>
>
>