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.

Populating a PDF form from database

Avatar

Former Community Member
I have asked this question here several times, but never get any replies. In fact my questions seem to be The Only ones that don't get a reply, which makes me think no one actually knows how to do this or my question is so remedial that no one is bothering. I'll try one more time.

/prays someone takes pity...



I have created a form that contains a subform with 4 text fields. The fields are read only and the form will be used only for printing. I have also connected to my Oracle database successfully and can populate the fields with data. My problem is I only get the first record. I have the subform set to repeat the data but am having no luck accomplishing this. I always just get the first record. Any suggestions would be more than appreciated as I have been fighting this for about a week now.



Thanks for any help.
6 Replies

Avatar

Former Community Member
Dear Robert,



Please first make sure that your form is dynamic, I mean, just create an Add button that clicking on it you will be able to create more rows. (there are many examples in the forum)



Once you got it you will have to create a function like this, please note that I have copied an pasted them without changing the objects, maybe you will need to change some of them, be careful



function populateDB()

{

var sDataConnectionName = "Oracle"; // name of the data connection to get the data from. Note the data connection will appear in the Data View.



// Search for sourceSet node which matchs the DataConnection name

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}



var oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

oDB.first();



// Search node with the class name "command"

var nDBIndex = 0;

while(oDB.nodes.item(nDBIndex).className != "command")

{

nDBIndex++;

}



// Backup the original settings before assigning BOF and EOF to stay

var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");

var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");



oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");



// Search for the record node with the matching Data Connection name

nIndex = 0;

while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)

{

nIndex++;

}



var oRecord = xfa.record.nodes.item(nIndex);



// Find the node representing the columns in the table

var oCol1Node = null;

var oCol2Node = null;

var oCol3Node = null;



for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)

{

if(oRecord.nodes.item(nColIndex).name == "C1") // column 1

{

//app.alert(oRecord.nodes.item(nColIndex))

oCol1Node = oRecord.nodes.item(nColIndex);

}

else

{

if(oRecord.nodes.item(nColIndex).name == "C2") // column 2

oCol2Node = oRecord.nodes.item(nColIndex);

else

{

if(oRecord.nodes.item(nColIndex).name == "C3") // column 3

//app.alert(oRecord.nodes.item(nColIndex))

oCol3Node = oRecord.nodes.item(nColIndex);

}

}

}



var secuencial;

var secuencial_ant;

var oNewRow;



secuencial_ant = 0;



while(!oDB.isEOF())

{

// create new row in Table1

if (oCol3Node.value != secuencial_ant)

oNewRow = xfa.form.Formulario1.Linea_detalle.Bordes.Campos.instanceManager.addInstance(false);



app.alert(oCol1Node.value);

app.alert(oCol2Node.value);

app.alert(oCol3Node.value);



rellenaCampo(oCol1Node.value ,oCol2Node.value);



secuencial_ant = oCol3Node.value;

oDB.next();

}



// Restore the original settings

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");



// Close connection

oDB.close();

}



function rellenaCampo(NombreCampo, ValorCampo)

{



var obj = xfa.form.resolveNode(NombreCampo);



if (obj != null)

obj.rawValue = ValorCampo;



}



Its read an Oracle DB table named T1 with three colums, name of the field, value and position into the list of fields (row 0, row 1, etc).



The second function just fill the field, the parameters are FieldName and FieldValue.



This codes are from the forum, I had your same problem and searching in it I founded it.



BR

Avatar

Former Community Member
Ruben,

Thanks very much for your reply. I think you have misunderstood my problem however. I am not trying to add rows to my database, I am simply trying to read and display multiple rows from my database. The user will not be entering any data. I simply want to display data records from my database and I am currently only able to get the first record to display when I view the PDF.

Avatar

Former Community Member
The oledb data connection returns a record set, so the default functionality will show the first record in the fields, and when you go next() the fields get updated with the values from the second record, etc.



If you need to show multiple subforms each showing each record at once, you would have to write a script that loops through the record set and for each record manually create a subform using the instanceManager, and set the rawValue of the fields in the subform to the value of the fields in the record.



Chris

Adobe Enterprise Developer Support

Avatar

Former Community Member
Hi you need to wrap your subform into another one and set it to flow content...

Avatar

Former Community Member
I'm also trying to do this - the PDF in no way knows what the key to pass to SQL is. The web page that calls this PDF does. I need to get ONE record from the DB and display it in the form. Again, I know the SQL, I can connect to the DB, but I need to be able to pass the key to the SQL to get the correct user.



Picture a user setup form. The user already exists in the DB. The web page knows what the userID in the DB (the key) is... it needs to pass that key to the PDF in some way, and have the PDF SQL use whatever was passed.



I'm currently playing around with the query string, but would think there should be a better way to do this.



Thanks,

-Greg