OAuth 2 Automation and JDBC calls

Question

Hi, I'm creating an OAuth 2 automation script to handle our login screen when calling an /api/jwt token retrieval.&nbsp; When getting the token, our login pages appear, for which I have added 4 pages of Automation to handle entering User Name, Password, etc.
&nbsp;
Page 1: Enter User Name screen
Page 2: Enter Password screen
Page 3: Request security token via Email or Text
Page 4: Enter valid security code which was received via Email or Text, and press Next
&nbsp;
Once I reach Page 4, I realized I had no way of getting the security code which our website sends me.&nbsp;
&nbsp;
So I began to consider my options.&nbsp; I felt like a JDBC call would work great and I could get the security token directly from the database.&nbsp; When I tried creating JDBC calls within the OAuth Automation script profile, it began to throw all sorts of errors, not being able to import groovy.sql.Sql, seeing a Sql.newInstance call as ReferenceError: Can't find variable: Sql]
&nbsp;
I thought maybe if I could call an outside Groovy script to return the value I might try that.&nbsp; So I created a script file which works, but attempting this type of code in the OAuth 2 automation screen fails.&nbsp;&nbsp;
&nbsp;
MyDBServer = "&lt;removed&gt;"MyUser = "&lt;removed&gt;"
MyDBName = "&lt;removed&gt;"
&nbsp;
Mysql = Sql.newInstance('jdbc:sqlserver://' + MyDBServer + ':1433;databaseName='+MyDBName+';integratedSecurity=true','','','com.microsoft.sqlserver.jdbc.SQLServerDriver')Mydata = Mysql.firstRow("SELECT MyCode FROM secured.MyAccessCodes AC JOIN secured.MyTable US ON AC.MyID = US.MyID Where US.UserName = '"+MyUser+"' and AC.ActiveFlag = 1")log.info(Mydata)Mysql.close()
&nbsp;
Any suggestions?&nbsp; I'd like to be able to add the database call directly into Step 4 of the OAuth 2 automation script in order to enter the security code into the window.&nbsp;&nbsp;

nmrao · Answer

Rmuenks1&nbsp;
&nbsp;
Would you please provide the following?
&nbsp;
1. Screen shot showing groovy script test along with script (fully visible). Also appreciate if you can attach the full script in text mode which will quickly help try.
&nbsp;
2. Assuming that you are using MS SQL Server. What driver / library files available under ReadyAPI?
&nbsp;
3. Full stacktrace of the error.

richie · Answer

Hi Rmuenks1,I'm not a coder so i can't help with your groovy, but i wanted to confirm some points to rule out any issues with the SQL.Your SQL is:SELECT mycodesFROM secured.myaccesscodes ac JOINsecured.mytable us ONac.myid = us.myidWHERE us.username = "'+myuser+'"AND ac.activeflag = 1Some RDBMSs struggle if you use partial aliasing.  You've aliased everything but the initial column you are retrieving in your result set, so rather than 'select mycodes from', instead use 'select ac.mycodes from'.Secondly i just wanted to ensure your notation is correct relative to the FROM and JOIN clauses.Your FROM/JOIN clauses read'FROM secured.myaccesscodes ac JOIN secured.mytable us'Can you confirm 'secured' is the database name, so 'myaccesscodes' and 'mytable' are the table names?  I struggled before using an earlier version of SoapUI (not ReadyAPI!) when i included the database name in the table identifier in my SQL.  Once i removed the database name reference so my table identifiers included only the table, my SQL worked fine, but didn't when i included the databasename.Cheers, sorry i can't comment on anything else,Rich

rmuenks1 · Answer

