topic Re: Get schema list from ODBC database in Discussions

Get schema list from ODBC database

AWMN — Thu, 21 Nov 2019 13:52:17 GMT

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

Re: Get schema list from ODBC database

pmroz — Thu, 21 Nov 2019 14:47:37 GMT

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))
	),
);

Re: Get schema list from ODBC database

AWMN — Thu, 12 Dec 2019 07:07:21 GMT

Thanks so much, this helped a lot!