Solved

FSM Client Scripts: Get integer value from db

  • 26 January 2022
  • 4 replies
  • 287 views

Userlevel 3
Badge +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!

icon

Best answer by csanders 21 June 2022, 20:55

View original

This topic has been closed for comments

4 replies

Userlevel 3
Badge +8

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.

Userlevel 7
Badge +24

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

Userlevel 3
Badge +8

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.

Userlevel 3
Badge +8

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.