Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to get the record column name from a dynamic query

Posted on 2014-08-20
12
Medium Priority
?
709 Views
Last Modified: 2014-09-08
Hi, i have this procedure where i send it some columns that i need  from some table
CREATE OR REPLACE FUNCTION DYNA_DATOS_COLUMNAS_F(PVH_COLS_NECESARIAS VARCHAR, PNH_fk_id_carga_cartera INTEGER) RETURNS integer AS $$
	DECLARE
	 VV_COLUMNAS_RETORNAR  varchar := '';

	  VV_QUERY_DINAMICO VARCHAR;
	  testRecord record;
	BEGIN
	VV_QUERY_DINAMICO:=' SELECT '|| PVH_COLS_DYNA ||' FROM as_detalle_carga WHERE fk_id_carga_cartera = ' || PNH_fk_id_carga_cartera || ' ;';
	--raise notice ' % ', VV_QUERY_DINAMICO;
	--printf("VV_QUERY_DINAMICO=%s\n",VV_QUERY_DINAMICO);
	--OPEN curs1 FOR EXECUTE VV_QUERY_DINAMICO;

	--FOR test IN EXECUTE VV_QUERY_DINAMICO LOOP
	--I NEED TO HANDLE THE DATA FOR EACH ROW
        --insert with the specific data would go here
	--	 	raise notice ' ELEMENT % ', testRecord ; 
--	END LOOP;
	return curs1;
		EXCEPTION WHEN OTHERS THEN
		raise notice '% %', SQLERRM, SQLSTATE;

			return -1;
			
	END;
	$$ LANGUAGE plpgsql;			

Open in new window


how can i get the testRecord.columnName  when the columns are dynamic?
0
Comment
Question by:Paul Mona
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40276399
I'm sure this will be different for the different databases that you are trying to use.

For Oracle, you couldn't use native dynamic SQL, you would have to use the DBMS_SQL package.  There are a few good examples in the documentation -> http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01113
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40276485
My MySQL you might use the AS clause to render a predictable column name.
0
 

Author Comment

by:Paul Mona
ID: 40276591
the problem is that i dont know what columns i might have to pass to the query (column3,column5,column1, etc),  so how can i call them in the record variable (record.column3, record.column5, record.column1, record.? ans so on )
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 40276727
If you know the order you can reference columns as a number for order by, never tried it for anything else.

Select * from tablename order by 1,3

You could insert the results into a temp table, then you would know the column names for that.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40276781
Are you using a high-level scripting language like PHP?  If so you can use variable names to create the query.

You can determine the names of the existing columns in the table with SHOW CREATE TABLE.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40276796
Can you confirm what database you are using for this?

When using DBMS_SQL in Oracle, you can call DESCRIBE_COLUMNS to get the names and data types of all the columns in the query.  This is shown in the examples that I linked to.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40276982
Obviously, if you  are already providing the names of the columns in "PVH_COLS_NECESARIAS",
Those are the columns returned by the query!
DUH!
Need the data type? use those name to query {ALL|DBA|USER}_TAB_COLUMNS
0
 

Author Comment

by:Paul Mona
ID: 40277625
1. Im using postgres.
2. im using $$ LANGUAGE plpgsql.
3. im dont need the data type.
2. I have the column names the main problem is that i dont know how to recall them on the record variable , i try record.1 , record.{1} , record.$1  when i try to iterate trough the dynamic query

FOR test IN EXECUTE VV_DYNAMIC_QUERY LOOP
		 	raise notice ' ELEMENT % ', test.1 ????; 
	END LOOP;

Open in new window


thanks for all the replies :)!!!
0
 
LVL 22

Expert Comment

by:earth man2
ID: 40278213
Most straightforward way would be to use returned cursor.

FETCH ALL FROM the_cursor_name;

All the column names and row data are contained within the cursor structure so should be extractable from a system table.
Ps to use cursors you need to be in a transaction so BEGIN ... COMMIT; needs to surround the sql.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 40278232
Need to know how you are going to use the data to get best solution.
0
 

Author Comment

by:Paul Mona
ID: 40278750
Need to know how you are going to use the data to get best solution.
I am gonna insert it on a diferent table this is why im using this method
1. upload file to a generic table
2. get data from certain columns (depending on configs)
3. insert the data on the table that it suppost to go to.

About the cursor, how can i recall the data on the cursor (dataFromcolumn:=cursor.column1) when i dont know the name of the column
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 1000 total points
ID: 40287312
you don't need to know the name of the columns only the number of columns and type.
eg just use text as most column types can be easily transmuted to text.

OPEN unbound_cursorvar FOR EXECUTE ' SELECT $1 FROM as_detalle_carga WHERE fk_id_carga_cartera = $2' USING PVH_COLS_DYNA, PNH_fk_id_carga_cartera;

FETCH unbound_cursorvar INTO rowvar;
or
FETCH unbound_cursorvar INTO foo, bar, baz;
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question