I'a using SQL Anywhere 12 (18.104.22.16853). I have a problem with "f_verify_pwd" function. One can find its implementation in the product help under "verify_password_function option" entry. I'm developing webApp which works with SQL Anywhere 12 database using JDBC driver "jconn4.jar". I would like to give enduser possibility to change his password on demand. For this I've prepared stored procedure in which I call:
begin execute('grant connect to "' || @userName || '" identified by "' || @password || '"') end
It works as expected when stored procedure is called from ISQL:
call "DBA"."aStoredProcedureName" ('someUserName','aNewPassword1')
But when I call the procedure from webApp it doesn't work. After debagging, I've noticed that this code in "f_verify_pwd" function is ignored:
INSERT INTO pwd_chars SELECT row_num, substr( new_pwd, row_num, 1 ) FROM dbo.RowGenerator WHERE row_num <= length( new_pwd );
There is no insert to "pwd_chars" local temporary table. I've managed to get it work using this workaround:
select row_num as pos, substr(new_pwd,row_num,1) as c into #temp from dbo.RowGenerator where row_num <= length(new_pwd); ... drop table #temp;
Is something wrong with my scenario? Can someone help me to explain that behavior?
GRANT CONNECT has an automatic commit. How is the local temporary table created? By default rows would be deleted on COMMIT -- maybe you need to try ON COMMIT PRESERVE ROWS. Just a shot in the dark--as @Breck suggested, more details would help understand what is going on.
answered 20 Mar '14, 09:17
Ivan T. Bowman