Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
AWMN
Level II

Get schema list from ODBC database

Hi all.

 

Im about to make a script, that fetches tables from an ODBC database. I have managed to use the query builder to do this, but I need to be able to select a schema from a drop down list, as not all users will have access to all schemas. Can anyone help me with an example on how to get the available schemas as a list or similar? It doesnt need to exclusively be the schemas the user has access to, but just all the schemas in the database. As an extra issue, it would be cool if I could get a list of tables that all have a specific view.

 

Thanks in advance!

 

Br. Anders

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
pmroz
Super User

Re: Get schema list from ODBC database

This is how I'd do it in Oracle:

dbc = create database connection("<your connection string here>");
sql = "SELECT a.username
  FROM all_users a
 ORDER BY a.USERNAME";
dt = execute sql(dbc, sql, invisible)
close database connection(dbc);
schema_list = dt:username << get values;
close(dt, nosave);

tbl_list = {};

nw = new window("Schema Browser",
	panel box("Schemas",
		schema_cb = combo box(schema_list,
			one_schema = schema_cb << get selected;
			sql = evalinsert(
"SELECT a.table_name
   FROM all_tables a
  WHERE a.owner = '^one_schema^'
  ORDER BY 1");
			dbc = create database connection("<your connection string here>");
			dt = execute sql(dbc, sql, invisible)
			close database connection(dbc);
			tbl_list = dt:table_name << get values;
			tbl_lb << set items(tbl_list);
			close(dt, nosave);
		),
	),
	panel box("Schema Tables",
		tbl_lb = list box(tbl_list, width(300), nlines(20))
	),
);

View solution in original post

2 REPLIES 2
Highlighted
pmroz
Super User

Re: Get schema list from ODBC database

This is how I'd do it in Oracle:

dbc = create database connection("<your connection string here>");
sql = "SELECT a.username
  FROM all_users a
 ORDER BY a.USERNAME";
dt = execute sql(dbc, sql, invisible)
close database connection(dbc);
schema_list = dt:username << get values;
close(dt, nosave);

tbl_list = {};

nw = new window("Schema Browser",
	panel box("Schemas",
		schema_cb = combo box(schema_list,
			one_schema = schema_cb << get selected;
			sql = evalinsert(
"SELECT a.table_name
   FROM all_tables a
  WHERE a.owner = '^one_schema^'
  ORDER BY 1");
			dbc = create database connection("<your connection string here>");
			dt = execute sql(dbc, sql, invisible)
			close database connection(dbc);
			tbl_list = dt:table_name << get values;
			tbl_lb << set items(tbl_list);
			close(dt, nosave);
		),
	),
	panel box("Schema Tables",
		tbl_lb = list box(tbl_list, width(300), nlines(20))
	),
);

View solution in original post

Highlighted
AWMN
Level II

Re: Get schema list from ODBC database

Thanks so much, this helped a lot!
Article Labels

    There are no labels assigned to this post.