Which way to go query database?

[#1]

Dear all,

I use database queries a lot, in combination with custom sql. I know, there are 3 methods like shown below.

I like the "new sql query" most, because it's most powerful, and more safe (see password ...).

Unfortunately it's behaviour regarding Hide ODBC connection strings has changed in JMP17, but it will be corrected:

JMP17 behaviour on Preference "Hide ODBC Connection Strings" set: connection str... - JMP User Commu...

I was told by the developers in the Discovery Summit Europe in Sitges, that the method new sql queries is the recommended one, more stable, and the both others are outdated. But I also heard that there is a speed issue. Here I have never read about this recommendation, nor in the documentation.

I'd like to discuss:

What method do you use, and why?

And what experiences have you made?

Thanks and best regards,

  Georg

Names Default To Here( 1 );

// 3 different ways for database access

// constants

// this works on all Oracle DB's
sql_str = "select * from Dual"; 

// needs to be adapted to your database
cnx_str ="DSN=MYDSN_NAME;UID=USERNAME;PWD=PASSWORD";


// new sql query needs connection string starting with "ODBC:"
// there is no good documentation in the manual (scripting guide and JMP Help)
qry_obj = New SQL Query( query name("New SQL Query"), connection( "ODBC:"||cnx_str ), custom sql( sql_str ) );
dt_sqlquery = qry_obj << run foreground();

// open database is a oneliner
// with "Hide ODBC connection string" not set, it saves the plain password into the table scripts
dt_open = open database(cnx_str, sql_str, "Open Database");

// expecute sql needs the database opened before sending statements
// with "Hide ODBC connection string" not set, it saves the plain password into the table scripts
dbc=create database connection(cnx_str);
dt_execute = execute sql(dbc, sql_str, "Execute SQL");
close database connection(dbc);

// EOS

[#2]

I prefer using the third option. Reason being is the most clear one (in my opinion) and lets me control when I close my database connection (it is also the first one I did learn). Only case where I use New SQL Query is when I want to run query in background. I could consider moving to New SQL Query if there is some reason for it (just being "recommended" is definitely not enough for me to move to it).

[#3]

Thanks @jthi for your answer,
I'd love to hear a statement from the developers of JMP here, because I could not find anything within the documentation,

@martindemel or @briancorcoran ?

Perhaps there could be added a few words to the documentation on the different ways to database?

[#4]

Some additional documentation and proper working examples would definitely be useful (maybe use .sqlite database and add it to $SAMPLE_IMPORT_DATA?).

This does seem to have some documentation but it isn't properly being linked in Scripting Index help button https://www.jmp.com/support/help/en/17.0/#page/jmp/sql-messages.shtml# and then we have this https://www.jmp.com/support/help/en/17.0/#page/jmp/sql-functions.shtml#ww5089351 . 

If this really is the preferred method (has the performances between different methods been tested?) I most likely could start changing my scripts to use << Run Foreground solution.

[#5]

I have done some testing with New SQL Query and I feel like it does have some nice features which are implemented in such a way that I wouldn't really use them, such as functions used with OnRunComplete/OnRunCanceled/OnError must be in global namespace. Maybe I could write some very general functions which could be used with these but still... it is global namespace.

It also seems to be slower than Open Database or Execute SQL (especially before the query starts) which could be an issue if you were to run multiple small queries. Maybe I would have to modify my dns string somehow when using New SQL Query (outside adding ODBC:)?

Most likely I will stick to "older" methods of querying unless I need to be able to run my queries in the background.

[#6]

Hi jthi, 

in my case speed is not the most important, I like "new sql query" due to the functionality, and because it is more safe regarding connectionstring and password.

If preference "Hide ODBC connections strings" is not set, both other methods will put the passwort literally into the source script of the new table. "new sql query" keeps the connection string but changes the password to "%PWD%".

If preference "Hide ODBC connection strings" is set, it would remove the scripts {"source", "modify query", "update from database"}, and I don't like this, because it's cool that jmp connects the table with the way the data has been generated and the possibility to update data, an option that I use very often.

In a company landscape with different scripters it is difficult to deal with different methods (and therefore different preference settings depending on methods). It makes everything complicated. That's why I love to have a clear favourite.

[#7 · Accepted solution]

Hi Georg, Jarmo,

New SQL Query is the method that will get the enhancements in the future, while create database connection and execute sql will largely be in maintenance (bug fix) mode. New JMP 18+ initiatives we have around creating more access methods to databases and persisting that connection information as JSL based data connectors will use New SQL Query.  I'll let Documentation know that the information could be more clear.

I hope this helps,

Brian Corcoran

JMP Development