Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

mysql DB Provisioning

Go to solution
PrashantG
New Contributor III
New Contributor III

‎10/30/2023 11:41 PM

Hi Team,

created one mysql app based on 

Tutorial for Creating a Custom Connector using the Saviynt Connector Framework (saviyntcloud.com)

using below JSON for provisioning

{
"query": [
"insert into mysql_saviynt_dev_db.accounts(name, firstname, lastname ) VALUES ('${ACCOUNTNAME}' ,'${user.firstname}','${user.lastname}')"
]
}

getting below errors in log...though this syntax works in mysql workbench, as PK is auto incremented.

also not getting any generated query in the logs, which actually reduced for any hint for correction

2023-10-31T06:07:33.392+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGCreate account JSON String is {     
2023-10-31T06:07:33.850+00:00ecm-worker    query": [        


2023-10-31T06:07:33.850+00:00"		ecm-worker      insert into mysql_saviynt_dev_db.accounts(name firstname lastname ) VALUES ('${ACCOUNTNAME}' '${user.firstname}''${user.lastname}')"   


2023-10-31T06:07:33.850+00:00"		ecm-worker    ]        
2023-10-31T06:07:33.850+00:00ecm-worker  }        
2023-10-31T06:07:33.425+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGtemplateCreateAccountJson is groovy.text.SimpleTemplateEngine$SimpleTemplate@28b2db7f
2023-10-31T06:07:33.425+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGlooping for pghotika31      
2023-10-31T06:07:33.426+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGpassword policyRule: null      
2023-10-31T06:07:33.426+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGMinLenPwd : null maxLenPwd : null noOfCAPSAlpha : null noOfSplChars : null noOfDigits : null
2023-10-31T06:07:33.426+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGplease set policyRule in securitysystems: NPC_MySQL_DB_SS   
2023-10-31T06:07:33.426+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGEither conn params are not set or all conn params are not set and hence generating random password 
2023-10-31T06:07:33.426+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGminLen: null, maxLen: null, noOfCAPSAlpha: null, noOfDigits: null, noOfSplChars: null 
2023-10-31T06:07:33.426+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGtask.password is null or empty, so setting random password   
2023-10-31T06:07:33.428+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGACCOUNTEXISTSJSON is empty .. returning false..    
2023-10-31T06:07:33.428+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGInside !AccountExists...      
2023-10-31T06:07:33.428+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGcreating account in DB      
2023-10-31T06:07:33.429+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGEnter buildBindingMap      
2023-10-31T06:07:33.430+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGUSE_DEFAULT_ATTR_VALS_IN_REQUEST =     
2023-10-31T06:07:33.430+00:00ecm-workerservices.SaviyntCommonUtilityServicequartzScheduler_Worker-6DEBUGusedefaultval = false       
2023-10-31T06:07:33.435+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGusermanager = kaughosh      
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGCreate account is not an ARRAY..     
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGEnter executeStatement      
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGEnter checkForStoredProcCall      
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGin lowercaseQuery null       
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGStmt is not stored proc or function call     
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGExit checkForStoredProcCall      
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6ERRORError occured in Excecuting Query     
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGClosing statements       
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGClosing stmt       
2023-10-31T06:07:33.436+00:00ecm-workerrest.RestUtilServicequartzScheduler_Worker-6DEBUGGot showLogs = true       
2023-10-31T06:07:33.436+00:00ecm-workerrest.RestUtilServicequartzScheduler_Worker-6DEBUGGot showLogs = true       
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6ERRORError in create account - java.sql.SQLException: Can not issue NULL query. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.StatementImpl.checkNullOrEmptyQuery(StatementImpl.java:383) at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1252) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) at java_sql_Statement$executeUpdate$9.call(Unknown Source) at com.saviynt.provisoning.DBProvisioningService.executeStatement(DBProvisioningService.groovy:238) at com.saviynt.provisoning.DBProvisioningService.this$2$executeStatement(DBProvisioningService.groovy) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1031) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:145) at com.saviynt.provisoning.DBProvisioningService$_createAccount_closure2.doCall(DBProvisioningService.groovy:434) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.callClosureForMapEntry(DefaultGroovyMethods.java:3875) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1354) at org.codehaus.groovy.runtime.dgm$149.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.provisoning.DBProvisioningService.createAccount(DBProvisioningService.groovy:331) at com.saviynt.provisoning.DBProvisioningService$createAccount.call(Unknown Source) at com.saviynt.ecm.services.ArsTaskService.createAccountTarget(ArsTaskService.groovy:11620) at com.saviynt.ecm.services.ArsTaskService$createAccountTarget$29.call(Unknown Source) at com.saviynt.ecm.services.ArsTaskHelperService$_whenTaskTypeIsThreeNewAccountAccess_closure50.doCall(ArsTaskHelperService.groovy:3066) at sun.reflect.GeneratedMethodAccessor3869.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.callClosureForMapEntry(DefaultGroovyMethods.java:3875) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1354) at org.codehaus.groovy.runtime.dgm$149.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.ecm.services.ArsTaskHelperService.whenTaskTypeIsThreeNewAccountAccess(ArsTaskHelperService.groovy:3057) at sun.reflect.GeneratedMethodAccessor3014.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1031) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66) at com.saviynt.ecm.services.ArsTaskHelperService$_completeAutoProvTasksUpgraded_closure1.doCall(ArsTaskHelperService.groovy:175) at sun.reflect.GeneratedMethodAccessor2652.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90) at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1086) at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1110) at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:910) at groovy.lang.Closure.call(Closure.java:411) at groovy.lang.Closure.call(Closure.java:427) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1325) at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1297) at org.codehaus.groovy.runtime.dgm$148.invoke(Unknown Source) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) at com.saviynt.ecm.services.ArsTaskHelperService.completeAutoProvTasksUpgraded(ArsTaskHelperService.groovy:160) at com.saviynt.ecm.services.ArsTaskHelperService$completeAutoProvTasksUpgraded$5.call(Unknown Source) at MultipleProvisioningJob.execute(MultipleProvisioningJob.groovy:222) at sun.reflect.GeneratedMethodAccessor3273.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:196) at org.codehaus.groovy.grails.plugins.quartz.GrailsJobFactory$GrailsTaskClassJob.execute(GrailsJobFactory.java:72) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
2023-10-31T06:07:33.850+00:00ecm-worker           
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6DEBUGStatus after inserts... false      
2023-10-31T06:07:33.436+00:00ecm-workerprovisoning.DBProvisioningServicequartzScheduler_Worker-6ERRORError while creating account -pghotika31 stopping the Job   
Labels:
0 Kudos
7 REPLIES 7

