OraFAQ Forum: Forms » Problem with Exec

Home » Developer & Programmer » Forms » Problem with Exec_sql for an insert stmt (Forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with Exec_sql for an insert stmt [message #387341]

Wed, 18 February 2009 21:59

formsdev
Messages: 6
Registered: June 2005

Junior Member

Hi ,

I am trying to do an insert of the form values into a table using exec_Sql.

I am getting bind variable error at runtime. How do I get this to work?
My code is as below.

I have a form which has a block called ControlBlk. It has fields login, password, connect_string, property, active.
I want to insert these fields into a table.

Begin 
	v_string := :controlBlk.login||'/'||:controlBlk.password||'@'||:controlBlk.connect_string;
	connection_id := EXEC_SQL.OPEN_CONNECTION(v_string); 
	EXCEPTION WHEN EXEC_SQL.PACKAGE_ERROR THEN
	 message(' connection open failed ');
	END;
	IsConnected := EXEC_SQL.IS_CONNECTED(Connection_id);
	--message(connection_id); 
	
	If IsConnected = FALSE then 
		message('Could not make corresponding changes in schema-'||:controlBlk.login);
	Else 
		  
		 	Begin 
		 	cursor_num := EXEC_SQL.OPEN_CURSOR(Connection_id); 
		 	EXEC_SQL.PARSE(Connection_id,cursor_num,' INSERT INTO stats_table  (property,id,property_name,ip_address,login,password,
                       connect_string,port_number,keys,active) values (:controlBlk.property,:controlBlk.id,:controlBlk.property_name,         
                         :controlBlk.ip_address,:controlBlk.login,:controlBlk.password,
		 	:controlBlk.connect_string,:controlBlk.port_number,:controlBlk.keys,:controlBlk.active)');
		 	
		 	ignore := EXEC_SQL.EXECUTE(Connection_id,cursor_num);
		 	
		 	EXEC_SQL.PARSE(Connection_id,cursor_num, ' commit');
		 	
		 	ignore := EXEC_SQL.EXECUTE(Connection_id,cursor_num);
		 	
		 	EXEC_SQL.CLOSE_CURSOR(connection_id,cursor_num); 
		 	EXEC_SQL.Close_Connection(Connection_id); 
		 	exception when others then 
		 		message(sqlerrm);
		 		message(EXEC_SQL.LAST_ERROR_MESG(Connection_id));
		 	
		 		
		 	end;

It works fine until the connection. But the insert does not work.
Thanks

Report message to a moderator