|
I'a using SQL Anywhere 12 (12.0.1.3153). 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. |
What exactly is the error message you are seeing? The phrase "it doesn't work" doesn't tell us anything.
Is it possible that your application code does not trap the error(s) you are getting? That is a common problem... everyone forgets to check :)
What exactly does "f_verify_pwd" have to do with your web app or your "aStoredProcedureName"? That is not clear from your question.
When you ask us to "explain that behavior", you first need to answer the question "what behavior?"
The example of "f_verify_pwd" shown in the SQL Anywhere 12 Help does contain a declaration of the pwd_chars table:
-- a table with one row per character in new_pwd DECLARE local temporary table pwd_chars( pos INT PRIMARY KEY, -- index of c in new_pwd c CHAR( 1 CHAR ) ); -- characterAlso, the code in that Help topic does work, if you make the change suggested by jbschueler 2013/08/02 08:50 The "ALTER LOGIN POLICY DEFAULT" statements should read "ALTER LOGIN POLICY root".
I will try to be more precise.
Initially my "f_verify_pwd" was exactly the same as we can find in the SQL Anywhere help.
What error message I can see?
When I fulfilled password requirements I got "password must contain at least one numeric digit". I'm sure I catch all exceptions.
What exactly does "f_verify_pwd" have to do with your web app or your "aStoredProcedureName"?
I'm developing JavaServer Faces app which is deployed on GlassFish app server. User requests to database are handled with Java CallableStatement which call SQL Anywhere stored procedures. But it is not the problem for me, it's not my first day with SQL and Java :). When problem appeared I deployed "f_verify_pwd" without "hidden" option. When turned on debug mode, debagger went into the code in my stored procedure
and in the next step it went into "f_verify_pwd". That is why I was sure that there was no insert into "pwd_chars" local temporary table (when I was debbaging this stored procedure called from ISQL the pwd_chars was filled with my new password chars).
"what behavior?"
When I'm calling stored procedure from ISQL as I wrote in my initial post, original "f_verify_pwd" worked as expected - when password was wrong I got proper messages, when it was OK - password was changed. When I'm calling stored prodedure form my webApp (using java CallableStatement) I was not able to change password - even it was OK (at least 6 chars, both upper- and lowercase characters, one numeric digit) - I got "password must contain at least one numeric digit". Debagger shows empty "pwd_chars".
And I repeat myself. When changed the code as I wrote in my initial post - everything went OK, proper messages when password wrong, password could be changed when met requirements, debagger shows "pwd_chars" filled as I expected.
After your post I've translated oryginal "f_verify_pwd" from Watcom-SQL to Transact-SQL - the fragment I've changed was almost the same as mine (Don't have the access to it now so I'm not posting the code). So, the problem can be connected with Watcom-SQL?
It would be helpful if you show us the actual (revised) code within f_verify_pwd() that you are using.
You may be seeing Characteristic Error Number 6: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL. :)
If you changed f_verify_pwd to contain a COMMIT statement (or any statement that implies COMMIT, like a GRANT) after the INSERT INTO pwd_chars statement, you will see that behavior because the COMMIT flushes the contents of pwd_chars. As Ivan said in his answer, you can change that behavior as follows:
-- a table with one row per character in new_pwd DECLARE local temporary table pwd_chars( pos INT PRIMARY KEY, -- index of c in new_pwd c CHAR( 1 CHAR ) ) -- character ON COMMIT PRESERVE ROWS;Of course, this is GUESSWORK without seeing the code you are using.
Thank you all of you colleagues. It works. Sorry for a lot of talking and this: "it's not my first day with SQL and Java" - let say it's my second day :).