How to connect to SQL Server database to setup test data

[JayneT]

Hi there,

I've just started working with Robot Framework after spending a lot of time using Specflow and I'm struggling to find a way of connecting to a SQL server database. The database library mentioned on the Robot Framework site doesn't work with SQL Server - do I have to resort to writing my own library and using Python to connect to SQL server? I'd really appreciate any help on this as I'd like to be able to setup test data in the database before running specific test cases (like the BeforeScenario tag in Specflow) and not just having a predefined set of test data on the server before running the tests.

Any ideas?

[Tatu Aalto]

Ugh

Could you define more better what is your problem: describe your is
problem (what keyword and possible sample test case) and send a possible
error messages would be nice. Also defining the which database library
(there at three that I know) you are using and what sql server (I would
quess that it is Microsoft SQL server, but one can use SQL Server name
from almost any sql database) you are using.

-Tatu

> --
> 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/groups/opt_out.

[Guy Kisel]

If you're using MS SQL Server, get the pymssql module, and then tell the Robot Framework Database library that you want to connect using pymssql. After that all you have to do is enter your hostname, username, password, and so on. It should be pretty straightforward.

Think of the Database library as a generic wrapper for other Python database modules.

[JayneT]

Excellent, it worked a treat - many thanks. 

[Kevin O.]

We did not have to resort to any coding. There may be other approaches, but here is what is working for us:

Install PyWin32 > release 211, which provides the adodbapi database api. Alternatively, install adodbapi separately.

Then you can connect to a database using a connection string. Example:

Connect To Database Using Custom Params    adodbapi    Provider=SQLOLEDB.1;Data Source=sql1.example.com;Initial Catalog=MyDatabase;User Id=dbUser;Password=password;

Kevin

[Kevin O.]

Sorry, copied that wrong. To get it to work I had to wrap it in quotes because the library uses eval.

Connect To Database Using Custom Params    adodbapi    'Provider=SQLOLEDB.1;Data Source=sql1.example.com;Initial Catalog=MyDatabase;User Id=dbUser;Password=password;'

[Guy Kisel]

Just for comparison purposes, here's my connection keyword call:

Connect To Database    pymssql    ${DB_NAME}    ${DB_USERNAME}    ${DB_PASSWORD}    ${DB_HOSTNAME}    ${DB_PORT}

Nice and simple.

[Kevin O.]

adodbapi's connect is just two parameters, conn string & timeout

You should give cx_oracle a spin. Its so bad there is a helper method to generate the value to pass as the value of the dsn parameter that connect() takes.

http://cx-oracle.sourceforge.net/html/module.html#cx_Oracle.makedsn 

[chandramo...@ggktech.com]

Hi Jayne,

Robot Framework does work with SQL Server, you need to import databaselibrary using pip command and follow the below lines of code  (cmd>pip install robotframework-databaselibrary)

****************************************************

** Settings **

Suite Setup       Connect To Database    pymssql  ${DBName}    ${DBUser}    ${DBPass}    ${DBHost}    ${DBPort}

Library           DatabaseLibrary

Library           OperatingSystem

** Variables **

${DBName}         yourdbname

${DBUser}           username

${DBPass}           password

${DBHost}           hostname

${DBPort}            PortNumber

** Test Cases **

tc1 Drop person table

    [Tags]    db    smoke

    ${output} =    Execute SQL String    Drop TABLE person

    Log    ${output}

    Log    "table Dropped successfully"

    Should Be Equal As Strings    ${output}    None

****************************************************

useful sql script:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName, 

       @@SERVERNAME AS DBHost, 

       local_net_address AS InstanceIPAddress, 

   local_tcp_port AS InstancePort

  FROM sys.dm_exec_connections WHERE session_id = @@spid

[lorenzo.b...@ucdconnect.ie]

Hi Jayne,

I faced the exact same problem, and I ended up using pyodbc to handle the connection. You need to pass in the arguments as follows:

  DatabaseLibrary.Connect To Database   pyodbc  ${DBName}   ${DBUser}   ${DBPass}   ${DBHost}   ${DBPort}

It works very well with our MS SQL Database. 

Hope this helps,

Best,

Lorenzo