Re: string handling in database-2.0.1 commands

[Olaf Till]

On Thu, Feb 21, 2013 at 04:29:08PM +0000, richard wrote:
> Hi all, 
> 
> I am trying to both read data from a database and to write the 
> data back to the database. Reading the data puts the data into 
> a cell structure "S.data" 
> 
> I may be trying to write the data back in an inefficient way, 
> but in any case I seem to have a problem with string handling:
> 
> octave:59> j=1
> j =  1
> octave:60> a= num2str(cell2mat(S.data(j,1)));
> octave:61> b= num2str(cell2mat(S.data(j,2)));
> octave:62>  c= num2str(cell2mat(S.data(j,3)));
> octave:63> d= num2str(cell2mat(S.data(j,4)));
> octave:64>  e= num2str(cell2mat(S.data(j,5)));
> octave:65> Y = ["(", a,", ", b,", " c, ", ", d, ", ", e,");"];
> octave:66>  X = "insert into rome001 (year,l_pay, legion_pay, legions, 
> Army_pay) values";
> octave:67>  Z = strcat("\"",X, Y, "\"");

You need just a string. You needn't (mustn't) include \" into the
string. And better use cstrcat instead of strcat. So you could do:

Z = cstrcat (X, Y);

> octave:68> Z
> Z = "insert into rome001 (year,l_pay, legion_pay, legions, Army_pay) 
> values(-27, 150, 559, 28, 15652);"
> octave:69>  pq_exec_params (conn, Z);
> NOTICE:  identifier "insert into rome001 (year,l_pay, legion_pay, legions, 
> Army_pay) values(-27, 150, 559, 28, 15652);" will be truncated to "insert 
> into rome001 (year,l_pay, legion_pay, legions, Army_pay)"
> error: pq_exec_params: fatal error: ERROR:  syntax error at or near ""insert 
> into rome001 (year,l_pay, legion_pay, legions, Army_pay) values(-27, 150, 
> 559, 28, 15652);""
> LINE 1: "insert into rome001 (year,l_pay, legion_pay, legions, Army_...
>         ^
> 
> I do not see why "identifier  .... will be truncated " happened.

As said above, the \" in the string are too much. They make the whole
string to be interpreted as an identifier by postgresql
 
> 
> Is there a preferred way to write data to a database table? 

While constructing strings from Octave valules (with cstrcat or
better, with sprintf) will work, I'd say the 'preferred' way is to use
placeholders ($..), as explained in the help-text of pq_exec_params.

Example:

data = S.data;

pq_exec_params (conn, "insert into rome001 (year, l_pay) values ($1, $2);", 
{data(1, 1), data(1, 2)});

> Why did this method fail? 

See above.

> 
> TIA
> 
> Richard 
>

Olaf

-- 
public key id EAFE0591, e.g. on x-hkp://pool.sks-keyservers.net

signature.asc
Description: Digital signature