Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

SQL Data Connection - Using Select Statements

Avatar

Level 1

Hey guys,

     So I'm currently working on this dynamic forms project for my company to help with form flow.  I've begun to get quite comfortable with designer and how to use it.  I was able to create a 'forms assistant' if you may the dynamically changes to assist the user in filling out company policy forms.  The most recent addition that I have been trying to encorporate is using SQL or data connections to improve the functionality.  I'm having some trouble getting what I need accomplished using this type of setup.  Basically what I need to do is get the user to fill in a company ID that would then use the value from that field and generate a select with a where clause to populate the rest (or most) of the form for them.  I have successfully been able to setup a connection and have fields from an SQL database, but I can't manipulate the data the way I need.  This is the basic code or flow that I need to

work.

xfa.sourceSet.HEAT.#command.query.setAttribute("text", "commandType");
xfa.sourceSet.HEAT.#command.query.select = "SELECT * FROM Profile WHERE CustID = " + this.rawvalue + ";";
xfa.sourceSet.HEAT.open();
xfa.sourceSet.HEAT.first();

I've been sticking to Java script, and would like to keep it that way.  I have been using source material from Stefan Cameron on forms.  So the last two commands work fine, and I am able to navigate through records just fine.  The first 2 command aren't the right syntax for Java, which I would like to know how to use.  But I also tried it as formCalc following the "Selecting Specific Database Records" from Stefan's tutorial.  I get the security error that is spoken of at the end of the tutorial.  I tried to follow his process for cloning, but his case is a little different from mine.  I don't know how it should look or where to create the clone.  All I need is a working select statement that I can change the where clause for.

1 Reply

Avatar

Level 1

UPDATE

Think I fixed this one myself.  One of my problems was syntax.  The other was properly creating the clone.  By using

var oDB = xfa.sourceSet.DataConnection.clone(1);

oDB.open();

in my initialization, I properly created a data instance.

In my actual select portion I think it was just syntax.  I used:

oDB.resolveNode("#command").query.commandType = "text";
oDB.resolveNode("#command").query.select.value = "SELECT * FROM Profile WHERE CustID = '03532'";
oDB.open();
oDB.first();

Pretty sure the resolveNode is what fixed my problem with actually running my command.  Works great now.  Now just to dynamically change the value in the where clause using the user's input ... no problem.