Go to solution
SB
Saviynt Employee
Saviynt Employee

‎11/02/2023 02:16 PM

Does the query work if you hardcode the values instead of using variable.


Regards,
Sahil
0 Kudos

Go to solution
PrashantG
New Contributor III
New Contributor III

‎11/02/2023 08:53 PM

yes...have tested this..

e.g.

insert into mysql_saviynt_dev_db.accounts(name, firstname, lastname ) VALUES ('pghotika_3Nov' ,'pname','gname')

DB log

PrashantG_0-1698983555536.png

 

0 Kudos

Go to solution
SB
Saviynt Employee
Saviynt Employee
In response to PrashantG

‎11/06/2023 01:39 PM - edited ‎11/06/2023 01:41 PM

did you run this directly in the DB or used it in Saviynt for CreatAccount JSON

Also, below is the format to be used when defining the Create Account JSON

{
"createaccountqry": "insert into users (id,displayname,email,empid,firstname,lastname,systemname,username,password) values (${user.id}, '${user.displayname}', '${user.email}', '${user.employeeid}', '${user.firstname}','${user.lastname}','${user.systemusername}', '${user.username}','${randompassword}')) "
}

https://docs.saviyntcloud.com/bundle/Database-v23x/page/Content/Configuring-the-Integration-for-Prov...


Regards,
Sahil
0 Kudos

Go to solution
PrashantG
New Contributor III
New Contributor III
In response to SB

‎11/06/2023 08:58 PM

Hi Sahil,

The one which you are referring is creating user in Saviynt.

I am trying out to create account in mysql DB application with simple insert statements, which is mentioned above. The table is also simple as mentioned the saviynt doc.

I am not getting any lead for to correct the error as nothing is getting printed in the logs..only "can not issue NULL query".

Hope this helps...

0 Kudos

Go to solution
SB
Saviynt Employee
Saviynt Employee

‎11/07/2023 10:12 AM

The above was just a reference example of the format you need to use for the JSON. Can you define you inster query inside the below format and check

{
"createaccountqry": "<Add Insert query> "
}


Regards,
Sahil
0 Kudos

Go to solution
PrashantG
New Contributor III
New Contributor III
In response to SB

‎11/07/2023 09:33 PM

Hi Sahil,

thanks for revert.

what I understand and confirmed that keyword "createaccountqry" is mandatory for this query execution....if I change this to any other value e.g. "query" this doesn't work.

so 

This works

{
"createaccountqry": 
"insert into mysql_saviynt_dev_db.accounts(name, firstname, lastname ) VALUES ('${ACCOUNTNAME}' ,'${user.firstname}','${user.lastname}')"
}

but same query with different "key" (other than "createaccountqry") doesn't work

This doesn't work

{
"qry": 
"insert into mysql_saviynt_dev_db.accounts(name, firstname, lastname ) VALUES ('${ACCOUNTNAME}' ,'${user.firstname}','${user.lastname}')"
}

 

0 Kudos

Go to solution
SB
Saviynt Employee
Saviynt Employee
In response to PrashantG

‎11/08/2023 08:07 AM

That is correct, the keyword before the query is mandatory and should be defined as is. Similarly for other actions as well that have been mentioned in the Documentation.


Regards,
Sahil
Copyright © 2024 Khoros, LLC