#11 New \i output mode to generate SQL

[Ed Avis]

This patch adds a new output style \i which generates
SQL code to insert into a table the rows selected - the
only thing missing from the SQL is the correct table
name, otherwise it is executable.

Line numbers are after applying the other patches I
sent, but I hope that patch(1) will apply this to
vanilla yasql.

@@ -1110,6 +1119,8 @@
$format = 'csv';
} elsif($terminator eq '\S') {
$format = 'csv_no_header';
+ } elsif($terminator eq '\i') {
+ $format = 'sql';
}
$num_rows ||= 0;
@@ -3092,6 +3141,55 @@

return($count, 'selected');
}
+ } elsif($format eq 'sql') {
+ # Produce SQL insert statements.
+ print "\r" if $op eq '<';
+ print FOUT "\n";
+
+ my $cols = lc join(', ', @{$sth->{NAME}});
+ my @types = map { scalar
$dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
+
+ my $count = 0;
+ while(my $res = $sth->fetch()) {
+ $count++;
+ die if @$res != @types;
+ print FOUT "insert into TABLE ($cols) values (";
+ foreach (0 .. $#$res) {
+ my $t = $types[$_];
+ my $v = $res->[$_];
+ if (not defined $v) {
+ print FOUT 'null';
+ } else {
+ if ($t eq 'DOUBLE' or $t eq 'NUMBER') {
+ die "bad number: $v" if $v !~ /\d/;
+ print FOUT $v;
+ } elsif ($t eq 'VARCHAR2' or $t eq 'CHAR' or
$t eq 'CLOB') {
+ $v =~ s/['']/''/g;
+ print FOUT "'$v'";
+ } elsif ($t eq 'DATE') {
+ print FOUT "'$v'";
+ } else {
+ warn "don't know how to handle SQL type $t"
+ unless $warned_unknown_type{$t}++;
+ print FOUT "(unknown type $t: $v)";
+ }
+ }
+ print FOUT ', ' unless $_ eq $#$res;
+ }
+ print FOUT ");\n";
+ last if $num_rows && $count >= $num_rows;
+ return(0) if $sigintcaught; #pseudo sig handle
+ }
+
+ return(0) if $sigintcaught; #pseudo sig handle
+
+ # If we didn't get any rows back, then the query
was probably an insert or
+ # update, so we call format_affected
+ if($count <= 0 && !$isselect) {
+ return($sth->rows(), 'affected');
+ }
+
+ return($count, 'selected');
} else {
die("Invalid format: $format");
}

[Ed Avis]

Logged In: YES
user_id=10769

As well as DOUBLE and NUMBER, DECIMAL should be printed
as-is (and relax the /\d/ regexp).

[Kozman Bálint]

Logged In: YES
user_id=1209168

Patch applied. Will be included in 1.83.
Please be kind to rather upload the patch the 'normal' way
as pasting it directly to the post makes it rather difficult
to transform it to a real patch as it loses special
characters like tab, etc.