#!/usr/bin/python

# mssqlquery - Run SQL queries on a Microsoft SQL Server
# Copyright (C) 2007-2011  Sertan Kolat (warex) - sertan@olympos.org

# version 0.3
# last update: Feb 28, 2011

# pymssql library is required
# download from http://pymssql.sourceforge.net

import getopt,sys
import getpass
from os import name as ostype

import readline
histfile='.history_mssqlquery'
try:
    readline.read_history_file(histfile)
except IOError:
    pass
import atexit
atexit.register(readline.write_history_file, histfile)



try:
	import _mssql
except ImportError:
	print "pymssql library is required to run this program.\nplease download it from http://pymssql.sourceforge.net"
	sys.exit(2)

def usage():
	print "mssqlquery - Run SQL queries on a Microsoft SQL Server"
	print "\nUsage: mssqlcmd.py [options]"
	print "\nOptions:"
	print "-h, --help	Show this help message and exit"
	print "-s SERVER	Remote server hostname or IP address."
	print "		On Windows you can use sqlhost\instancename"
	print "		to connect to a non-default(named) instance.\n"
	print "-p PORT		Server port (default 1433)"
	print "-u USERNAME	SQL server username (default 'sa')."
	print "		Will ask for a password if -u option is given.\n"
	#print "-d DATABASE	Database name to run SQL queries on (default: master)."

def main():
	# Set up options and parse arguments
	if len(sys.argv) == 1:
		usage()
		sys.exit(2)

	try:
		opts, args = getopt.getopt(sys.argv[1:], "hds:u:p:", ["help"])
	except getopt.GetoptError:
		#print help information and exit
		usage()
		sys.exit(2)
	
	#dbhost='localhost'
	dbhost=''
	dbport=1433
	dbuser='sa'
	dbpass=''
	for o, a in opts:
		if o in ("-h", "--help"):
			usage()
			sys.exit()
		if o in "-u":
			dbuser = a
			try:
				dbpass = getpass.getpass('Enter password for %s: ' % a)
			except KeyboardInterrupt:
				sys.exit()
		if o in "-p":
			dbport = a
		if o in "-s":
			dbhost = a
#		if o == "-d":
#			database = a
		
	
	if not dbhost:
		print 'You must provide a server to connect! (-h for help)'
		sys.exit(2)
	
	if dbport != 1433:
		if ostype in ["nt", "ce"]:
			dbhost = dbhost + ',' + dbport
		else:
			dbhost = dbhost + ':' + dbport
		
	try:
		conn=_mssql.connect(dbhost, dbuser, dbpass)
	except:
		print 'Error connecting to MSSQL server!'
		sys.exit(2)
	else:
		print 'Connected to %s!' % dbhost


	def runSQLQuery(c, q):
		try:
			ret=c.execute_query(q)
		except _mssql.MssqlDatabaseException,e:
			print e.message
		else:
			#print '='*70
			for row in conn:
				print row[0]
			#print '='*70
			#result=conn.fetch_array()
			#if len(result[0][0]) == 1:
			#	if not result[0][0][0][0]:
			#		for s in result[0][2]:
			#			if s[0] != None:
			#				print '%s' % s
			#	else:
			#		print '='*40
			#		print '%s (returned %s rows)' % (result[0][0][0][0], result[0][1])
			#		print '='*40
			#		for s in result[0][2]:
			#			print s[0]
			#else:
			#	print '='*40
			#	print [i[0] for i in result[0][0]]
			#	print '='*40				
			#	for s in result[0][2]:
			#		if s[0] != None:
			#			print '%s' % str(s)

	while 1:
		try:
			res	= raw_input('mssqlquery> ')
		except KeyboardInterrupt:
			print "Aborted"
			sys.exit(0)
		except:
			print "Exit"
			sys.exit(0)

		if res.lower() == "q":
			break
		elif res.lower() == "h" or res.lower() == "help" or res.lower() == "?":
			print "help yourself, use q to quit"
		else:
			runSQLQuery(conn, res)


if __name__ == '__main__':
	main()

