Musings

A random collection

Archive for the ‘databases’ Category

TECH: SQL Variables

Need to use variables in your SQL script in SQL Server Management Studio. Here you go:

DECLARE @Commodity int
DECLARE @SettleDate datetime

SET @Commodity = 35
SET @SettleDate = '6/14/2011'

SELECT * FROM SettlePrices
WHERE CommodityId = @Commodity
    AND SettleDate = @SettleDate
Advertisements

Written by curious

June 15, 2011 at 3:43 pm

Posted in databases

TECH: Connecting to databases from Linux (using PHP, python etc)

Getting started

  1. Install Free TDS (freetds) and unixODBC
  2. Try connecting to the database (freetds comes with 2 clients: tsql, fisql). tsql is lower level tool. fisql is a replacement for isql (provided with unixODBC)
    tsql -S dbserver -U sa -P password -D MyOwnDB
    2> select * from SomeTable
    2> go
    
    fisql -S dbserver -U sa -P password
    1> use MyOwnDB
    1> go
    2> select * from SomeTable
    2> go
    
    fisql -S dbserver -U sa -P password -c ';'
    1> use MyOwnDB;
    2> select * from SomeTable;
    
  3. To try unixODBC, use test tool
  4. $ cat $HOME/.odbcinst.ini
    [FreeTDS]
    Description             = MS SQL Server Driver
    Driver          = /usr/lib/libtdsodbc.so.0
    Setup           = /usr/lib/libtdsS.so.2
    FileUsage               = 1
    UsageCount              = 1
    
    $ cat $HOME/.odbc.ini
    [SomeServerDSN]
    Driver = FreeTDS
    Description = Dev Server
    Server = serverhostname
    Trace = Yes
    Database = MyOwnDB
    
    $ isql SomeServerDSN dbuser dbpassword
    

Using Python (pyodbc) to connect to a SQL Server

  1. Install pyodbc
  2. Direct Connection (no DSN)
    import pyodbc
    cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=serverhostname;DATABASE=MyOwnDB;UID=youruid;PWD=secret')
    cursor = cnxn.cursor()
    cursor.execute("select count(*) from SomeTable")
    row = cursor.fetchone()
    if row:
      print row
    else:
      print "No data"
    
  3. Use a DSN
    import pyodbc
    cnxn = pyodbc.connect('DSN=SomeServerDSN;UID=youruid;PWD=secret')
    cursor = cnxn.cursor()
    cursor.execute("select count(*) from SomeTable")
    row = cursor.fetchone()
    if row:
      print row
    else:
      print "No data"
    
  4. More help: code.google.com/p/pyodbc/wiki/GettingStarted

Using Django to connect to SQLServer

  1. Install django-pyodbc
    svn co http://django-pyodbc.googlecode.com/svn/trunk/ django-pyodbc
    export PYTHONPATH=$HOME/django-pyodbc
    
  2. Set up settings.py
    DATABASES = {
        'default': {
            'ENGINE': 'sql_server.pyodbc',
            'NAME': 'MyDBName',
            'USER': 'youuid',
            'PASSWORD': 'secret',
            'OPTIONS': {
              'driver': 'FreeTDS',
              'dsn': 'SomeServerDSN',
              },
        }
    }
    
  3. Test connection
    python manage.py shell
    >>> from django.db import *
    >>> c = connections['default'].cursor()
    >>> r = c.execute("select count(*) from SomeTable")
    >>> r = c.fetchone()
    >>> print r
    
  4. More help: django-pyodbc on Code.Google

Using PHP to connect to a SQL Server

  1. Install php, php-odbc, php-mssql
  2. Allow HTTPd to connect to database:
    setsebool -P httpd_can_network_connect_db 1
    
  3. Try out the following PHP script to connect to the database using ODBC
    <?php
    // connect to DSN MSSQL with a user and password
    print("Running to display traders<br/>\n");
    $connect = odbc_connect("Driver=FreeTDS;Server=SomeServerDSN", "DbUser", "Password") or die("couldn't connect");
    odbc_exec($connect, "use MyOwnDB");
    $result = odbc_exec($connect, "SELECT iField1, strField2 " .
            "FROM tblTable1");
    while(odbc_fetch_row($result)){
      print(odbc_result($result, "iField1") .
            ' ' . odbc_result($result, "strField2") . "<br>\n");
    }
    odbc_free_result($result);
    odbc_close($connect);
    ?>
    
  4. Now lets try using php-mssql
    <?php
    $myServer = "ServerHostName";
    $myUser = "DbUser";
    $myPass = "DbPassword";
    $myDB = "MyOwnDB";
    
    //connection to the database
    $dbhandle = mssql_connect($myServer, $myUser, $myPass)
      or die("Couldn't connect to SQL Server on $myServer");
    
    //select a database to work with
    $selected = mssql_select_db($myDB, $dbhandle)
      or die("Couldn't open database $myDB");
    
    //declare the SQL statement that will query the database
    $query = "SELECT iField1, strField2 ";
    $query .= "FROM tblTable1";
    
    //execute the SQL query and return records
    $result = mssql_query($query);
    
    $numRows = mssql_num_rows($result);
    echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
    
    print "<table>";
    print "<tr>";
    print "<th>ID</th>";
    print "<th>Name</th>";
    print "</tr>";
    
    //display the results
    while($row = mssql_fetch_array($result))
    {
    print "<tr>";
      echo "<td>" . $row["iField1"] . "</td>";
      echo "<td>" . $row["strField2"] . "</td>";
    print "</tr>";
    }
    print "</table>";
    //close the connection
    mssql_close($dbhandle);
    ?>
    

