SQL Anywhere database - Converting binary to string

Author

Converting binary to string

Oliver Schulze L.

2005-08-24, 7:23 am

Hi,
I noted that when I run this SQL statement:
grant connect to user1 identified by '123';
while creating/saving a sp/function, it is transformed to:
grant connect,to user1 identified by encrypted
0x5818F03D1898C6E3CC
21224773560713AA6B21
7430B06B0587F13667DC
26DA40D6593529
by Sybase Central.

I wonder, can I do this too?

I need to store the ASA user password for replicating to
other remote DBs.

So, I need to do something like this in a sp:

declare passwd binary(36);
declare passwd_str varchar;
declare execute_tmp varchar;
select passwd_binary into passwd
from dba.myusers
where user_id = 123;
set passwd_str = FUNCTION(passwd);
set execute_tmp=string('
grant connect to user1 identified by encrypted
''',passwd_str,''' ');

Don't know is passwd_str must be a varchar or a long.

Is there a function to do this?

My problem is that one user has a ' character in its binary password,
and that breaks the execute_tmp string.

I'm on an old ASA v6.0.4

Thanks
Oliver

Oliver Schulze L.

2005-08-25, 8:23 pm

Well, I done it using a custom function,
here it is if someone needs it.

create function dba.f_passwd_to_hex(in arg_passwd binary(36))
returns varchar(72)
begin
/*
* varchar(72) dba. f_passwd_to_hex(asa_
passwd);
*
* news.oliver@samera.com.py
*
* Release:
* 24-ago-2005
*/
declare i integer;
declare str_tmp varchar(10);
declare resultado varchar(72);
set i=1;
set resultado='';
// recorrer todo el passwd
while i <= 36 loop
// obtener uno a uno los caracteres
set str_tmp=inttohex(BYT
E_SUBSTR(string(arg_
passwd),i,1));
// en str_tmp hay 8 caracteres, del 1 al 6 estan en 0 siempre
set resultado=string(res
ultado,substr(str_tm
p,7));
//message 'i: ',i,' str_tmp: ',str_tmp,' resultado: ',resultado
type info to console;
set i=i+1
end loop;
return resultado
end
go

Use it like this in a sp:
set passwd_binary_hex=db
a. f_passwd_to_hex(pass
wd_binary_tmp);
set execute_tmp=string('
grant connect to ',db_user_name_tmp,'

identified by encrypted 0x',passwd_binary_he
x,' ');
execute immediate execute_tmp

HTH
Oliver

Sponsored Links