Re: Long tablenames
From irl3b2c.att.com!bt@uunet.uu.net:
* From uunet.uu.net!rmy.emory.edu!ilist Thu Sep 22 07:14:36 1994
* Message-Id: <9409221228.AA03960@ig1.att.att.com>
* From: irl3b2c.att.com!bt@uunet.uu.net
* Date: 22 Sep 94 13:24:55 GMT
* To: rmy.emory.edu!informix-list@uunet.uu.net
* Original-From: bt
* Original-Date: Thu Sep 22 13:24:55 GMT 1994
* Email-Version: 2
* Subject: Long tablenames
* Original-To: att!rmy.emory.edu!informix-list
* Content-Type: text
* Sender: rmy.emory.edu!informix-list-owner@uunet.uu.net
* Reply-To: irl3b2c.att.com!bt@uunet.uu.net
* X-Informix-List-To: informix-list-in@dssmktg.com
* X-Informix-List-Id: <list.4751>
*
* In response to ------
* >>One simple problem:
* >> I want to export a database on (SCO) UNIX and import this
* >> database with I-SQL DOS !
* >> The dbexport on UNIX generates to long filenames, so i can4t use
* >> the SQL-Script and the unload files.
* [stuff deleted]
* Jack Parker wrote:
*
* :Try this:
* :{tabren.ace}
* :database xxxxxx end
* :
* :output
* :top margin 0
* :bottom margin 0
* :page length 1
* :end
* :
* :select tabname from systables where tabid > 99
* :end
* :
* :format
* :
* :on every row
* : print "mv ", tabname clipped, ".unl ", tabname[1,8], ".unl"
* :end
* :--- end ---
<< stuff deleted >>
This will work until to you 2 or more tables with similar names that are
longer than 8 chars.
Here is something I wrote a long time ago regarding sending schemas and
exports to DOS.
I hope someone will take it and use/modify it to make it work the way they
want. It works just great for us.
To run, run the dosexport and dosschema programs. On DOS, run the schema.4gl
or schema.sql program first to create the database. The run the dosload.4gl
or dosload.sql program to upload the data.
Here is the dosexport routine script
# --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v--
:
#------------------------------------------------------------------------------
# dosexport - Shell to export a DB to DOS format for loading
# dosload.4gl and dosload.sql programs are generated
# unload files are in dos_unl/*
#------------------------------------------------------------------------------
# Change to dbaccess when applicable
SQLPROG={$SQLPROG=isql} ; export SQLPROG
if test "$1" = "" ; then
echo "usage: dosexport db_name"
exit 1
fi
db_name=$1 ; export db_name
if test -d dos_unl ; then
rm -fr dos_unl
fi
# First export the database to get a fresh copy
if test ! -d ${db_name}.exp ; then
echo "exporting Database, please wait ....\\c"
dbexport ${db_name} 1>export.log 2>&1
if test $? != 0 ; then
echo "\\nExport of database failed"
exit 1
fi
else
echo "\\nExported Database Exists, Aborting"
exit 1
fi
echo ""
# Create the dos_unl directory for the files.
mkdir dos_unl
# Create header for dosload.4gl
echo "database ${db_name}" > dosload.4gl
echo "" >> dosload.4gl
echo "main" >> dosload.4gl
# Clear dosload.sql if exists
echo "" > dosload.sql
num=0 ; export num
# Loop through files to get real table names
for i in ${db_name}.exp/*.unl
do
tbnm=`basename $i .unl`
${SQLPROG} ${db_name} 1>$$ 2>/dev/null <<!
output to $$ without headings
select tabname from systables where dirpath matches "*$tbnm";!
cat $$ | sed '/^$/d' | sed 's/ //g' > $$.1
s=`cat $$.1` ; export s
rm -f $$ $$.1
num=`expr $num + 1` ; export num
cp $i dos_unl/${num}.unl
echo " display 'loading $s'" >> dosload.4gl
echo " load from \\"dos_unl/${num}.unl\\" insert into $s" >> dosload.4gl
echo "load from \\"dos_unl/${num}.unl\\" insert into $s;" >> dosload.sql
echo $i $s | awk '{ printf("%-25s: %s\\n",$1,$2)}'
done
echo "" >> dosload.4gl
echo "end main" >> dosload.4gl
echo "Complete"
# --^-- SNIP --^-- SNIP --^-- SNIP --^-- SNIP --^-- SNIP --^-- SNIP --^--
And here is the schema script:
# --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v-- SNIP --v--
:
#------------------------------------------------------------------------------
# dosschema - creates a schema program for DOS
#
# creates both a 4gl and sql
#------------------------------------------------------------------------------
# Change to dbaccess when applicable
SQLPROG=${SQLPROG=isql} ; export SQLPROG
db_name="" ; export db_name
fixit()
{
rm -f $$* 4gl_format sql_format schema.4gl schema.sql
echo "\\ndosschema interrupted"
exit 1
}
do_table_stuff()
{
TABNAME=`cat $$` ; export TABNAME
dbschema -d sales -t $TABNAME | \\
grep -v "[A-Z]" | \\
grep -v "{" | \\
grep -v "revoke" > $$.1
cat $$.1 | sed "s/on \\".*\\"\\.$TABNAME/on $TABNAME/g" > $$
cat $$ | grep -v "^$" > $$.1
cat $$.1 | sed -e 's/\\".*\\"\\.//g' > $$
cp $$ sql_format
cat $$ | sed -e 's/;//g' > $$.1
echo "grant all on $TABNAME to public" >> $$.1
echo "grant all on $TABNAME to public;" >> sql_format
rm -f $$
mv $$.1 4gl_format
}
usage()
{
echo "usage: dosshema [ -o ] db_name"
echo " -o = online flag"
exit 1
}
trap "fixit ; exit 1" 2 3 9 15
onl=""
if [ "$#" != "0" ] ; then
for i in $*
do
case $i in
-*) OPT=`echo $i | sed s/-//p` # remove the '-'
O=`echo $OPT | sed "s/\\(.\\).*/\\1/p"` # get first character
OPT=`echo $OPT | sed s/.//p` # remove first character
case $O in
o) onl="o" ;;
esac ;;
*) db_name=$i ; export db_name
esac
done
fi
if test "$db_name" = "" ; then
usage
fi
if [ "$onl" != "o" -a ! -d "$db_name.dbs" ] ; then
echo "Database must be in current path"
exit 1
fi
echo "Reading table names"
${SQLPROG} ${db_name} > $$ 2>/dev/null <<EOF
select tabname from systables where tabid > 99 order by tabname;EOF
cat $$ | grep -v "tabname" | grep -v "^$" | grep -v "ud_[acsp]_" > $$.tab
rm -f $$
tabcount=`wc -l $$.tab | awk ' { print $1 } '`
echo "Number of Tables: $tabcount"
echo "Creating schema.4gl and schema.sql"
echo "# DOS database creation program converted from UNIX" > schema.4gl
echo "{ DOS database creation script converted from UNIX }" > schema.sql
echo "" >> schema.4gl
echo "" >> schema.sql
e