Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Calling a stored procedure.

Avatar

Level 2

Hello all. I have two questions, SQL related.

what are some ways to use the SQL activity? is it possible to write my own SQL in that activity to query a list of recipients instead of using the query activity?

second, is there a way to call a stored procedure in adobe campaign classic?

any info on this will be much appreciated!

Thank you!

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi Edward,

Is it possible to write my own SQL in that activity to query a list of recipients instead of using the query activity? - Yes, You can use SQL activity  for this. As an example:

E.g

UPDATE NmsDelivery SET iRoutingDeliveryId = <%= vars.deliveryId %> WHERE iDeliveryId=<%= vars.iDeliveryId %>

Also, if you can use normal javascript activity and use functions like below to Executes an SQL script.

sqlExec (query [, value1, ... ])

sqlExecOnDataSource (query, dataSource [, value1, ... ])

sqlGetString (query [, value1, ... ])

E.g :

var firstName = sqlGetString('select sfirstName from nmsRecipient where sEmail =$(sz) order by tsCreated DESC LIMIT 1', email)

var sql4 = "INSERT INTO Reporting (sClientdesc,sClient) values ('" + client.@description.toString() + "','" + client.@clientCode.toString() +"')";

sqlExec(sql4);

REFER: https://docs.campaign.adobe.com/doc/AC/en/jsapi/p-1.html  for more SQL functions.

I hope this helps.

Thanks,

Kapil Kochatr

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

Hi Edward,

Is it possible to write my own SQL in that activity to query a list of recipients instead of using the query activity? - Yes, You can use SQL activity  for this. As an example:

E.g

UPDATE NmsDelivery SET iRoutingDeliveryId = <%= vars.deliveryId %> WHERE iDeliveryId=<%= vars.iDeliveryId %>

Also, if you can use normal javascript activity and use functions like below to Executes an SQL script.

sqlExec (query [, value1, ... ])

sqlExecOnDataSource (query, dataSource [, value1, ... ])

sqlGetString (query [, value1, ... ])

E.g :

var firstName = sqlGetString('select sfirstName from nmsRecipient where sEmail =$(sz) order by tsCreated DESC LIMIT 1', email)

var sql4 = "INSERT INTO Reporting (sClientdesc,sClient) values ('" + client.@description.toString() + "','" + client.@clientCode.toString() +"')";

sqlExec(sql4);

REFER: https://docs.campaign.adobe.com/doc/AC/en/jsapi/p-1.html  for more SQL functions.

I hope this helps.

Thanks,

Kapil Kochatr