Subscribe Bookmark RSS Feed

Re: Could any expert check on my jsl script?

bernie426

Community Trekker

Joined:

Feb 9, 2015

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales

Staff

Joined:

Mar 21, 2013

Solution

  "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.

Craige
6 REPLIES
Craige_Hales

Staff

Joined:

Mar 21, 2013

Solution

  "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.

Craige
bernie426

Community Trekker

Joined:

Feb 9, 2015

Thanks a lot Craige

pmroz

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Feb 9, 2015

Hi PMroz,

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

Does it means ^ equal "||?

Thanks,

Highlighted
Craige_Hales

Staff

Joined:

Mar 21, 2013

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.

Craige
pmroz

Super User

Joined:

Jun 23, 2011

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.