> My client wants all fields in all user tables exported to CSV
> files. Can anyone point me to the best way to accomplish this from
> within my application? If I have to write my own, can you tell me
> if the RDB$SYSTEM_FLAG field of the RDB$RELATIONS table being set to
> 0 indicates a user table? If not, how do I detinquish user tables
> from system tables? I'd like to write a generic process so changes
> in the database don't screw up the export.

Here's a little script to do a CSV dump of all data, with configurable
quoting, date formats, and delimiter support.
It's in PHP, but it'll give you ideas if you need to do it in another
language.

Nige.





#!/usr/local/bin/php -e
<?php
// Connect to the source database
$src_db =
ibase_connect("203.26.213.98:/u1/db/asset.fdb","sysdba","masterkey") or $err
= 1;
if($err == 1){
echo "Error:($err). Source Database connection failed\n";
exit;
}

/* ------------------------------------------------
Uncomment these if you want to transfer immediately
------------------------------------------------ */
/*
$tfer = 1;
// Connect to the destination database
$dest_db =
ibase_connect("203.26.213.98:/u1/db/test.gdb","sysdba","masterkey") or $err
= 2;
if($err == 2){
echo "Error:($err). Destination Database connection failed\n";
exit;
}
*/


// Set the following variable to 1 to produce insert statements
$ins = 0;

// Set the field delimiter here
$delim = "'";

// Set the field seperator here
$sep = ",";

// Set the single-quote escaper here
$esc = "'";

// Set the date formats here (// %H:%M:%S // %Y-%m-%d // %Y-%m-%d %H:%M:%S)
ibase_timefmt("%H:%M:%S",IBASE_TIME);
ibase_timefmt("%Y-%m-%d",IBASE_DATE);
ibase_timefmt("%Y-%m-%d %H:%M:%S",IBASE_TIMESTAMP);

// Set the begin comment here ($bcomm = "/*"; $bcomm = "--";)
$bcomm = "/*";

// Set the end comment here ($ecomm = "*/"; $ecomm = "";)
$ecomm = "*/";




/* ---------------------------------------
Configuration complete
If you're changing things below this line,
you probably shouldn't be...
--------------------------------------- */


$tsql = "SELECT distinct(a.rdb\$relation_name) as str_tablename from
rdb\$relation_fields a where a.rdb\$relation_name NOT CONTAINING 'RDB\$'";

$trec = ibase_query($src_db,$tsql) or die("Bad Table Query");
while($tobj = ibase_fetch_row($trec)){
//We have a table name. Let's pull the data
if($bcomm){echo "$bcomm --- ".trim($tobj[0])." --- $ecomm\n";}
$dsql = "SELECT * FROM ".trim($tobj[0]);
$drec = ibase_query($src_db,$dsql) or die("Bad Data Query");

// Echo out the field names
//echo $comm." (";
$col = 0;
$cols = ibase_num_fields($drec);
for($f=0;$f<$cols;$f++){
$info[$f] = ibase_field_info($drec,$f);
// Quotes management - some types need quotes
if($info[$f]['type'] == "VARYING"
|| $info[$f]['type'] == "VARCHAR"
|| $info[$f]['type'] == "TIMESTAMP"
|| $info[$f]['type'] == "DATE"
|| $info[$f]['type'] == "TIME"
){
$q[$f] = $delim;
} else {
$q[$f] = "";
}

// Null management - some fields can't handle empties...
if($info[$f]['type'] == "VARYING"
|| $info[$f]['type'] == "VARCHAR"
){
// Leave the empty value - it's ok
$n[$f] = 0;
} else {
// replace the empty value with a "null"
$n[$f] = 1;
}

// ISO format management - convert dates to a suitable format
if($info[$f]['type'] == "DATE"
){
$iso_d[$f] = 1;
} else {
$iso_d[$f] = 0;
}
}
//echo ") \n";

// Echo out the data
while($dobj = ibase_fetch_row($drec)){
// Output the row(if $ins is turned on, output insert statements)
if(!$ins){
$col = 0;
while($col < $cols){
if($dobj[$col] == "" && $n[$col]){
echo "null";
} else {
echo $q[$col].$dobj[$col].$q[$col];
}
if($col + 1 < $cols){
echo $sep;
} else {
echo "\n";
}
$col++;
}
} else {
$out = "INSERT INTO ".trim($tobj[0])." (";
for($col=0;$col < $cols;$col++){
$out .= $info[$col]['name'];

if($col + 1 < $cols){
$out .= $sep;
}
}
$out .= ") VALUES (";
for($col=0;$col < $cols;$col++){
if($dobj[$col] == "" && $n[$col]){
$out .= "null";
} else {
$prep = str_replace("'",$esc."'",$dobj[$col]);
$out .= $q[$col].$prep.$q[$col];
}
if($col + 1 < $cols){
$out .= $sep;
}
}
$out .= ");\n";
if($tfer){
ibase_query($dest_db,$out) or $err = 3;
if($err == "3"){
$dberr = ibase_errmsg();
echo "Error:(".$err."), \nDB Error Description: '".$dberrtext."'.
Destination Database insert failed\n";
}
} else {
echo $out;
}
}
}
}
?>