Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.

How can I get a count of records in a DB?

Avatar

Former Community Member
If I want to get a count of the number of records in a table how do I do it?
17 Replies

Avatar

Former Community Member
You can do it by writing a JavaScript script inspired from the custom Data List Box object provided in the Library.



This object provides a canned script to populate a list box from a table in a database (for which you've created a Data Connection in Adobe LiveCycle Designer using the Data View palette).



To have a look, simply drag a Data List Box from the Library palette's Custom tab onto your form and look at its Initialize script.



This canned script asks you to fill-in three variables, of which one is named
sDataConnectionName. The other two variables ask for the text and value table column names which you want to map to the list box's text and value values for the items the script will add to it.



You should be able to modify this script to obtain a record count simply by removing the excess stuff about the list box items and adding an item counter at the point where the script is looping through the records in the table and populating the list box with items.



The script could look like this (note: replace "<value>" with your data connection name):



var sDataConnectionName = "<value>"; // example - var sDataConnectionName = "MyDataConnection";



// 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");



var itemCount = 0;



while(!oDB.isEOF())

{

itemCount++;

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();



this.rawValue = itemCount;


This example assumes the script is placed on a numeric field's Initialize event as a JavaScript script.



Stefan

Adobe Systems

Avatar

Former Community Member
I'm still trying to learn about how some of these events work but wouldn't it be Ok to put this in a calculate event so as records are added or deleted to the data base a running or live total would be seen?

Avatar

Former Community Member
Sure but keep in mind that adding/removing records to/from the database outside of the form (by other means such as using Microsoft Access or the MySQL prompt) won't trigger calculations.



If you're adding/removing records using dynamic subforms and data binding in your form, then calculations will run when records are added/removed and your running total, in the Calculate event, should be accurate.



Stefan

Adobe Systems

Avatar

Former Community Member
Thanks. Now I just need to figure out how to modify the original script. If I run into any difficulties I will post again. Most likely that will happen. Thanks for the info and response.

Avatar

Former Community Member
You're welcome. I'll keep an eye on this thread in case you have any questions about the script.



Stefan

Adobe Systems

Avatar

Former Community Member
Could you explain what the class name "command" is and where it comes from?



I have tried looking this up in some of the refs that I have but nothing makes sense to me.



So far the number that shows up in the numeric field is "0" and I know there are more records in the table.

Avatar

Former Community Member
The line "oDB.nodes.item(nDBIndex).className != 'command'" is looking at the nodes (elements) inside the <source> node which represents the Data Connection you specified in sDataConnectionName, inside the <sourceSet> node which contains all data connections.



If you go to the XML Source tab, once you've created the data connection, and scroll to about 1/3 way from the bottom, you'll see a set of nodes that look like this:



<sourceSet xmlns="http://www.xfa.org/schema/xfa-source-set/1.0/">

<source name="DataConnection" db="ado">

<connect delayedOpen="0" timeout="15">[...]</connect>

<command timeout="30">

<query commandType="table">

<select>onecolumn</select>

<recordSet cursorType="static" cursorLocation="client" lockType="optimistic" bofAction="moveFirst" eofAction="moveLast"/>

<map from="title" bind="#bind0">

<?templateDesigner DataType 129?>

</map>

</query>

</command>

<bind id="bind0" ref="$record.DataConnection.title"/>

</source>

</sourceSet>


If you follow along with the code, nIndex is the index of the <source> node which represents the data connection (in this case, "DataConnection", the only one). Then, nDBIndex is the index of the <command> element inside the data connection's <source> node. Once the command element is obtained, it's BOF (Beginning of File) and EOF (End of File) actions are slightly modified for the purposes of the script (they're restored after the script is done counting the items in the table).



Temporarily modifying the BOF and EOF actions as the script does helps with performance when we just need to iterate through the items in the table from beginning to end, once only.



Note that to create a data connection to a table in the Data View palette, you need a DSN in your ODBC connections (found in Control Panel/Administrative Tools) through which the data connection can connect to the table in the database.



Stefan

Adobe Systems

Avatar

Former Community Member
Thank you for this explanation.



I got it to work. I had forgotten a parenthesis character. Thanks for the help on this.



Steven

Avatar

Former Community Member
I have been told that it is good to close the connection at the end of an operation. I can get a count of the records on initialization but what if I want to get a running count so the number changes as I add or delete records? Seems like the connection needs to remain open doesn't it?

Avatar

Former Community Member
You wouldn't want to keep it open, as database connections consume system resources - it's usually best to connect, do what you need to do, and disconnect. This makes it easier for the application to scale - a thousand users won't need a thousand mostly-idle database connections.



--

SteveX

Adobe Systems

Avatar

Former Community Member
Generally, it's good practice to close the connection once you're done with it unless you have a reason to keep going back to it, in which case it probably becomes a performance issue. If you need to keep accessing the same connection repeatedly while your form is running (in Acrobat), I think you would be best to leave it open after initialization.<br /><br />You might consider placing the code which counts the records into a function inside a Script Object. This way, you can just call the Script Object method and retrieve the record count whenever you need it.<br /><br />Make sure you define the <b>oDB</b> variable outside the function but inside the Script Object:<br /><pre>var oDB = null;<br /><br />function GetRecordCount()<br />{<br /> if (oDB == null)<br /> {<br /> var sDataConnectionName = "<value>"; // example - var sDataConnectionName = "MyDataConnection"; <br /><br /> // Search for sourceSet node which matchs the DataConnection name <br /> var nIndex = 0; <br /> while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName) <br /> { <br /> nIndex++; <br /> } <br /><br /> var oDB = xfa.sourceSet.nodes.item(nIndex); <br /> oDB.open();<br /> }<br /><br /> oDB.first(); <br /><br /> // Search node with the class name "command" <br /> var nDBIndex = 0; <br /> while(oDB.nodes.item(nDBIndex).className != "command") <br /> { <br /> nDBIndex++; <br /> } <br /><br /> // Backup the original settings before assigning BOF and EOF to stay <br /> var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction"); <br /> var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction"); <br /><br /> oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction"); <br /> oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction"); <br /><br /> var itemCount = 0; <br /><br /> while(!oDB.isEOF()) <br /> { <br /> itemCount++; <br /> oDB.next(); <br /> } <br /><br /> // Restore the original settings <br /> oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction"); <br /> oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction"); <br /><br /> return itemCount;<br />}</pre><br />You can create a script object by right-clicking on the top-level form node ("form1" by default). Say you name your script object "Utils", you can then call the function inside of it from any event script like this:<br /><pre>this.rawValue = Utils.GetRecordCount();</pre><br />Stefan<br />Adobe Systems

Avatar

Former Community Member
I just noticed SteveX's post on this thread. It's up to you to decide what you want to do. He does have a good point about the system resources, though.



You can modify my example to open and close the connection every time the script object function is called simply by taking the entire script from your Initialize event and putting it into the function, removing the "var oDB = null;" defined outside the function.



Stefan

Adobe Systems

Avatar

Former Community Member
This is all great info. I just need to figure out how to manipulate and work with code examples.



And it sounds like script objects are the equivalent of Document JavaScripts that I'm used to working with in Acrobat right?

Avatar

Former Community Member
They are similar to Document JavaScripts in that you can put variables and methods in them and access them from anywhere on your form.



Chris

Adobe Enterprise Developer Support

Avatar

Former Community Member
Speaking of the system resources,



the way I read the code, to me it looks like you are looping through all returned records to work out how many there are.

This means you are actually retrieving all these records just to count them.



Would it not be faster and less resource hungry to execute a query that leaves it to the dbms to count the records?



ie, in your QUERY string, instead of saying

SELECT fldnamelist FROM tablename WHERE selection_criteria_clause



Just use

SELECT COUNT(*) FROM tablename WHERE selection_criteria_clause



and it returns only one record, with single field that contains the number to records matching your criteria in the where clause. (if you truly want to count all records in the table, you can leave the where clause out altogether.)



Just a thought,

Sanna

Avatar

Former Community Member
Sounds interesting. How does one get this into some JavaScript. I tried several things with my limited knowledge of Designer JavaScript and SQL but nothing works.

Avatar

Former Community Member
Since you already have a data connection which connects to the table which you're iterating through, you can specify the query right in the data connection settings. Just right-click on the data connection in the Data View palette,
Connection Properties..., click on Next and then select the
SQL query radio button and enter what Sanna suggested (modified for your table, of course):



SELECT COUNT(*) as NumberOfRows FROM tablename WHERE selection_criteria_clause


The result will be a data connection with a single node.



Then, modify your script which currently sums the number of rows to simply retrieve the value of the "NumberOfRows" in the first (and only) row in the table (generated in the data connection by the SQL query you specified).



Stefan

Adobe Systems