Thread: when inserting to table, text type parameter become NULL (after big assignment to this parameter)

when inserting to table, text type parameter become NULL (after big assignment to this parameter)

From
"Sofer, Yuval"
Date:
17 October 2007, 07:09:48
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">Hi, </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">I am trying to get server parameters (pg_settings) using a cursor and insert them to one column
table.</span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">A stored procedure parameter holds the long string.</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">The insert of this parameter succeeds. </span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The problem is that the string (the parameter value) is not inserted to the table.</span></font><p
class="MsoNormal"><fontface="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">When selecting, this column shows NULL value. </span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The procedure : </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">CREATE OR REPLACE FUNCTION dbu_show_server(monitor_table_id text)</span></font><p
class="MsoNormal"><fontcolor="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">RETURNS int AS</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">$BODY$</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">DECLARE</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">cur1 cursor for</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">select name,setting,source</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">from pg_settings;</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">p_name text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_value text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_source text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">p_out text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">BEGIN       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">open cur1;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">  </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">  loop</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       fetch cur1 into p_name,p_value,p_source; </span></font><p
class="MsoNormal"><fontcolor="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">       exit when not found;  </span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       p_out := p_out || p_name || '=' || p_value || ' source=' || p_source ||
',';</span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">          </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">   end loop;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">close cur1;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">raise notice 'p_out is %' ,p_out;</span></font><p class="MsoNormal"><font
color="blue"face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">insert into dbu_monitor_table(id,printable_output)
values(monitor_table_id,p_out); </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">return 0; </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">END;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">$BODY$</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">LANGUAGE 'plpgsql' VOLATILE;</span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">To activate the procedure: </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">select dbu_show_server('Sunday')</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The monitor table creation: </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">create table dbu_monitor_table(id text, printable_output text)</span></font><p
class="MsoNormal"><fontface="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">I noticed that it is a matter of the parameter length. </span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">When I defined p_out to be shorter, there was no problem.</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">For example instead of: </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">p_out := p_out || p_name || '=' || p_value || ' source=' || p_source || ',';
</span></font><pclass="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">I wrote (here it gets only p_name variable): </span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_out := p_out || p_name || '=' || p_value</span></font><font face="Arial"><span
style="font-family:Arial">;</span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">In this case column printable_output was filled as expected…</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">Please help. </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">Thanks </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Yuval Sofer<br /> BMC Software<br /> CTM&D Business Unit</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">DBA Team<br /> 972-52-4286-282<br /><a
href="mailto:yuval_sofer@bmc.com">yuval_sofer@bmc.com</a></span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font></div>