Problems connecting to MS SQL from Robot Framework

[OliverNZ]

Hello,

So I run on ubuntu 14.04 and am trying to connect to or MS SQL Server during my RF tests. So I installed the DatabaseLibrary and installed pymssql. In RF I do:

${DATABASE_IP}    local/core   

${DATABASE_PORT}    1433   

${DATABASE_PT_CORE}    DB_Local    

${DATABASE_USER}    xxxxx

${DATABASE_PASSWORD}    xxxxx

Connect To Database  pymssql ${DATABASE_PT_CORE} ${DATABASE_USER} ${DATABASE_PASSWORD} ${DATABASE_IP} ${DATABASE_PORT}

or

Connect To Database  pymssql ${DATABASE_PT_CORE} ${DATABASE_USER} ${DATABASE_PASSWORD} ${DATABASE_IP}

I get the response:

Starting test: Ptus.Robotframework.TestSuites.Database Test.Database Test.Database Test 001

20140626 09:23:48.208 :  FAIL : TypeError: connect() got an unexpected keyword argument 'port'

Ending test:   Ptus.Robotframework.TestSuites.Database Test.Database Test.Database Test 001

In Python though it all seems to work. I can do this and get a result:

import pymssql

conn = pymssql.connect(host='local\core', user='xxxxx', password='xxxxx', database='DB_Local')

cursor = conn.cursor()

cursor.execute("""select * from TestTable""")

So why am I getting a port issue? Hope someone could clear this up for me. 

Thanks

Oliver

[Janne Härkönen]

Hi Oliver,

On Thu, Jun 26, 2014 at 12:30 AM, OliverNZ <oliver....@gmail.com> wrote:

Hello,

Connect To Database  pymssql ${DATABASE_PT_CORE} ${DATABASE_USER} ${DATABASE_PASSWORD} ${DATABASE_IP} ${DATABASE_PORT}

or

Connect To Database  pymssql ${DATABASE_PT_CORE} ${DATABASE_USER} ${DATABASE_PASSWORD} ${DATABASE_IP}

I get the response:

Starting test: Ptus.Robotframework.TestSuites.Database Test.Database Test.Database Test 001

20140626 09:23:48.208 :  FAIL : TypeError: connect() got an unexpected keyword argument 'port'

Ending test:   Ptus.Robotframework.TestSuites.Database Test.Database Test.Database Test 001

In Python though it all seems to work. I can do this and get a result:

import pymssql

conn = pymssql.connect(host='local\core', user='xxxxx', password='xxxxx', database='DB_Local')

cursor = conn.cursor()

cursor.execute("""select * from TestTable""")

 

So why am I getting a port issue? Hope someone could clear this up for me. 

My guess is that the pymssql driver does not support port argument in connect(). So this is likely to fail in Python too 

conn = pymssql.connect(host='local\core', user='xxxxx', password='xxxxx', database='DB_Local', port=1433)

The DatabaseLibrary always calls connect with some value for port, thus causing the failure. If this is the case, there's no easy workaround and the possibilities are either to patch the library or submit an issue and wait for a possible fix.

hth,

--Janne

[OliverNZ]

Haha! Thanks Janne! Easy as explanation. Should have seen that myself. It looks like pymssql doesn't like the port argument. I'll do some more digging and then patch DatabaseLibrary and post a request for a fix.

Thanks!
Oliver

[Kevin O.]

Hold on.

Before you look into patching the library, try using Connect To Database Using Custom Params.

There is not standard parameters for the connect method in the DB API. Connect To Database has some common ones that clearly don't work for every implementation.

Connect To Database Using Custom Params    pymssql  host='local\\core', user='xxxxx', password='xxxxx', database='DB_Local'

see

http://franz-see.github.io/Robotframework-Database-Library/api/0.6/DatabaseLibrary.html#Connect%20To%20Database%20Using%20Custom%20Params

[Jerry Schneider]

Also, from what I can see for examples of pymssql connect strings it should support port=

conn = pymssql.connect(host='sql10.example.com', user='usr', password='pwd',database='my_db', port='3010')

you should also be able to add the port to the hostname

conn = pymssql.connect(host='sql10.example.com:3010', user='usr', password='pwd',database='my_db')

I will try to look into why  this is failing, but I do  not have any mssql servers right now to connect to.  Does the Connect To Database Using Custom Params work?

jer

--
You received this message because you are subscribed to the Google Groups "robotframework-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to robotframework-u...@googlegroups.com.
To post to this group, send email to robotframe...@googlegroups.com.
Visit this group at http://groups.google.com/group/robotframework-users.
For more options, visit https://groups.google.com/d/optout.

-- 
Linux registered user #475536
Ubuntu registered user #28583

[Oliver Erlewein]

Hi,

So...answered my own question. And read the whole response 1st!

I edited the code (/usr/local/lib/python2.7/dist-packages/DatabaseLibrary/connection_manager.py) and added (after line 79):

     elif dbapiModuleName in ["pymssql"]:

            dbPort = str(dbPort or 1433)

            logger.debug ('Connecting using : %s.connect(database=%s, user=%s, password=%s, host=%s, port=%s) ' % (dbapiModuleName, dbName, dbUsername, dbPassword, dbHost, dbPort))

            self._dbconnection = db_api_2.connect (database=dbName, user=dbUsername, password=dbPassword, host=dbHost)

So that gave me a connection but things were still weird. I couldn't get Select statements to work. I could see them reach the MS SQL DB and get executed but the result was empty. So I searched some more and as it turns out I did it all wrong ;-)

I installed pymssql by doing 

apt-get install python-pymssql

that is WRONG!!!! It doesn't work according to some sources. So you need to do the following (all as sudo):

  (sudo apt-get uninstall python-pymssql  <--if you already did what I did!)

    sudo apt-get install python-dev libxml2-dev libxslt-dev

  sudo pip install Cython

  sudo pip install pymssql

Now everything seems to be working as advertised! I also took my code back out and it still works. Not sure though that that is 100% original code now because I changed my system so much. Maybe someone out there can confirm this some day. 

So "works on my machine now" and I am happy. pymssql does treat the port variable a bit differently so it could be something to clean up some day.

Cheers & Thanks for those that helped! 

Oliver

[Oliver Erlewein]

Hi again,

I was a bit too quick. So when I remove my code it works but I do get this error at the end of the test:

unexpected error: Exception in thread Thread-1 (most likely raised during interpreter shutdown):

Traceback (most recent call last):

  File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner

  File "/usr/lib/python2.7/threading.py", line 763, in run

  File "/usr/lib/python2.7/SocketServer.py", line 235, in serve_forever

<type 'exceptions.AttributeError'>: 'NoneType' object has no attribute 'select'

If I include the code from the previous post it's gone. No idea at all why that would be. 

So if anyone is interested in including my "fix" in DatabaseLibrary please point me in the direction where it tells me how to do that.

Cheers Oliver

[Kevin O.]

I would not be too quick to assume your fix fixes it. That error could easily be an intermittent one. It looks like it was caused by the socket server still executing after the reference to the the select module got garbage collected because the interpreter was shutting down.

I would try adding calling Disconnect From Database in a suite teardown. This should close the socket connection before the interpreter starts shutdown.

This is just an educated guess.

I have not seen this error and I never bother disconnecting from databases, but I use cx_Oracle, adodbapi (MSSQL), and MySQLdb. I have never used pymssql.