osql

在Linux/Unix下面访问WINDOWS SQLSERVER 的ODBC驱动程序

#! /bin/sh
# $Id: osql,v 1.6 2007/02/06 02:51:18 jklowden Exp $
#
# Check odbc.ini, odbcinst, and, optionally, freetds.conf, 
# then execute isql (assume it's unixODBC's isql).  
#

USAGE="Syntax: $(basename $0) -S server -U user -P password"

while getopts I:S:U:P: OPTION
do
	case ${OPTION} in
		I)	OVER_DIR=${OPTARG}	# override
			;;
		S)	DSN=${OPTARG}
			;;
		U)	USERNAME=${OPTARG}
			;;
		P)	PASSWORD=${OPTARG}
			;;
		\?)     echo $USAGE
			exit 1
			;;
	esac
done

#cho ${DSN} ${USERNAME} ${PASSWORD}
if [ -z "${DSN}" -o -z "${USERNAME}" -o -z "${PASSWORD}" ]
then 
	
	echo $USAGE
	exit 1
fi

ISQL=$(command -v isql)
if [ -z "${ISQL}" ]
then
	echo "$(basename $0): error: no \"isql\" command found.  Is unixODBC installed?"
	exit 1
fi

# Establish ODBC prefix directory

ODBC_DIR=$(strings ${ISQL} | grep ^/ | grep -v elf | grep -v '\.so\.' | head -1 | sed 's/lib$/etc/' )

# N.B.  better than head(1) would be a loop to test the strings.  Since we're looking for a directory, 
# something like this might work:
# for D in $(strings ${ISQL} | grep ^/ | grep -v elf | sed 's/lib$/etc/' )
# do
# 	if [ -d $D ]
# 	then
# 		ODBC_DIR = $D
# 	fi
# done

if [ -z "${ODBC_DIR}" ]
then
	echo "$(basename $0): error: no potential directory strings in \"$(command -v isql)\""
	echo "isql strings are:"
	strings ${ISQL} | grep ^/ | sed 's/^/+ /'
	exit 1
fi

if [ "${OVER_DIR}" ]
then
	if [ -d "${ODBC_DIR}" ]
	then
		echo "\"${ODBC_DIR}\" (from isql) is a directory, "
		echo "	but is overridden by \"${OVER_DIR}\"."
	else 
		echo "${ODBC_DIR} (from isql) is not a directory"
		echo "	and is overridden by \"${OVER_DIR}\"."
	fi
	
	if [ -d "${OVER_DIR}" ]
	then
		ODBC_DIR=${OVER_DIR}
	else
		echo "$(basename $0): error: \"${OVER_DIR}\" is not a directory (disallowing)"
	fi
fi

if [ ! -d "${ODBC_DIR}" ]
then
	echo "$(basename $0): error: ${ODBC_DIR} (from isql) is not a directory!"
	echo "isql strings are:"
	strings ${ISQL} | grep ^/ | grep -v elf | grep -v '\.so\.' | sed 's/^/+ /'
	return 1
fi

echo "looking for odbc.ini and odbcinst.ini in ${ODBC_DIR}"

# Look for server entry in odbc.ini 

for F in "${HOME}/.odbc.ini" "${ODBC_DIR}/odbc.ini"
do
	if [ -r "$F" ]
	then
		echo '	'reading \"$F\"
	else
		echo '	'cannot read \"$F\"
		continue
	fi

	grep "[${DSN}]" $F > /dev/null

	if [ $? -eq 0 ]
	then
		echo "[${DSN}]" found in \"$F\"
		ODBC_INI=$F
		break
	else
		echo "[${DSN}]" not found in \"$F\"
	fi
done

if [ -z "${ODBC_INI}" ]
then
	echo "$(basename $0): error: unable to locate ${DSN} in any odbc.ini"
	exit 1
fi

# Report finding of server entry
echo found this section:
SED_CMD="/^\[${DSN}\]/,/^[[:space:]]*$/ { s/^/	/; p; }"
sed -ne "${SED_CMD}" ${ODBC_INI}

#
# Examine server entry in odbc.ini 
#

# Find the driver in the servername or default section
for D in "${DSN}" 'default' 
do
	echo "looking for driver for DSN [$D]"
	grep "$D" ${ODBC_INI} > /dev/null
	if [ $? -eq 0 ]
	then
		CMD="/^\[$D\]/,/^[[:space:]]*$/ { s/^/	/; p; }"
		DRIVER_LINE=$(sed -ne "${CMD}" ${ODBC_INI} \
				| grep -Ei '^[[:space:]]*driver[[:space:]]*=')
		if [ -z "${DRIVER_LINE}" ]
		then
			echo "no driver mentioned for [$D] in $(basename ${ODBC_INI})"
			continue
		fi

		DRIVER=$(echo ${DRIVER_LINE} | awk '{print $3}')
		if [ "${DRIVER}" ]
		then
			echo "driver \"${DRIVER}\" found for [$D] in $(basename ${ODBC_INI})"
			break
		else
			echo "driver line incomplete for [$D] in $(basename ${ODBC_INI})"
			continue
		fi
	fi
done

if [ -z "${DRIVER}" ]
then
	echo "$(basename $0): error: no driver found for [${DSN}] in $(basename ${ODBC_INI})"
	exit 1
fi