Thanks for the feedback regarding SQL, but this code works in a Groovy script.&nbsp;
&nbsp;
What I need help with is understanding in what ways the script editor inside the OAuth 2 profile GUI differs from the capabilities of a script inside a Groovy script. Also, if there is a limitation on what you can do within the OAuth 2 automation profile scripting, what options exist for making a SQL database call from within the automation profile to get our unique 6-digit security token during OAuth 2 automation profile to validate the user?
&nbsp;
When running the code in the original post in the OAuth2 automation scripts GUI I get an error that says:
ReferenceError: Can't find variable: Sql]
&nbsp;
if I use an "import Groovy.sql.Sql" at the beginning I get an error that says:&nbsp;&nbsp;
Error: syntax error (scriptToValidate#1)
&nbsp;
So, it seems like the "Automation scripts for OAuther 2 profile" screen has some limitations on what sort of code can execute within it, but I'm not finding anything in the documentation, or online, to indicate that limitation.
&nbsp;
Thanks again!

olga_t · Answer

Hi all,
&nbsp;
Thank you,&nbsp;richie, for your reply.
&nbsp;
Rmuenks1, let me refer to some of our Community leaders, maybe they will be able to assist.
&nbsp;
groovyguy&nbsp;,&nbsp;avidCoder&nbsp;,&nbsp;nmrao&nbsp;,&nbsp;HimanshuTayal, could you please have a look at the issue? Do you have any ideas or suggestions?
Community, feel free to post your comments!
&nbsp;
Thanks everyone in advance!

rmuenks1 · Answer

Hi,
&nbsp;
So when I press "get the token" it opens up our login pages, so I created 'automation' scripts to handle those login screens.&nbsp; Page 1 is the Enter User Name screen.&nbsp; Page 2, the Enter Password screen, Page 3, the Request authentication code screen, and Page 4, the Enter authentication code screen.
&nbsp;
What I'm trying to do is make a database call from within the automation script which goes to our database and gets the authentication token, rather than trying to get it from our emails, or text messages, which I would imagine is even harder to code into the automation script.&nbsp; But the SQL calls I'm making in the script shown below work fine if I pull them out into a separate Groovy script, just not inside the Oath2 automation script.&nbsp; So I'm curious why it's not working.&nbsp; After I get the authorization code from the database I intended on putting that in the field on the screen and pressing submit.
&nbsp;
Thank you,
Roy
&nbsp;
Page 1if (document.getElementById('txtUserName')) {document.getElementById('txtUserName').value = '${#Project#Prop_UserName}';document.getElementById('btnNext').click();}
Page 2if (document.getElementById('txtPassword')) {document.getElementById('txtPassword').value = '${#Project#Prop_Password}';document.getElementById('btnSignin').click();}
Page 3if (document.getElementById('MainContent_rblDeliveryMethods_0')) {document.getElementById('MainContent_rblDeliveryMethods_0').click();document.getElementById('btnRequestCode').click();}
Page 4if (document.getElementById('MainContent_AccessCodeRequiredFieldValidator')) {MyDBServer = "&lt;removed for security reasons&gt;";MyUser = "&lt;removed for security reasons&gt;";Mysql = sql.newInstance('jdbc:sqlserver://' + MyDBServer + ':1433;databaseName=&lt;removed for security reasons&gt;;integratedSecurity=true','','','com.microsoft.sqlserver.jdbc.SQLServerDriver');Mydata = Mysql.firstRow("SELECT AC.AccessCode FROM UserAccessCodes AC JOIN Users US ON AC.UserID = US.UserID Where US.UserName = '"+MyUser+"' and AC.ActiveFlag = 1");AuthCode = Mydata;Mysql.close();document.getElementById('MainContent_AccessCodeRequiredFieldValidator').value = AuthCode;document.getElementById('btnConfirmCode').click();}

Forum Discussion

OAuth 2 Automation and JDBC calls

Recent Discussions

Adding custom header to auth profile token call

Update API definition : Error with the json format

Test result Pass when data source times out

Related Content

Gmail OAuth 2.0 API Automation Example and example SubmitListener.beforeSubmit

JavaScript ECMA6 Asynchronous Calls

Automation Script OAuth 2.0 Azure not fully validated