SQL Anywhere database - ASA 9.02 - - can't execute stored-procedure, because Space-Character in String

Author

ASA 9.02 - - can't execute stored-procedure, because Space-Character in String

M.Erlinger

2005-10-27, 7:40 am

Hello

i have following problem: i create from my PowerBuilder-Applikation the
users in the database - now i want to comment this users with her first-name
and second-name.
i have desinged a stored-procedure - and it works fine, besides if i define
first_name + ' ' + Second_name as the is_COMMENT-Parameter i get an
error-message...........

using the stored-procedure:
********************
****
call sp_set_user_Comment(
'user_login', 'Mike Username');
---> ERROR : Error in syntax for 'Username' in row 1.

if i call the stored-procedure
call sp_set_user_Comment(
'user_login', 'Mike')
......it works fine!!! - but have only one name as the comment.

the stored-procedure:
*******************
CREATE PROCEDURE "DBA"."sp_set_user_Comment"(in is_login char(20), IN
is_Comment char(50) )
begin
execute immediate 'COMMENT ON USER ' || is_Login || ' IS ' ||
is_Comment;
end
*******************

have anyone a clue or help for my problem??
many thank's in advance
Michael E.

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

Try this...

call sp_set_user_Comment(
'user_login', '''Mike Username''');

Breck

On 20 Oct 2005 07:20:37 -0700, "M.Erlinger"
<michael.erlinger@vienna.at> wrote:

>Hello
>
>i have following problem: i create from my PowerBuilder-Applikation the
>users in the database - now i want to comment this users with her first-name
>and second-name.
>i have desinged a stored-procedure - and it works fine, besides if i define
>first_name + ' ' + Second_name as the is_COMMENT-Parameter i get an
>error-message...........
>
>using the stored-procedure:
> ********************
****
>call sp_set_user_Comment(
'user_login', 'Mike Username');
>---> ERROR : Error in syntax for 'Username' in row 1.
>
>if i call the stored-procedure
>call sp_set_user_Comment(
'user_login', 'Mike')
>.....it works fine!!! - but have only one name as the comment.
>
>
>the stored-procedure:
>*******************
>CREATE PROCEDURE "DBA"."sp_set_user_Comment"(in is_login char(20), IN
>is_Comment char(50) )
>begin
> execute immediate 'COMMENT ON USER ' || is_Login || ' IS ' ||
>is_Comment;
>end
>*******************
>
>have anyone a clue or help for my problem??
>many thank's in advance
>Michael E.
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

M.Erlinger

2005-10-27, 7:40 am

mmhhh....o.k. - this work's fine...................but why??? and why three
' - at begin and end of the string

thank's
Michael

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schrieb im
Newsbeitrag news:seafl11gii1p4vo
ornnbvhh4doahf0dre8@
4ax.com...
> Try this...
>
> call sp_set_user_Comment(
'user_login', '''Mike Username''');
>
> Breck
>
> On 20 Oct 2005 07:20:37 -0700, "M.Erlinger"
> <michael.erlinger@vienna.at> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

Because the EXECUTE IMMEDIATE is passing a string containing a SQL
command to the engine to be parsed, and that string did not have
quotes around the comment when it hit the engine. You represent a
single quote inside a quoted string literal as two single quotes.

Breck

On 20 Oct 2005 07:55:00 -0700, "M.Erlinger"
<michael.erlinger@vienna.at> wrote:

>mmhhh....o.k. - this work's fine...................but why??? and why three
>' - at begin and end of the string
>
>thank's
>Michael
>
>
>
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> schrieb im
>Newsbeitrag news:seafl11gii1p4vo
ornnbvhh4doahf0dre8@
4ax.com...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Bruce Hay

2005-10-27, 7:40 am

The proper fix is to add the double quotes within the procedure, not in the
CALL. The user name is an identifier.

CREATE PROCEDURE DBA. sp_set_user_Comment(
in is_login char(20), IN is_Comment
char(50) )
begin
execute immediate 'COMMENT ON USER "' || is_Login || '" IS ' ||
is_Comment;
end

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

