Home » Developer & Programmer » Forms » Problem with Exec_sql for an insert stmt (Forms 6i)
Problem with Exec_sql for an insert stmt [message #387341] Wed, 18 February 2009 21:59 Go to next message
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
Re: Problem with Exec_sql for an insert stmt [message #387360 is a reply to message #387341] Wed, 18 February 2009 23:57 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please format your code.

Search this forum for 'EXEC_SQL.PARSE'. There are worked examples.

David
Previous Topic: Convert word document into pdf
Next Topic: FRM-40564:RECORD HAS BEEN UPDATED BY ANOTHER USER-REQUERY TO SEE CHANGE
Goto Forum:
  


Current Time: Sat Dec 03 22:05:44 CST 2016

Total time taken to generate the page: 0.08136 seconds