Pre SQL for InDB

[saubert]

Hello,

We use the pre-sql statement of the input to set some parameters of connections. Sadly, we cannot do that in a in-db workflow. This would be a total game-changing feature for us.

Best Regards,

Simon

[simonaubert_bd]

@avinavbI think this is just a question of priority and visibility. in-database doesn't seem that important to some people at Alteryx. If you have a look at all the ideas, a lot are well positionned, many votes, etc.

Maybe with more votes and comments on all in db ideas we would see a change? I make my co-workers and customers vote for it.

Best regards,

Simon

[mestevez1]

Upvoted

[pinecone]

Any updates on this?

[CharlesW]

Lack of Pre/Post SQL with In-DB is still an issue.

First I looked into some way to feed in the info I need to delete into a dynamic In-DB but you can only execute 1 sql statement so no matter how I tried to figure out how to get around this limitation it just wasn't going to work.

Now since we have control containers I tried a few new angles but I'm more confused now.

Trying to make sure to clear out the table holding the portion of data the user is attempting to replace without just emptying the table or appending additional data to the old data.

I've set up a batch macro to execute a select top 1 statement and used the control parameter to update the pre or post sql statement.

Thereby creating a dynamic delete from <table> where <user input> 

This works on the normal tools so I tried to do that and leverage the control containers to make sure it happens before the new write happens.   Unfortunately it throws an error that In-DB cannot cross the control container boundaries.  

Ok this makes sense, so all In-Db tools need to be in the same container because in reality it's just one bit long SQL statement.

Next I tried:

So I put them all in the same container not just the write to table and I get the same error.   

Now I'm confused.....

Next I tried:

So I set up a dynamic In-DB and used the control containers to execute the delete before even building the In-DB query.  

That's not working either.  Even though the dynamic In-DB query hasn't even been built until after the delete happens, and not using a control container around the In-DB tools.

Resulted in an append and never deleted anything.

Any other ideas?

[CharlesW]

Ok Long story short, it does work!

Using control containers use the normal dynamic input tool do a Select * from <table> where 'xxxxxx'

In the post-sql update your table based on DELETE FROM <table> where 'xxxxxx'

Put this inside a batch macro with a control parameter, where you map your field to identify the records you want to delete to both inputs.

In the dynamic input update 'xxxxxx' to your value

And also in the update tool have it update your post sql

Add a macro output and you'll also get an output of how many records you just deleted (since you deleted them via post sql after it counted). 

Put all this in a control container 1,

Put all your updates to the query in control container 2 and it can't build the query to execute the In-DB append until it's already removed the records.

So I'm updating 2 tables, I realized this morning one was working while the other wasn't.  It was my fault for copying the first batch macro thinking I updated the delete post sql for the second table when I didn't. 

Therefore, the original idea of using control containers to do the same thing as pre-sql does work!

[simonaubert_bd]

Hello @CharlesW First of all, congrats, that's nice. Would it be possible to share something ? Screenshots? Workflow? Batch macro?

Best regards,

Simon