This script is supposed to pop-up a warningBox when the badgeID that is entered is not found in the database but the script is missing the if( statement and running directly to the messageBox saying the user has logged-in as user None. What am I missing?
[code]
badgeID = event.source.parent.getComponent(‘txtBadgeID’).text
exists = system.db.runScalarQuery(“SELECT badge_id FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
if exists == “None”:
system.gui.warningBox(“Username Doesn’t Exist!”)
else:
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)
[/code]
Well I am guessing the exists query returns a NULL or a value. You are checking right now if the return value is a string “None”. I think you want:[code]badgeID = event.source.parent.getComponent(‘txtBadgeID’).text
exists = system.db.runScalarQuery(“SELECT badge_id FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
if exists == None:
system.gui.warningBox(“Username Doesn’t Exist!”)
else:
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)[/code]
When I try to log in into the application using the credentials from the database user Auth. I get this error:
[code]com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Error creating authentication profile.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:302)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:276)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:229)
at com.inductiveautomation.factorypmi.application.runtime.ClientGatewayConnection.doLogin(ClientGatewayConnection.java:90)
at com.inductiveautomation.ignition.client.gateway_interface.AbstractGatewayConnection.loginEncoded(AbstractGatewayConnection.java:524)
at com.inductiveautomation.ignition.client.gateway_interface.AbstractGatewayConnection.login(AbstractGatewayConnection.java:519)
at com.inductiveautomation.factorypmi.application.runtime.ClientPanel$LoginAction$LoginTask.run(ClientPanel.java:461)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: SQL exception during authentication
at com.inductiveautomation.ignition.gateway.authentication.impl.JDBCAuthenticator.authenticate(JDBCAuthenticator.java:232)
at com.inductiveautomation.ignition.gateway.authentication.AuthenticatorFailoverWrapper.authenticate(AuthenticatorFailoverWrapper.java:36)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.runLogin(Gateway.java:930)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:416)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(null)
Ignition v7.4.2 (b953)
Java: Sun Microsystems Inc. 1.6.0_29
[/code]
Have you ever been able to authenticate against that authentication profile? Do you get the same error if you try the “Verify Authentication Profile” link on the authentication profiles page?
What is your setup exactly? What database are you using? What authentication profile type are you using?
No I have never been able to authenticate against this profile. When I Verify the Authentication Profile I get this error
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'mapping.Rolename' in 'where clause'
Where is this “Where” clause located?
Setup:
Authentication Profile name : UserAuth
Type: Database
User table: users
- user_id
- username
- password
- badge_id
- role
Role table: roles
- role_id
- rolename
Mapping table: user_role_mapping
- user_id
- role_id
Database is MySQL - latest rev from site
Code on the Login button
[code]
badgeID = event.source.parent.getComponent(‘txtBadgeID’).text
exists = system.db.runScalarQuery(“SELECT badge_id FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
userName = system.db.runScalarQuery(“SELECT username FROM users WHERE badge_id LIKE ‘%s’” % badgeID)
if exists == None:
system.gui.warningBox(“Username Doesn’t Exist!”)
else:
success = system.security.switchUser(userName, badgeID, event)
system.gui.messageBox(“Welcome You are Logged in as: %s” % userName)
system.nav.closeParentWindow(event)
if not success:
event.source.parent.getComponent("txtBadgeID").requestFocusInWindow()[/code]
I see. Ignition builds queries for you to authenticate a user. Since your tables are a little different from what Ignition would create we need to use the “Expert Configuration” under advanced properties. So, edit your authentication profile. Uncheck the auto create checkbox and click on the advanced properties checkbox. Check the box to use expert configuration. You will see three fields to enter queries.
Your three queries should be:
- Authentication Query
SELECT Username FROM USERS WHERE Username = '$username$' AND Password = '$password$'2) Role List QuerySELECT Rolename FROM ROLES3) User’s Roles QuerySELECT r.Rolename FROM USER_ROLE_MAPPING m JOIN ROLES r ON r.Role_ID = m.Role_ID JOIN USERS u ON u.User_ID = m.User_ID WHERE u.Username = '$username$'Save and try authenticating.
That allowed me to verify the Auth Profile but when I run the Log-in script, the script fails with this error:
[code]com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Login failed.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:302)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:276)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:229)
at com.inductiveautomation.factorypmi.application.runtime.ClientGatewayConnection.doLogin(ClientGatewayConnection.java:90)
at com.inductiveautomation.ignition.client.gateway_interface.AbstractGatewayConnection.loginEncoded(AbstractGatewayConnection.java:524)
at com.inductiveautomation.ignition.client.gateway_interface.AbstractGatewayConnection.login(AbstractGatewayConnection.java:519)
at com.inductiveautomation.factorypmi.application.script.builtin.SecurityUtilities.login(SecurityUtilities.java:70)
at com.inductiveautomation.factorypmi.application.script.builtin.SecurityUtilities.switchUser(SecurityUtilities.java:93)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:455)
at org.python.core.PyObject.call(PyObject.java:422)
at org.python.core.PyObject.call(PyObject.java:426)
at org.python.pycode._pyx97.f$0(event:actionPerformed:25)
at org.python.pycode._pyx97.call_function(event:actionPerformed)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1261)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:539)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:152)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:261)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at $Proxy14.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Ignition v7.4.2 (b953)
Java: Sun Microsystems Inc. 1.6.0_29
[/code]
The Log-in script is on a button in my application. show in the post above
If you can verify the script should work. Make sure that authentication profile is attached to your project. Call us if you can’t get it to work.
Yep something simple, I forgot that I was using the default Auth profile in my project so I could get logged-in. Switched it back to the database Auth profile and everything works.
Thanks for all your help.