Connecting to MySQL from PHP

  1. Install php-mysql
  2. Run the following PHP script
    <?php
    $myServer = "theMySqlServer"; # myServer = "theMySqlServer:3306";
    $myUser = "mysql";
    $myPass = "dbPassword";
    $myDB = "dbName";
    
    //connection to the database
    $dbhandle = mysql_connect($myServer, $myUser, $myPass)
      or die("Couldn't connect to SQL Server on $myServer");
    
    //select a database to work with
    $selected = mysql_select_db($myDB, $dbhandle)
      or die("Couldn't open database $myDB");
    
    //declare the SQL statement that will query the database
    $query = "SELECT iField1, sField2 ";
    $query .= "FROM tblTable1 ";
    
    //execute the SQL query and return records
    $result = mysql_query($query);
    
    $numRows = mysql_num_rows($result);
    echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
    
    print "<table>";
    print "<tr>";
    print "<th>ID</th>";
    print "<th>Name</th>";
    print "</tr>";
    
    //display the results
    while($row = mysql_fetch_array($result))
    {
    print "<tr>";
      echo "<td>" . $row["iField1"] . "</td>";
      echo "<td>" . $row["sField2"] . "</td>";
    print "</tr>";
    }
    print "</table>";
    //close the connection
    mysql_close($dbhandle);
    ?>
    

Written by curious

April 21, 2010 at 10:39 am

Posted in databases, linux

TECH: SQL Server Backups

backup database [DatabaseName] to disk = 'c:\a1.bak' with noformat, noinit, name = 'Some name for the backup', norewind, nounload, skip

Written by curious

March 25, 2010 at 2:13 pm

Posted in databases

TECH: SQL from Command Line

How to connect to SQL Server 2008 from Command Line?

"C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe" -U sa -P oyeoye -S hostname -d AhaDB

Use -i input-file to run a SQL script

Reference: MSDN sqlcmd

How do you connect to MySQL from command line (on Windows)?

"c:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" -b -h host -P 3306 -u mysql -pchup-oye AhaDB

You will need MySQL Server command line tools installed.

Written by curious

February 24, 2010 at 3:48 pm

Posted in databases

TECH: Exporting schema from SQL Server

Use “Microsoft SQL Server Management Studio”, right click on the database node.

From the popup menu choose “Tasks” → “Generate Scripts …” to launch Script Wizard

From the “Script Wizard”, select appropriate database in the list and check “Script all objects in the selected database” checkbox at the bottom.

Click on “Next”. Review options if you want to and click “Next”.

Select the file name you want it to create the script in.

Written by curious

February 4, 2010 at 10:32 am

Posted in databases

TECH: Basic MySQL Commands

  1. An example MySQL session:
    CREATE DATABASE somedb;
    CREATE USER "someuser"@"localhost" IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON somedb.* TO "someuser"@"localhost";
    FLUSH PRIVILEGES;
    
    SHOW DATABASES;
    USE somedb;
    SHOW TABLES;
    
    DROP TABLE tablename;
    
  2. Create a table:
    CREATE TABLE tablename
    (
        colname1 DATATYPE,
        colname2 DATATYPE,
        PRIMARY KEY (colname1)
    );
    INSERT INTO tablename VALUES
    ( value1, value2 )
    ;
    
  3. Alter a table
    ALTER TABLE tablename
        ADD COLUMN colname datatype,
        ADD PRIMARY KEY (colname2)
    ;
    [/sourcecode language="sql"]</li>
    
    <li>Select rows
    
    SELECT * FROM tablename;
    
  4. Update rows
    UPDATE tablename SET
        colname = value;
    
  5. Delete rows
    DELETE FROM tablename WHERE condition;
    

First time startup

Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h fc13 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

Written by curious

January 26, 2010 at 11:33 am

Posted in databases