Skip to main content
Solved

FSM Client Scripts: Get integer value from db

  • January 26, 2022
  • 4 replies
  • 345 views

Forum|alt.badge.img+8

I’m trying to write a client script to be used in the smart client of FSM 6.5.

In my Client Script SQL I’m returning two columns.

  • exp_date: Date saved in the DB
  • days_left: Using DATEDIFF(day, GETDATE(), exp_date) AS days_left

When I access these values from my script, days_left is set to type System.String and I need it to be an integer.

I have tried changing the SQL to:
CAST(DATEDIFF(day, GETDATE(), exp_date) AS int) AS days_left

but in my script the variable is still a string so I’m unable compare the value with an integer.

Example: if (days_left <= 0) { doesn’t work. 
The error message will say: The ‘<’ operation cannot be performed between the operands 853 and 0

Here is my script so far
 

// Get information from the screen.
var place_id = getControlValue("request", "place_id_to_bill");
var card_id = getControlValue("request", "user_def24");
var today = new Date();

// Make sure we have both the Place_ID and Card_ID.
if (isNullOrEmptyString(place_id) == false && isNullOrEmptyString(card_id) == false) {
	// Get CC Exp Date
	var dbData = getDBValues("GOS_CREDIT_CARD_EXP_DATE", place_id, card_id);
	if (isNullOrEmptyString(dbData) == false) {
		var exp_date = dbData[0]["exp_date"];
		var days_left = dbData[0]["days_left"];

		// Credit card is expired.
		if (days_left < 1) {
			setControlValue("custom", "cc_exp","EXPIRED");
			alert("The selected credit card is expired");

		// Credit card expires soon.	
		} else if (days_left <= 14) {
			setControlValue("custom", "cc_exp", exp_date);
			alert("The selected credit card is expiring soon.");

		// Credit card is good.	
		} else {
			setControlValue("custom", "cc_exp", exp_date);
		}
	} else {
		alert("Error checking credit card expiration date.");
	}
}

I have tried var days_left = 0; before loading the value from the db hoping it would be forced to be an integer, but it still ends up as a string once the value from the db is loaded.

Any ideas?

Thanks!

Best answer by csanders

Just in case anyone else was looking for help with this. I found this in the documentation today: 

Retrieving database results using getDBValues() performs dynamic data typing for Mobile clients but returns strings in the FSM Smart Client. Adjust testing procedures as required to accommodate this difference.

This really limits what you can do with scripts in the smart client.  The only work around that I’ve found is to do any and all math in your SQL before the results are sent back to your script.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

4 replies

Forum|alt.badge.img+8
  • Author
  • Sidekick (Customer)
  • 49 replies
  • January 26, 2022

I ended up moving all of the math and checks to the SQL to bypass the situation.  I’d really like to know if there is another solution as this is not the first time I’ve ran into this problem.

If there is no other solution it would seem like a major flaw/limitation with client scripts.


Lee Pinchbeck
Ultimate Hero
Forum|alt.badge.img+24
  • Ultimate Hero
  • 1219 replies
  • January 27, 2022

Hi @csanders,

Usually you would create a client script SQL entry in FSM for he relevant SQL section and then call this in the client script.

With version 6 most of the SQL manipulation was removed from client scripting to prevent possible SQL injection.

Kind regards,

Lee Pinchbeck


Forum|alt.badge.img+8
  • Author
  • Sidekick (Customer)
  • 49 replies
  • January 27, 2022
Lee Pinchbeck wrote:

Hi @csanders,

Usually you would create a client script SQL entry in FSM for he relevant SQL section and then call this in the client script.

With version 6 most of the SQL manipulation was removed from client scripting to prevent possible SQL injection.

Kind regards,

Lee Pinchbeck

 

Thanks for the reply. I do use the client script sql.

The problem is that numbers are coming from the db into my script as strings.
So I can’t use them in my script as numbers,

The client script can’t do things like check if “850” is greater than 1 because as far as it’s concerned “850” is not a number, it’s just text. It’s like asking if “apple” is greater that 1.


Forum|alt.badge.img+8
  • Author
  • Sidekick (Customer)
  • 49 replies
  • Answer
  • June 21, 2022

Just in case anyone else was looking for help with this. I found this in the documentation today: 

Retrieving database results using getDBValues() performs dynamic data typing for Mobile clients but returns strings in the FSM Smart Client. Adjust testing procedures as required to accommodate this difference.

This really limits what you can do with scripts in the smart client.  The only work around that I’ve found is to do any and all math in your SQL before the results are sent back to your script.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings