nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Could any expert check on my jsl script?

bernie426

I tried to wrote a simple jsl script to pull data from a SQL database with a functionality to select pulling data from a set time frame. However, after checking from couple times I still cannot find out the mistake. The script debugger saying -[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ''.

Could any one help me to check out where is wrong in my script?

//This script is wrote to pull data within a certain time frame

start_time_="02/23/2015 12:00:00 AM";

end_time_="02/24/2015 12:00:00 PM";

Open Database( "DSN=;Description=;UID=;PWD=;APP=JMP;WSID=;DATABASE=",

"SELECT * FROM dbo.WORKWEEK

WHERE ((dCreated >= '",start_time,"')

AND (dCreated <='",end_time,"'))

ORDER BY dCreated",

"Updated Table"

)

dCreated is the column from the database that used to provide time information. It is a numeric data type, continuous modeling type and the format is already set to m/d/y/ h:m:s

Many Thanks,

Accepted answers

Craige_Hales

"SELECT * FROM dbo.WORKWEEK

WHERE ((dCreated >= ' " || start_time_ || " ' )

AND (dCreated <= ' " || end_time_ || " ' ))

ORDER BY dCreated "

/*:

"SELECT * FROM dbo.WORKWEEK

WHERE ((dCreated >= ' 02/23/2015 12:00:00 AM ' )

AND (dCreated <= ' 02/24/2015 12:00:00 PM ' ))

ORDER BY dCreated "

use || for the string concatenation in JSL. I added the space between the apostrophe and the quotation mark just so I could see them better, you'll need to remove it most likely.

All comments

Craige_Hales

"SELECT * FROM dbo.WORKWEEK

WHERE ((dCreated >= ' " || start_time_ || " ' )

AND (dCreated <= ' " || end_time_ || " ' ))

ORDER BY dCreated "

/*:

"SELECT * FROM dbo.WORKWEEK

WHERE ((dCreated >= ' 02/23/2015 12:00:00 AM ' )

AND (dCreated <= ' 02/24/2015 12:00:00 PM ' ))

ORDER BY dCreated "

use || for the string concatenation in JSL. I added the space between the apostrophe and the quotation mark just so I could see them better, you'll need to remove it most likely.

bernie426

Thanks a lot Craige

pmroz

I use EVALINSERT for a cleaner look to the sql statement. To use it, enclose any variables you want evaluated in ^ characters.

//This script is wrote to pull data within a certain time frame

start_time_ = "02/23/2015 12:00:00 AM";

end_time_ = "02/24/2015 12:00:00 PM";

sql_statement = evalinsert(

"SELECT * FROM dbo.WORKWEEK

WHERE dCreated >= '^start_time_^'

AND dCreated <= '^end_time_^'

ORDER BY dCreated");

Open Database(

"DSN=;Description=;UID=;PWD=;APP=JMP;WSID=;DATABASE=",

sql_statement,

"Updated Table"

);

bernie426

Hi PMroz,

Does the EVALINSERT having the same functionality/meaning as "||?

Does it means ^ equal "||?

Thanks,

Craige_Hales

Lists and Expressions is part of the online help for JSL; there is also a built-in set of examples in Help->Scripting Index, although this particular example isn't very detailed. Help->Books->Scripting Guide is the pdf book on your computer and has a better example.

I'd say the two techniques are complementary, and in this particular case EvalInsert might make your JSL easier to understand. They accomplish the same thing, in very different ways.

pmroz

C Hales is correct - the two techniques are complementary. You can use evalinsert with variables surrounded by ^ characters to achieve the same effect as the string concatenation. But they're not the same thing.

I like using evalinsert because it gives a cleaner look to the code, where you need to insert variables into a string.