nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to Execute a SQL Statement Before Deploying Extended Attribs

System

I am trying to deploy from extended attributes into a database table in MS SQL Server where the id column of the target table is automatically generated by the database. I've written code to separately query the database for the next id and set the appropriate extended attribute with that value. On deployment, I wish to push the whole row, with the ID to the database.

This can only work if the insertion of the row is preceded by a SQL statement that tells the database to allow insertion of the identity with the row. So, my question is this: how can I execute the sql statement and insert the row defined by my attribute map in the same deployment? I have tried using a sql element along with the dbschema element, as shown in the attached example, but if I supply the command line parameters for the sql element, it appears that the dbschema deployment does not occur. If I omit the command line params for the sql element, then it does not run, and deployment of the dbschema fails.

Can someone point out what I am doing wrong here, or describe another way to accomplish this?

Find more posts tagged with

Comments

Bill Klish

This can easily be accomplished using the Get External Value call capability documented in the Advanced Features section of the DataDeploy admin manual.

Basically you write a java class that gets the next id from your database and returns the value into your tuple for insertion into the database.

It is very easy to do. Let me know if you get stuck.

Migrateduser

Get External Value would help with getting the ID, but that problem has already been solved through other means. The ID is already part of the tuple, coming directly from the document's extended attributes. The problem is that I need to instruct the database to allow insertion of this value along with the rest of the row. Can I use a similar call to execute arbitrary SQL commands within the same "session" that I will be inserting a row via the dbschema element?

simple_line_graph.rptdesign

Bill Klish

The database connection object available to java is not the same one used by the actual deployment. So, I doubt this is an avenue for you.

Without knowing why you added this as an EA, I think you have a few options.

1) Scrap writing it into an EA and use the suggestion I mentioned before.
2) Change the database column to just be an integer and maintain the next id in a separate "sequences" table

Make sense?

Migrateduser

I understand what you're saying here... Just for background, I need to have the ID ahead of time because it needs to be referenced in another table. I'll check and see if there are any other constraints that would prevent me from moving the sequence to a new table and changing the identifier column to an integer. Seems kludgy, but it may work for our needs.

(Unless anyone out there knows of a way to execute the SQL statement first...)

Bill Klish

Well, you can populate the id in other tables with the same external value java component. Not sure what is kludgy about that.

Pre-running some sort of sql to enable a value as valid seems kludgy to me Smiley Happy

Migrateduser

I'll grant you that... it may be kludgy, but people are generally willing to perform a few contortions to stay in the realm of what they know. Learning to write an External Data Source implementation looked like a bigger investment than getting a line of SQL code to execute as part of a deployment. If you'll bear with me just a bit longer, please answer the question below.

Let's suppose that I have a dbschema involving two groups, where the second group must reference the ID created for the first group. Can I use a GetExternalValue implementation, to retrieve the id that would have been created during the insertion of the first group and include it as the value of a column in the second?

Migrateduser

I am trying to deploy from extended attributes into a database table in MS SQL Server where the id column of the target table is automatically generated by the database. I've written code to separately query the database for the next id and set the appropriate extended attribute with that value. On deployment, I wish to push the whole row, with the ID to the database.

This can only work if the insertion of the row is preceded by a SQL statement that tells the database to allow insertion of the identity with the row. So, my question is this: how can I execute the sql statement and insert the row defined by my attribute map in the same deployment? I have tried using a sql element along with the dbschema element, as shown in the attached example, but if I supply the command line parameters for the sql element, it appears that the dbschema deployment does not occur. If I omit the command line params for the sql element, then it does not run, and deployment of the dbschema fails.

Can someone point out what I am doing wrong here, or describe another way to accomplish this?

Hi jeremy3mc,

Sorry I didn't see this sooner, but with the Holidays and all... I just didn't get to it. Now that I'm back though, I can tell you about a nifty little feature in OD6.1.1 that would seem to be just what you're looking for!

From the quick look that I took, the config file that you sent should be OK for the new feature too! The feature is called "do-sql-and-exec" and you would simply run your deployment like this:

<ODHome>/bin/iwodcmd start doc_meta_dd -k iwdd=dotcom_meta -k iwdd_op=do_sql_and_exec -user_op=idInsert

For more info, see the OD6.1.1 docs and/or release notes.

Good Luck!
Mike

Migrateduser

Thanks, MikeS!

We're using OpenDeploy 6.0.2 at the moment, but I am hoping to upgrade and test 6.1.1 in the near future. As a follow up to the previous conversation, I ended up moving the identity column into a separate table and making the id column of the table where I was performing the inserts a plain numeric column. I do an insert on the new table to get the id, write that to the EA's for the file, then deploy the whole row to the original table to save the metadata for the file.

I'm looking forward to trying out the 6.1.1 feature, however, and will update the group when I have done so.