topic Re: How to save results from SQL query in data table? in Discussions

How to save results from SQL query in data table?

lukasz — Fri, 01 May 2020 20:31:12 GMT

Hello Everybody,

I have the following issue. I want to execute SQL query to get data from database and that is working well. After that I would to save data from query in data table and use that for further processing. Based on ideas from other posts I did a simple assignment to the new variable dt_TestData. I am providing as argument for Data Table the name taken from QueryName. However an error occurs when I want to save data in the new variable. The execution of query works well and the new table with name Experimental_Data opens automatically, but I would like to make the reference to that table. I am overlooking something and not able to notice that. I would appreciate for help how solve this issue. I am using JMP15. One more question, how to disable displaying the table after query execution? Best regards.

Data = New SQL Query(
	Connection(
		"ODBC:Description=Datenbank;DRIVER=SQL Server;SERVER=Server;UID=%_UID_%;PWD=%_PWD_%;APP=JMP;DATABASE=EngineeringSupport;"
	),
	QueryName( "Experimental_Data" ),
	Select(
		Column( "Param1", "t1"),
		Column( "Param2", "t1"),
		Column( "Param3", "t1"),
	),
	From( Table( "TestData", Schema( "dbo" ), Alias( "t1" ))),
) << Run;

dt_TestData = Data Table("Experimental_Data");
Open(dt_TestData);
//further actions with dt_TestData: slicing, joining with other tables, etc.

Re: How to save results from SQL query in data table?

gzmorgan0 — Fri, 01 May 2020 21:55:18 GMT

@lukasz ,

The last JSL statement is the issue: Open( dt_testData ). If the query ran successfully, the data table is already opened. If you want to make another copy of that table, use Subset. In teh example below dt2 is different table.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = dt << Subset( All Rows, Selected Columns(0), Output Table Name("Big2") );

Re: How to save results from SQL query in data table?

lukasz — Sat, 02 May 2020 09:35:55 GMT

gzmorgan0,

Thank you for reply! I should have explained that more accurately, the problem is I could not create "dt" variable which would make reference to the data resulted from the query. After the query is executed the new table is created an opened but where can I find (or create) the variable referencing the data? Or it is somehow created and I cannot see/use that? JMP is showing error regarding this line:

dt_TestData = Data Table("Experimental_Data");

"The data table cannot be found when accessing or evaluating Data Table, Data Table/*###*/("Experimental_Data")"

I think the variable dt_TestData cannot be created in such a way? I would appreciate for further help to solve the issue.

Re: How to save results from SQL query in data table?

Mark_Bailey — Sat, 02 May 2020 11:39:04 GMT

The variable Data stores the reference to the data table returned from the query. The function call Data Table( "Experimental Data' ) returns a reference to the open data table Experimental Data. You can send messages to the reference stored in Data.

Re: How to save results from SQL query in data table?

lukasz — Sat, 02 May 2020 15:41:02 GMT

markbailey, thank you for reply. I did not know how to get the reference by scripting but after additional searching I found the solution in this post. Thank you all for your time and best regards.

Re: How to save results from SQL query in data table?

Ressel — Fri, 16 Jun 2023 11:13:12 GMT

That's weird. When I run a query, I cannot send messages directly to the table from the query. I need to first make that table into a data table. See example below. Am I misunderstanding something?

Table = New SQL Query(
	Version( 130 ),
	Connection( "ODBC:DSN=yournamehere;" ),
	QueryName( "MyQuery" ),
	Select(
		Distinct,
		Column( "A", "t1" ),
	),
	From(
		Table(
			"some table",
			Schema( "some view" ),
			Alias( "t1" )
		),
		Table(
			"another table",
			Schema( "another view" ),
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "BatchId", "t1" ), Column( "BatchId", "t2" ) )
			)
		)
	)
) << Run;

// does not work
Close( Table, no save );

// does work
NewTable = Data Table( Table );
Close( NewTable, no save );

Ah, I think I have a faint idea now what might have caused this problem:

Table = New SQL Query(
	Version( 130 ),
	Connection( "ODBC:DSN=yournamehere;" ),
	QueryName( "MyQuery" ),
	Select(
		Distinct,
		Column( "A", "t1" ),
	),
	From(
		Table(
			"some table",
			Schema( "some view" ),
			Alias( "t1" )
		),
		Table(
			"another table",
			Schema( "another view" ),
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "BatchId", "t1" ), Column( "BatchId", "t2" ) )
			)
		)
	)
) << Run Foreground; // this fixed it

// does work
Close( Table, no save );