Data source name not found, and no default driver specified

18
votes

I need help fixing an error: SQL state IM014 in SQLConnect and SQL state IM002 in SQLConnect.

I run the same script, one on webserver/remote/ and the other one from the local Machine trying to access the same database but i get different error message.

When i run it from web server i get

SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQL

where as when i run it on local machine i get

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

I used the following code in php script to connect to the Local database

$odbc['dsn'] = "SageLine50v19";
$odbc['user'] = "Peac";
$odbc['pass'] = "XXXX";
$mysql['host'] = "localhost";
$mysql['user'] = "root";
$mysql['pass'] = "";
$mysql['dbname'] = "sagetest";
$mysql['idfield'] = "id";


// Step 1: Connect to the source ODBC database
if ($debug) echo "Connect to " . $odbc['dsn'] . ' as ' . $odbc['user'] . "\n";
$conn = odbc_connect($odbc['dsn'], $odbc['user'], $odbc['pass']);
if (!$conn) {
die("Error connecting to the ODBC database: " . odbc_errormsg());
}

// loop through each table 
$allTables = odbc_tables($conn);
$tablesArray = array();
while (odbc_fetch_row($allTables)) {
 if (odbc_result($allTables, "TABLE_TYPE") == "TABLE") {
    $tablesArray[] = odbc_result($allTables, "TABLE_NAME");
 }
}
 //print_r($tablesArray);      // to list all tables

My ODBC.ini looks like below

[ODBC 32 bit Data Sources]
manager=Sage Line 50 v16 (32 bit)
t=SQL Server Native Client 10.0 (32 bit)
s1=Pervasive ODBC Client Interface (32 bit)
SageLine50v19=Pervasive ODBC Client Interface (32 bit)
[manager]
Driver32=C:\Windows\SysWOW64\S16DBC32.dll
[t]
Driver32=C:\Windows\system32\sqlncli10.dll
[s1]
Driver32=C:\Program Files (x86)\Pervasive Software\PSQL\bin\w3odbcci.dll
[SageLine50v19]
Driver32=C:\Program Files (x86)\Pervasive Software\PSQL\bin\w3odbcci.dll
4
phpsqlsql-serverodbc
The DSN architecture mismatch is because the bitness of the application and the bitness of the driver differ, you're trying to use a 32-bit driver with a 64-bit application or vice versa. You'll need to match them for everything to work properly. On your local machine, if you're using a 32-bit driver switch to a 64-bit one and try again. On the server, do you have the driver and DSN installed? – KylePorter
@KylePorter thanks for reply i am not sure about DSN but i know my webhost added "odbc" extension. i will check with them about the DSN – Kinfe
It is not enough to have only the ODBC driver installed, you must also create a DSN on the server to connect to, the same as on your local computer. If you are deploying the same code, then you must ensure that the DSN is set up identically to the one you have locally. – KylePorter
Is there any reason to not use the standard mysqli or pdo_mysql instead? – Kevin Nagurski
@KevinNagurski because ODBC is not just for MySQL... Also for what its worth, KylePorter is correct, i ended up needing to write an interface for the 32 -> 64, which it turns out is really not pleasant. – Pogrindis

4 Answers

3
votes

Very simple!

On server:

SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQL

Delete the USER data source name and define it as a SYSTEM data source name. Go to -> Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC): - User DSN : delete the specified User DSN - System DSN : create a new System DSN

Else try to install same 32bits or 64bits version as locally installed.

Locally:

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

32bit/64 bit conflict - either you install a 32bit driver version or you use/configure the 32bit under C:\Windows\SysWoW64\

Try to install same 32bits or 64bits version as locally installed.

1
votes

There are a few things that can cause this. First, the DSN needs to be declared on both machines, on the remote machine it needs to be a WAN or LAN address depending on where it lives in the network. Second you need to make sure you have the right ODBC driver, there are 32 bit drivers and 64 bit drivers. MySQL connector ships with both.

32 bit ODBC: %systemdrive%\Windows\SysWoW64\odbcad32.exe
64 bit ODBC: %systemdrive%\Windows\system32\odbcad32.exe

I would try removing the 64 bit driver, adding the 32 bit driver and see how that goes. Also, make sure you test your ODBC to make sure you have connection. If you after than then time to check the coding.

1
votes

First open the administrative tools option. Check your operating system. If you are using 64-bit then select driver of 64-bit. Or it is 32-bit then select 32-bit.

Please do not make any user DSN. Select system DSN.

e.g. I made System DSN which name is "empcon1" then in PHP code you should write like this:

$dbNewName = "empcon1"; <br/>
$dbUserName = "yash"; <br/>
$dbPassword = "yash";<br/>
$conn=odbc_connect($dbNewName,$dbUserName,$dbPassword);

thats all.. You will get connected.

0
votes

In my case, I was trying to connect a PHP script to a MS Access 2003 database (.mdb file) using the code I found somewhere else:

$dbName = "C:\\DB\\myAccessFile.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; charset=UTF-8; DBQ=$dbName; Uid=myUserName; Pwd=secret;");

// use the connection here
$result = $db->query('SELECT * FROM tableName');
$counter = 1;
while ($row = $result->fetch()) {
    echo $row["firstName"] . " " . $row['lastName'];
    echo '<br/>';
    $counter = $counter + 1;
    if ($counter == 50) {
       break;
    }
}

// and now we're done; close it
$sth = null;
$dbh = null;

Operating system: Windows 10 Pro.

WAMP server version: 3.1.9

Just make sure the driver specified in the code matches exactly the driver name in the ODBC dialog box, as shown in this image:

enter image description here