"M.Erlinger" <michael.erlinger@vienna.at> wrote in message
news:4357a7b5@forums
-1-dub...
> Hello
>
> i have following problem: i create from my PowerBuilder-Applikation the
> users in the database - now i want to comment this users with her
first-name
> and second-name.
> i have desinged a stored-procedure - and it works fine, besides if i
define
> first_name + ' ' + Second_name as the is_COMMENT-Parameter i get an
> error-message...........
>
> using the stored-procedure:
> ********************
****
> call sp_set_user_Comment(
'user_login', 'Mike Username');
> ---> ERROR : Error in syntax for 'Username' in row 1.
>
> if i call the stored-procedure
> call sp_set_user_Comment(
'user_login', 'Mike')
> .....it works fine!!! - but have only one name as the comment.
>
>
> the stored-procedure:
> *******************
> CREATE PROCEDURE "DBA"."sp_set_user_Comment"(in is_login char(20), IN
> is_Comment char(50) )
> begin
> execute immediate 'COMMENT ON USER ' || is_Login || ' IS ' ||
> is_Comment;
> end
> *******************
>
> have anyone a clue or help for my problem??
> many thank's in advance
> Michael E.
>
>

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

Although the doublequotes around the user id are a good idea, I'm not
sure "proper fix" applies <bg>...

....those weren't doublequotes in the call, they were doubled-up single
quotes. The problem was the COMMENT ON command looked like this when
passed through the EXECUTE IMMEDIATE:

COMMENT ON USER user_login IS Mike Username;

What it needs to be is this:

COMMENT ON USER user_login IS 'Mike Username';

....although this is good too :)...

COMMENT ON USER "user_login" IS 'Mike Username';

Breck

On 20 Oct 2005 10:47:16 -0700, "Bruce Hay"
< h_a_y~a_t~i_a_n_y_w_
h_e_r_e~d_o_t~c_o_m> wrote:

>The proper fix is to add the double quotes within the procedure, not in the
>CALL. The user name is an identifier.
>
>CREATE PROCEDURE DBA. sp_set_user_Comment(
in is_login char(20), IN is_Comment
>char(50) )
>begin
> execute immediate 'COMMENT ON USER "' || is_Login || '" IS ' ||
>is_Comment;
>end
>
>
>Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
>Developer Community at http://www.ianywhere.com/developer
>
>"M.Erlinger" <michael.erlinger@vienna.at> wrote in message
> news:4357a7b5@forums
-1-dub...
>first-name
>define
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Bruce Hay

2005-10-27, 7:40 am

You're right. Both the userid and the comment string should be enclosed with
the "proper" type of quotes in the procedure. If the comment string can
itself contain quotes, the "replace()" expression below can be used to
double them.

CREATE PROCEDURE DBA. sp_set_user_Comment(
in is_login char(20), IN is_Comment
char(50) )
begin
execute immediate 'COMMENT ON USER "' || is_Login ||
'" IS ''' || replace(is_Comment,'
''','''''') || '''';
end

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:kqmfl1lmisldi9e
486u89dt8gf85kv5sq1@
4ax.com...
> Although the doublequotes around the user id are a good idea, I'm not
> sure "proper fix" applies <bg>...
>
> ...those weren't doublequotes in the call, they were doubled-up single
> quotes. The problem was the COMMENT ON command looked like this when
> passed through the EXECUTE IMMEDIATE:
>
> COMMENT ON USER user_login IS Mike Username;
>
> What it needs to be is this:
>
> COMMENT ON USER user_login IS 'Mike Username';
>
> ...although this is good too :)...
>
> COMMENT ON USER "user_login" IS 'Mike Username';
>
> Breck
>
>
>
>
>
>
> On 20 Oct 2005 10:47:16 -0700, "Bruce Hay"
> < h_a_y~a_t~i_a_n_y_w_
h_e_r_e~d_o_t~c_o_m> wrote:
>
the[color=darkred]
is_Comment[color=dar
kred]
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

You guys WORK SO HARD at implementing the big, important, difficult
features, it must really annoy you when folks like me say...

REPLACE() is one of the things that make SQL Anywhere GREAT!

Breck

On 20 Oct 2005 11:54:26 -0700, "Bruce Hay"
< h_a_y~a_t~i_a_n_y_w_
h_e_r_e~d_o_t~c_o_m> wrote:

>You're right. Both the userid and the comment string should be enclosed with
>the "proper" type of quotes in the procedure. If the comment string can
>itself contain quotes, the "replace()" expression below can be used to
>double them.
>
>CREATE PROCEDURE DBA. sp_set_user_Comment(
in is_login char(20), IN is_Comment
>char(50) )
>begin
> execute immediate 'COMMENT ON USER "' || is_Login ||
> '" IS ''' || replace(is_Comment,'
''','''''') || '''';
>end
>
>
>Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
>Developer Community at http://www.ianywhere.com/developer
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:kqmfl1lmisldi9e
486u89dt8gf85kv5sq1@
4ax.com...
>the
>is_Comment
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links