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?

asked 20 Mar '14, 03:33

Serg's gravatar image

Serg
36347
accept rate: 0%

edited 20 Mar '14, 03:39

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 ) );   -- character

Also, 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".

SELECT f_verify_pwd ( 'DBA', '1' );
password must be at least 6 characters long
(20 Mar '14, 08:39) Breck Carter
Replies hidden

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

begin
    execute('grant connect to "' || @userName || '" identified by "' || @password || '"') 
end

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?

(20 Mar '14, 13:26) Serg

It would be helpful if you show us the actual (revised) code within f_verify_pwd() that you are using.

(20 Mar '14, 13:50) Mark Culp

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.

(20 Mar '14, 13:57) Breck Carter

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 :).

(21 Mar '14, 02:28) Serg

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.

permanent link

answered 20 Mar '14, 09:17

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×86
×63
×15

question asked: 20 Mar '14, 03:33

question was seen: 2,215 times

last updated: 21 Mar '14, 02:28