If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Informix > Dynamic queries SPL

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-27-09, 12:17
fmalmeida fmalmeida is offline
Registered User
 
Join Date: Jul 2009
Posts: 12
Dynamic queries SPL

Hi everybody...

I have an issue, i'm trying to resolve, but it doesn't seem i canīt get it.
So, i have a table in which i put a procname and variable names that are parameters (some_spl|lv_var1,lv_var2). Inside a spl, I execute a cycle and construct a query. Here's an example:

let lv_str = execute procedure pr_pap_gram_med_sp(lv_mill_cd,"PM4",lv_data_ini,lv _data_fim)

This is on trace file.
After that, I make:

execute IMMEDIATE trim(lv_str);

But then, I get this error:

lv_mill_cd:Variable() not declared

However this variable is declared.

What do you think about this?

Thanks
Reply With Quote
  #2 (permalink)  
Old 07-27-09, 14:31
Tyveleyn Tyveleyn is offline
Registered User
 
Join Date: Aug 2006
Location: The Netherlands
Posts: 248
Why don't you publish the stored procedure here? Please put the code between CODE-tags (the # button in the toolbar).

Hans
Reply With Quote
  #3 (permalink)  
Old 07-28-09, 05:26
fmalmeida fmalmeida is offline
Registered User
 
Join Date: Jul 2009
Posts: 12
Dynamic queries SPL

The code is the following:

Code:
	foreach cs_cod_var for
		select cod_var
		into lv_cod_var
		from pr_rel_text
		where mill_cd = av_mill_cd
		and cod_rel = av_cod_rel


		let lv_parameters, lv_procname = (select parameters, procname from get_proc
							where mill_cd = av_mill_cd
							and cod_rel = av_cod_rel
							and cod_var = lv_cod_var);

		let lv_str = 'execute procedure '||trim(lv_procname)||'(';
							
		if (lv_parameters is not null or lv_procname is not null) then

			let lv_count = length(lv_parameters) - length(replace(lv_parameters,'|',''))+ 1;

			FOR lv_index_var = 1 TO lv_count
				if (lv_index_var != lv_count) then
					let lv_str = trim(lv_str)||trim(gps_getkeyvalues_sp(lv_parameters,"|",lv_index_var)) ||',';
				else
					let lv_str = trim(lv_str)||trim(gps_getkeyvalues_sp(lv_parameters,"|",lv_index_var))||')';
				end if;
			END FOR
						
			execute IMMEDIATE trim(lv_str);
		end if;

		let lv_str = '';
		
	end foreach; -- cs_cod_var
I choose another approach in a way I can reach it, but I still want to know if it's possible to do it that way.

Thanks.
Reply With Quote
  #4 (permalink)  
Old 07-28-09, 15:25
Tyveleyn Tyveleyn is offline
Registered User
 
Join Date: Aug 2006
Location: The Netherlands
Posts: 248
Hi,
Quote:
But then, I get this error:
lv_mill_cd:Variable() not declared
However this variable is declared.
But when I look at your code example I notice that the lv_mill_cd must be the returnvalue of your gps_getkeyvalues_sp function. I'm not familiar with a Informix stored function that can return a pointer, other than to a BLOB (or maybe an opaque type, I've no experience with that). So I guess lv_mill_cd has to be a literal and should be treated like that which means it should be quoted because it's definitely not a numeric value.

Regards,
Hans
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On