how do I successfully use PDO with my ODBC for MSSQL?

how do I successfully use PDO with my ODBC for MSSQL? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: how do I successfully use PDO with my ODBC for MSSQL? (/thread-11163.html)

how do I successfully use PDO with my ODBC for MSSQL? - cyberion1985 - Jun-26-2018

Windows Server 2016 64 bit (Fully Updated)
WAMP/3.1.3 (Win32)
Apache/2.4.33 (Win32)
PHP/7.2.4 (Win32)

I am trying to connect to my database on a SQL Server but can't seem to get the connection working. I have tried several approaches, downloaded files, enabled extensions but none of them seem to work. I have established that the SQL Server has Named Pipes and that Remote Connections are allowed. I have also confirmed that he password and username work:

<?php

$driver='{ODBC Driver 17 for SQL Server}';
/* $driver='{ODBC Driver 13 for SQL Server}'; */
/* $driver='{ODBC Driver 11 for SQL Server}'; */
$dsn='phpodbc11';
$hostname='host01';
$database='data01';
$username='root';
$password='pass123';

$conn = new PDO("odbc:$dns")
$conn = new PDO("dblib:host=$hostname;dbname=$database", $username,$password);
$conn = new PDO("sqlsrv:Server=$hostname;Database=$database", $username, $password);
$conn = new PDO("odbc:Driver=$driver;Server=$hostname;Database=$database", $username, $password);

?>

php.ini extensions :

extension=pdo_odbc
extension=php_mysqli.dll
extension=php_ldap.dll
extension=php_pdo_sqlsrv_72_ts_x86.dll
extension=php_sqlsrv_72_ts_x86.dll

Errors I get for each of the aforementioned new PDO attempts

SQLSTATE[IM002] SQLConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

could not find driver

SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x86: https://go.microsoft.com/fwlink/?LinkId=163712

SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

RE: how do I successfully use PDO with my ODBC for MSSQL? - buran - Jun-26-2018

This is Python forum, not PHP :-)

RE: how do I successfully use PDO with my ODBC for MSSQL? - cyberion1985 - Jun-26-2018

Smile

oops, sorry !

Then python! Cool

The above screenshots should still be valid. I actually started with python and then moved to PHP. The below basic code should just simply see if it can make a connection. So, THANK YOU as I was able to use python to get it working !!! And who knows, this might help in fixing the PHP too !

import pyodbc

con = ""

try:
    con = pyodbc.connect(
        r'DRIVER={ODBC Driver 11 for SQL Server};'
        r'SERVER=SERVER\SRVINSTANCE;'
        r'DATABASE=dbname;'
        r'UID=username;'
        r'PWD=password;'
        )
except pyodbc.Error, err:
    print err

if con:
       
    cur = con.cursor()
    
    con.close()
    
    print "yes! it works!"

RE: how do I successfully use PDO with my ODBC for MSSQL? - Maverick494 - Jun-26-2018

I personally use something like the following:

import pymysql

conn =  pymysql.connect(host=dbhost, port=3306,user=username,password=dbpassword,db='database',autocommit=True,local_infile=1)
cursor = conn.cursor()
cursor.execute(Query)
conn.close()

You should be able to do something similar with MSSQL using a DSN

conn = pyodbc.connect(r'DSN=mynewdsn;UID=user;PWD=password')

yea; here is a good example

https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-2017