This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Custom Datatype in sql table

Hello,

I have a custom datatype, which is not view-able through sync editor.

For example below CAMSUser attribute, which is having dependency on that application and not advisable to change the datatype supported by Q1IM7.0

Please advice me on how to proceed further for mapping.

Thanks.

Regards,

Vijay

0 Markus Weiss-Ehlers over 8 years ago
I think you have two options here.

Option 1:
Create a view in your database to fetch the data from the table. In that view you convert the custom data types to standard data types. For example UTszUser:varchar(15) to varchar(15). The database connector should be able to see the data then.

Option 2:
Use the ADO.NET generic database connector. You can define your own select - statements here to fetch the data. The statements would need to convert the data-types as well.

You probably need to customize the data operations for the tables using the custom data types if you want to write to them. Depending on the option you choose, you will find some information in the online manuals.

Search for define data operations in the connector for SQL server databases manual:

https://documents.software.dell.com/identity-manager/7.1/native-database-connector-user-guide-for-connecting-sql-server-databases/native-database-connector-for-sql-server-databases/creating-a-synchronization-project/connecting-a-system-to-an-sql-server-database

Search for commit data changes in the connector for the generic ADO.net provider manual:

https://documents.software.dell.com/identity-manager/7.1/native-database-connector-user-guide-for-the-generic-ado.net-provider/native-database-connector-for-the-generic-ado-net-provider/creating-a-synchronization-project/connecting-a-system-using-a-generic-ado-net-provider
Cancel
Up 0 Down

Cancel
0 vijay.anand over 8 years ago

Thanks Markus, will try your options.
Cancel
Up 0 Down

Cancel
0 vijay.anand over 8 years ago

Hi Markus,

I tried your 1st option on writing the data on view.
For some reasons am unable to write on the view.
Could you please advice me on the scripting on data definition.

Thanks & Regards,
Vijay
Cancel
Up 0 Down

Cancel
0 Markus Weiss-Ehlers over 8 years ago

How did you tried to write to a view? Without a data modification script or single-line DML code your are unable to write to the view. As a view is defined as being read-only.

You can take a look at the code snippets for the script code in the script editor of the database connector. Choose the snippet icon from the toolbar or press F2 and select a snippet for a head-start.
Cancel
Up 0 Down

Cancel
0 vijay.anand over 8 years ago

Markus,

Could you please give me screen shot and steps so that it will be easy for me to understand.
Am trying to push value from my UNSAccountB to my application table.

Thanks,
Vijay
Cancel
Up 0 Down

Cancel
0 Markus Weiss-Ehlers over 8 years ago

You haven't even answered my question? How did you tried to test the write to that view?

Here is the screenshot that shows you the snippets i have talked about.
Cancel
Up 0 Down

Cancel
0 vijay.anand over 8 years ago

Hi Markus,

I tried to push the value with sync editor, As the view is read-only it was failing.

As per my understanding, even in view also we can write with the help of script.

In my insert Snippet am not getting the options same as your screen and also could you please guide me how the script should look.

Thanks.

Vijay
Cancel
Up 0 Down

Cancel

0 Markus Weiss-Ehlers over 8 years ago

The view is getting to be writeable, as soon as you create your own data operations (either scripting or pattern based).

Which version of 7.0 are you using? (Screenshot is from 7.0.2)

This is the sample code from a snippet for an insert.

IValueStore store = data.Values.CreateValueStore();

connection.ExecuteSqlNonQuery(store.Replace("CREATE USER \"%ColumnName1%\" IDENTIFIED BY \"%ColumnName2%\"", true));
connection.ExecuteSqlNonQuery(store.Replace("GRANT \"CONNECT\" TO \"%ColumnName1%\"", true));

// Fetch auto-generated primary key from database
object key = connection.ExecuteSqlScalar(store.Replace("SELECT PKColumn FROM TableName WHERE USERNAME = '%ColumnName1%'", true));

// Return key value (You may have to convert the key to correct datatype)
return new ScriptBasedDMLStrategyResult("PKColumn", Convert.ToInt64(key));

If you use the pattern based data operations you can execute a single line of SQL code and have access to the columns of your view using %columnname%.

Sample for calling a procedure: (from the online documentation)

exec CreateUser('%Uid%','%FirstName%','%LastName%')

Markus

0 vijay.anand over 8 years ago

Thanks Markus.

Am using 7.0.1.

Regards,
Vijay
Cancel
Up 0 Down

Cancel
0 vijay.anand over 8 years ago

Hi Markus,

Am pushing the value to view, below is data operations.

I have done with the mappings, Please see the below sync log. I see data is populating but still getting error.

Please advice me.

Thanks,

Vijay
Cancel
Up 0 Down

Cancel