Write a command script to be run by SQL Editor

[pengzhang]

Hi There,

Can I write a command script file run by SQL Editor or something else and the script is smart enough to detect if a table or database object exists before it creates the object.

I can do it in SQL Server where I can use "if exists (...)". But can I do it in Informix?

Thanks

 

[vpshriyan]

Hi,

One possible solution using unix script is as follows:

#!/bin/sh

db="testdb"
aa=`echo "select count(*) from sysdatabases where name='$db'"|dbaccess sysmaster
`
aa=`echo $aa | awk '{ print $2}'`
if test $aa = 1
then
echo database $db exists.
fi

tab="tab1"
aa=`echo "select count(*) from systables where tabname='$tab'"|dbaccess rntdb`
aa=`echo $aa | awk '{ print $2}'`
if test $aa = 1
then
echo table $tab exists.
fi

Regards
Shriyan

 

[pengzhang]

Thanks Shriyan,

But do you have some thoughts on Windows 2000 platform? I'm thinking maybe I have to write a E-SQL program.

Peng

 

[ajw0]

This is a good reply. On W2000 you can get MKS toolkit to permit useful shell scripts

 

[vpshriyan]

pengzhang:

Here is a Win2000 version of the same Unix script. You will notice that in all E-SQL ( embbedding of SQL ) programs or scripts the SQL does not change, they remain same; only the relevant program/script and logic/syntax varies. Even if you want to impliment this in any FrontEnd tools (ESQL/C, Informix-4GL, VB, PB etc.) again the SQL part will remain exactly the same.

Regards,
Shriyan


@echo off

set db=testdb
set tab=tab2

echo select count(*) from sysdatabases where name='%db%' | dbaccess sysmaster | findstr "1$" > NUL

if %ERRORLEVEL% == 0 ( echo database %db% exists.
echo create other database objects here.
) else (
echo %db% not found in %INFORMIXSERVER%
goto :end
)

echo select count(*) from systables where tabname='%tab%' | dbaccess %db% | findstr "1$" > NUL
if %ERRORLEVEL% == 0 ( echo database %tab% exists.
echo create other table objects here.
) else (
echo %tab% not found in %db%
)

:end

 

[pengzhang]

Thanks Shriyan!

It's very helpful. But I got another problem: when I accessed another database, dbaccess doesn't like using clause to input a password except I create another SQL scipt containing the using clause.

I need to pass the actual password to dbaccess. Do you know if there are some walkaround about this?

Thanks

Peng

 

[vpshriyan]

Peng,

Following for example at Win2000 prompt:
echo CONNECT TO 'testdb' USER informix USING 'informix' | dbaccess

will result into:
32412: USING clause unsupported. DB-Access will prompt you for a password.

echo CONNECT TO 'testdb' USER informix | dbaccess
will prompt for user password at run time.

Regards,
Shriyan

 

[pengzhang]

Sorry, Shriyan. Maybe I didn't make the question clear. What I wanted actually is how can I pass in the password without having to manually input them at Runtime. I just wanted to run a script to update the database at background without any interruption.

Although I could use the using clause in a SQL script, the password in the SQL script is hardcoded. I need the password as a paremeter 'cause the time I run the script I would know the password but the password would be different everytime when running the script. You know what I'm saying here? It would be perfect that dbaccess or some other tool has the functionality to allow me to include password as the command options.

Thanks a lot

Peng