Hi everyone!
I need to fill a "Select" Object on a Start Request Form with a SQL Query!
I know that I can make my query on "Script" but where I define my SQL Instance and the Database that I want use?
Regards,
Sérgio Castro
I need to fill a "Select" Object on a Start Request Form with a SQL Query!
I know that I can make my query on "Script" but where I define my SQL Instance and the Database that I want use?
Regards,
Sérgio Castro
Then in the script section, you can do something like this (I copied/pasted an example of what I'm using):
{
// ################################
// SQL Connection Data
// ################################
get_sql_driver_name : function() {
return 'com.microsoft.sqlserver.jdbc.SQLServerDriver';
},
get_sql_connection_url : function() {
var sServer = 'SERVERNAME;
var sDB = 'DATABASE_NAME';
return 'jdbc:sqlserver://' + sServer + ':1433' + ';DatabaseName=' + sDB;
},
get_sql_user_name : function() {
return 'USERNAME';
},
get_sql_password : function() {
return 'PASSWORD';
},
// ################################
// Function called when form is loaded
// ################################
form_onLoad : function() {
ca_fd.js.populate_select_sql('Form.SelectField', ca_fd.js.query(), 'SQLColumnToDisplay');
},
// ################################
// SQL Query
// ################################
query : function() {
This function should return an SQL Query sting
},
// ################################
// # HELPER FUNCTION
// # Function called to populate a select field with an SQL statement
// #
// # p_form_field = Select Field to populate
// # p_sql = SQL Query String
// # p_column = SQL column from result set to display
// # p_opt_array_field = ( Optional ) Array field to populate
// # Benefit of populating an array field is because ALL values from SQL query are retained and passed to process in case you need them later
// ################################
populate_select_sql : function(p_form_field, p_sql, p_column, p_opt_array_field) {
var callBack = new Object();
callBack.onSuccess = function( result ) {
// # DEBUGGING
//alert(result + "\n" + p_form_field + "\n" + p_column + "\n" + p_opt_array_field + "\n" + ca_pam_convertToJavaScriptObject(result).length );
//Print count of results for debugging
//alert( ca_pam_convertToJavaScriptObject(result).length );
//alert( "add to store");
ca_pam_addValuesInSelectStore( p_form_field, ca_pam_createSelectStoreFromSQLResult(result, p_column) );
if ( typeof p_opt_array_field != 'undefined') {
//alert( "add to table");
ca_pam_setTableData( p_opt_array_field, result );
}
}
callBack.onFailure = function( caught ) {
alert(caught);
}
ca_pam_getDataUsingSQLQuery( ca_fd.js.get_sql_driver_name(),
ca_fd.js.get_sql_connection_url(),
ca_fd.js.get_sql_user_name(),
ca_fd.js.get_sql_password(),
p_sql,
callBack
);
}
}