# get filename of driver
echo found driver named \"${DRIVER}\"
if [ ! -r "${DRIVER}" ]
then
	# not a filename, look it up
	DRIVERNAME=${DRIVER}
	ODBC_INST="${ODBC_DIR}/odbcinst.ini"
	echo "${DRIVERNAME} is not a readable file"
	echo "looking for entry named [${DRIVERNAME}] in ${ODBC_INST}"

	grep "${DRIVERNAME}" ${ODBC_INST} > /dev/null
	if [ $? -ne 0 ]
	then
		if [ $? -eq 1 ]
		then
			echo "$(basename $0): error: no driver entry [${DRIVERNAME}] in ${ODBC_INST}"
		fi
		exit 1;
	fi
	
	CMD="/^\[${DRIVERNAME}\]/,/^[[:space:]]*$/ { s/^/	/; p; }"
	DRIVER_LINE=$(sed -ne "${CMD}" ${ODBC_INST} \
				| grep -Ei '^[[:space:]]*driver[[:space:]]*=')
	if [ -z "${DRIVER_LINE}" ]
	then
		echo "no driver mentioned for [${DRIVERNAME}] in $(basename ${ODBC_INST})"
		exit 1
	fi

	DRIVER=$(echo ${DRIVER_LINE} | awk '{print $3}')
	if [ -z "${DRIVER}" ]
	then
		echo "driver line incomplete for [${DRIVERNAME}] in $(basename ${ODBC_INST})"
		exit 1
	fi

	echo "driver \"${DRIVER}\" found for [${DRIVERNAME}] in $(basename ${ODBC_INST})"
fi

if [ -z "${DRIVER}" ]
then
	echo 'error: sorry, failed sanity check: ${DRIVER} is null' 
	exit 1
fi

if [ -r "${DRIVER}" ]
then
	echo "${DRIVER} is a readable file"
else
	echo "${DRIVER} is not a readable file"
	echo "$(basename $0): error: no driver found for ${DSN}"
	exit 1
fi

# find the server/servername
SERVER_LINE=$(sed -ne "${SED_CMD}" ${ODBC_INI} \
		| grep -Ei '^[[:space:]]*server(name)*[[:space:]]*=')

ATTRIBUTE=$(echo ${SERVER_LINE} | awk '{print $1}')

if [ -z "${ATTRIBUTE}" ]
then
	echo "$(basename $0): neither \"Server\" nor \"Servername\" found for [${DSN}] in $(basename ${ODBC_INI})"
	exit 1
fi

echo ${SERVER_LINE} | grep -i servername >/dev/null

#
# Find the server's hostname 
#
if [ $? -eq 0 ] # ODBC-Combined
then
	TDS_SERVER=$(echo ${SERVER_LINE} | awk '{print $3}')
	echo 'Using ODBC-Combined strategy'
	echo "FreeTDS servername is \"${TDS_SERVER}\" (from ${ODBC_INI})"
	if [ -z "${TDS_SERVER}" ]
	then
		exit 1
	fi

	# Look for $TDS_SERVER in freetds.conf 
	
	FREETDS_DIR=$(tsql -C | grep 'freetds.conf directory' | awk -F: '{print $2}' | sed 's/^ *//')
	if [ -z "${FREETDS_DIR}" ]
	then
		echo "$(basename $0): error: unable to locate directory for freetds.conf using \"$(command -v tsql)\""
		exit 1
	fi
	
	for F in "${HOME}/.freetds.conf" "${FREETDS_DIR}/freetds.conf"
	do
		echo "looking for [${TDS_SERVER}] in $F"
		if [ -r "$F" ]
		then
			echo "\"$F\" is a readable file"
		else
			echo cannot read \"$F\"
			continue
		fi

		grep "[${TDS_SERVER}]" $F > /dev/null

		if [ $? -eq 0 ]
		then
			FREETDS_CONF=$F
			break
		else
			echo "[${TDS_SERVER}]" not found in \"$F\"
		fi
	done
	
	if [ -z "${FREETDS_CONF}" ]
	then
		echo "$(basename $0): error: unable to locate ${TDS_SERVER} in any freetds.conf"
		exit 1
	fi

	# Examine server entry in freetds.conf 

	echo found this section:
	SED_CMD="/^\[${TDS_SERVER}\]/,/^[[:space:]]*$/ { s/^/	/; p; }"
	sed -ne "${SED_CMD}" ${FREETDS_CONF}

	SERVER_LINE=$(sed -ne "${SED_CMD}" ${FREETDS_CONF} \
			| grep -Ei '^[[:space:]]*host[[:space:]]*=')

	HOST=$(echo ${SERVER_LINE} | awk '{print $3}')

	if [ -z "${HOST}" ]
	then
		echo "$(basename $0): no \"host\" entry found for [${TDS_SERVER}] in $(basename ${FREETDS_CONF})"
		exit 1
	fi
	
else # odbc.ini contains a "server", a DNS host
	HOST=$(echo ${SERVER_LINE} | awk '{print $3}')
	echo "\"${ATTRIBUTE}\" found, not using freetds.conf"
	echo "FreeTDS servername is \"${HOST}\""
	if [ -z "${HOST}" ]
	then
		echo "$(basename $0): no value found for \"${ATTRIBUTE}\" entry in $(basename ${ODBC_INI})"
		exit 1
	fi
fi

# Now we have a DNS hostname for the server in HOST

CMD="host ${HOST}"

OUTPUT=$(${CMD})
ADDRESS=$(echo ${OUTPUT} | sed 's/..*has address //')
if [ -z "${ADDRESS}" ]
then
	echo "$(basename $0): no IP address found for \"${HOST}\""
	exit 1
fi

echo ${HOST} has address ${ADDRESS}

# Report what we know and exec isql 

printf "\n";
printf "%30s:\t%-30s\n" DSN ${DSN}
printf "%30s:\t%-30s\n" "Server's hostname" ${HOST}
printf "%30s:\t%-30s\n" Address ${ADDRESS}
printf "%30s:\t%-30s\n" Driver ${DRIVER}
printf "\n";

echo Attempting connection as ${USERNAME}... 
exec isql ${DSN} ${USERNAME} ${PASSWORD} -v