EzDevInfo.com

Common ways to connect to odbc from python on windows?

What library should I use to connect to odbc from python on windows? Is there a good alternative for pywin32 when it comes to odbc?

I'm looking for something well-documented, robust, actively maintained, etc. pyodbc looks good -- are there any others?

How do I create a DSN for ODBC in Linux?

I am digging around in a Linux application that supposedly uses DSNs to connet to SQL Server. The connection stopped working and I can't find the credentials that are being used (all I know is the DSN's name).

I am familiar with DSNs in Windows, but how are they created and where are they stored in Linux?

What are the functional differences between iODBC and unixODBC?

There are two major Open Source platform independent implementation of the ODBC. It is iODBC and unixODBC.

Considering Unix as ODBC user platform and feature-wise, what are the real practical differences between these two implementations?

PostgreSQL ODBC driver not showing up in Control Panel (Data Sources)

I installed psqlODBC and when I go to Control Panel -> Data Sources (ODBC) I don't see the Postgres driver installed.

I rebooted, still nothing.

I then noticed that if I launch this control panel applet from the file c:\WINDOWS\system32\odbccp32.cpl the drivers suddenly appear!

This happens on an XP 64 bit machine.

I can't really explain why this is, or what the implications of this are.

When launching from the Control Panel, rundll32's command line is:

"rundll32.exe" shell32.dll,Control_RunDLL "c:\WINDOWS\system32\odbccp32.cpl",

(this data is from Process Explorer)

When launching from odbccp32.cpl the command line is:

"C:\WINDOWS\system32\rundll32.exe" C:\WINDOWS\system32\shell32.dll,Control_RunDLL "C:\WINDOWS\system32\ODBCCP32.CPL",@0

Any ideas?

How do I connect to my 64-bit SQL Server with ODBC?

I recently installed SQL Server 2008 Express on my Windows 7 Ultimate x64 home machine. I also have IIS 7.5 with PHP 5.3, and I was trying to connect to SQL via ADODB, but kept getting this error:

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

After doing a small amount of digging on the internet, I think this is because the SQL Server ODBC driver is meant for 32-bit operating systems, and mine's 64. First of all, am I correct? Is this the reason I'm running into trouble? Secondly, if so, how do I fix this? Are there any updated ODBC drivers that work with 64-bit operating systems? I looked but was unable to find any...

Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present

I want to do a hand install of the MS Access 64 bit odbc drivers. Uninstalling 32 bit Office and installing 64 bit Office is not an option due to the add-ins that our company uses.

I downloaded the AccessDatabaseEngine_x64.exe and using WinRar and Universal Extractor have managed to unpack all the files into a temp directory. I believe I have all of the files necessary but am a bit unsure where to go from here and would appreciate a little guidance.

Which DLLs need to be registered to make the MS Access ODBC drivers available in the 64 bit ODBC administrator?

Is there a list of registry entries that I will need to make for it to be available?

Has anyone else dealt with this in a reasonable manner?

Thank you in advance!

Setting up PostgreSQL ODBC on Windows

I have the latest 64 bit version of PostgreSQL. I am running Win 7 64 bit. I had installed the ODBC driver (via the initial installer) when I installed PG, and upgraded it to the latest version from http://www.postgresql.org/ftp/odbc/versions/msi/.

However, when I go to the ODBC control panel and try to add a User DSN, PostgreSQL isn't listed as a server.

I was following the instructions given here, but I noted the links are dated.

ODBC problems in SQL 2000 --> 2005 Upgrade

This wiki post outlines both a problem and a solution. I wanted to post this for others that may be having similar problems, as I couldn't find anything specifically to solve this problem elsewhere.

We recently upgraded our SQL Server 2000 database to SQL Server 2005. One of the databases on the server is a back-end to a MS Access database. The MS Access database uses pass-through queries, via DSN-less ODBC to connect to SQL Server.

An example of the DSN-less connection string is shown below:

ODBC; DRIVER=SQL Server;SERVER=servername;APP=Microsoft® Access (Pass Through
    Query);DATABASE=databasename;Network=DBMSSOCN;ConnectionTimeout=20;
    Trusted_Connection=Yes

After the upgrade, we found that users were unable to run the pass-through queries, and were getting the following error displayed :

ODBC -- connection to 'SQL Server ' failed

This initially appeared to be a permission issue, as elevating the priveledges of the SQL server logins to sysadmin server role alleviated the problem (but obviously this isn't a great solution).

After taking the logins back out of the sysadmin role we found that when connecting to SQL Server via Management Studio, the login could execute the stored procedures. The very same login could not from within MS Access. This pointed to something MS Access was doing while trying to execute the stored procedures - rather than a permission issue.

We ran a trace on the server using Profiler, and this showed up MS Access trying to execute the following command prior to stored proc execution:

DBCC TRACEON(208)

It appeared to fail at this command, prior to stored procedure execution. Research on the web showed that DBCC TRACEON(208) is equivalent to using 'SET QUOTED IDENTIFIERS ON' command, and that in SQL 2005 priveledges to run this DBCC command had been revoked.

After further research, we found references to MS Query having a similar problem, and that the APP component of the connection string should be changed from 'MS Query' to something else.

On a hunch, we changed our APP component of the ODBC connection string, and MS Access no longer tried executing DBCC TRACEON(208) prior to stored procedure execution.

After further testing, we tracked the problem down to the 'copyright' symbol included in the APP component :

APP=Microsoft® Access (Pass Through Query)

By removing the copyright symbol, all was well with the connection and the application worked as it had previously done on SQL 2000.

Hope this helps out anyone else having a similar problem.

Fetching UTF-8 text from MySQL in R returns "????"

I'm stuck trying to fetch UTF-8 text in a MySQL database from R. I'm running R on OS X (tried both via the GUI and command line), where the default locale is en_US.UTF-8, and no matter what I try, the query result shows "?" for all non-ASCII characters.

I've tried setting options(encoding='UTF-8'), DBMSencoding='UTF-8' when connecting via ODBC, setting Encoding(res$str) <- 'UTF-8' after fetching the results, as well as 'utf8' variants of each of those, all to no avail. Running the query from the command line mysql client shows the results correctly.

I'm totally stumped. Any ideas why it's not working, or other things I should try?

Here's a fairly minimal test case:

$ mysql -u root
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE test (str VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test (str) VALUES ('こんにちは');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-----------------+
| str             |
+-----------------+
| こんにちは      |
+-----------------+
1 row in set (0.00 sec)

Querying the table in R using both RODBC and RMySQL shows "?????" for the str column:

> con <- odbcDriverConnect('DRIVER=mysql;user=root', DBMSencoding='UTF-8')
> sqlQuery(con, 'SELECT * FROM rtest.test')
    str
1 ?????
> library(RMySQL)
Loading required package: DBI
> con <- dbConnect(MySQL(), user='root')
> dbGetQuery(con, 'SELECT * FROM rtest.test')
    str
1 ?????

For completeness, here's my sessionInfo:

> sessionInfo()
R version 2.15.1 (2012-06-22)
Platform: x86_64-apple-darwin9.8.0/x86_64 (64-bit)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RMySQL_0.9-3 DBI_0.2-5    RODBC_1.3-6 

What is a privileged instruction?

I have added some code which compiles cleanly and have just received this Windows error:

---------------------------
(MonTel Administrator) 2.12.7: MtAdmin.exe - Application Error
---------------------------
The exception Privileged instruction.

 (0xc0000096) occurred in the application at location 0x00486752.

I am about to go on a bug hunt, and I am expecting it to be something silly that I have done which just happens to produce this message. The code compiles cleanly with no errors or warnings. The size of the EXE file has grown to 1,454,132 bytes and includes links to ODCS.lib, but it is otherwise pure C to the Win32 API, with DEBUG on (running on a P4 on Windows 2000).

Connect PHP to IBM i (AS/400)

I've got an upcoming project wherein I will need to connect our website (PHP5/Apache 1.3/OpenBSD 4.1) to our back-end system running on a iSeries with OS400 V5R3 so that I can access some tables stored there. I've done some checking around but am running into some roadblocks.

From what I've seen the DB2 extensions and DB2 software from IBM only run under Linux. I've tried compiling the extensions with all the software from IBM and even tried their precompiled ibm_db2 extension with no luck. IBM only supports Linux so I turned on the Linux emulation in the kernel but that didn't seem to help anything.

If anyone has run across getting everything to run natively under OpenBSD that would be great, but what I think I may have to do is setting up a second server running CentOS with DB2 installed (most likely via ZendCore for IBM since it seems to do all this for me) and the driver so that I can set up a small transaction server that I can post against and get a JSON representation of the DB2 data that I need.

Does the second option seem overkill or does anyone else have any better ideas?

How to search for rows containing a substring?

If I store an HTML TEXTAREA in my ODBC database each time the user submits a form, what's the SELECT statement to retrieve 1) all rows which contain a given sub-string 2) all rows which don't (and is the search case sensitive?)

Edit: if LIKE "%SUBSTRING%" is going to be slow, would it be better to get everything & sort it out in PHP?

Need a row count after SELECT statement: what's the optimal SQL approach?

I'm trying to select a column from a single table (no joins) and I need the count of the number of rows, ideally before I begin retrieving the rows. I have come to two approaches that provide the information I need.

Approach 1:

SELECT COUNT( my_table.my_col ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

Then

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

Or Approach 2

SELECT my_table.my_col, ( SELECT COUNT ( my_table.my_col )
                            FROM my_table
                           WHERE my_table.foo = 'bar' ) AS row_count
  FROM my_table
 WHERE my_table.foo = 'bar'

I am doing this because my SQL driver (SQL Native Client 9.0) does not allow me to use SQLRowCount on a SELECT statement but I need to know the number of rows in my result in order to allocate an array before assigning information to it. The use of a dynamically allocated container is, unfortunately, not an option in this area of my program.

I am concerned that the following scenario might occur:

• SELECT for count occurs
• Another instruction occurs, adding or removing a row
• SELECT for data occurs and suddenly the array is the wrong size.
  -In the worse case, this will attempt to write data beyond the arrays limits and crash my program.

Does Approach 2 prohibit this issue?

Also, Will one of the two approaches be faster? If so, which?

Finally, is there a better approach that I should consider (perhaps a way to instruct the driver to return the number of rows in a SELECT result using SQLRowCount?)

For those that asked, I am using Native C++ with the aforementioned SQL driver (provided by Microsoft.)

PHP & SQL Server - field names truncated

Here is the relevant code:

function connect(){
    // DB credentials and info defined here....
    $connection = odbc_connect("DRIVER={SQL Server Native Client 11.0}; Server=$server; Database=$db;", $loginname, $loginpass);
    return $connection;
}

function odbc_fetch_results($stmt, &$results) {
    $numrows = odbc_num_rows($stmt);
    $row = odbc_fetch_array($stmt);
    print_r($row); // Prints: Array ( [MEASUREMENT_UNI] => kg)
    if($row){
         $results = array ($row);
         while( $row = odbc_fetch_array($stmt)){
            array_push($results, $row);
         }
    }
    return $numrows;
}

$sql = "select * from measurements where ID=$id";
$stmt = executeSQL($conn,$sql);
$nrows = odbc_fetch_results($stmt, $results);
odbc_free_result($stmt);
print_r($result[0]); // Prints: Array ( [0] => Array ( [MEASUREMENT_UNI] => kg) ) 

The result should contain a columnn called MEASUREMENT_UNIT which (when I do a print_r I can verify) is truncated to MEASUREMENT_UNI which is only 15 characters. The last letter T is cut off.

I also tried a query with a different table and a different column on the SQL Server database as a test to make sure it wasn't any strange setup with the particular table or column that I'm working with. I verified the same thing occurs with a different table/column: column names are truncated to 15 characters max when I run a select query as above.

I have also tried a select which specifies the field name like select MEASUREMENT_UNIT from from measurements where ID=$id instead of select * but that doesn't solve the problem either.

I've seen other similar posts here about this but they all seem to indicate that I should be able to get at least 30 characters, not the 15 character limit that I'm seeing.

Why is the column name being truncated to 15 characters?

Edit: Connecting to a MySQL server database did not seem to result in the same problem. DB column names from the MySQL tables were NOT truncated which leads me to believe that this is not a problem with the ODBC plugin.

$connection = odbc_connect("DRIVER={MySQL};Server=$server; Database=$db;", $loginname, $loginpass);
$sql = "select * from measurements where ID=$id";
$stmt = executeSQL($conn,$sql);
$nrows = odbc_fetch_results($stmt, $results);
odbc_free_result($stmt);
print_r($result[0]); // Prints CORRECTLY: Array ( [0] => Array ( [MEASUREMENT_UNIT] => kg) )

Note that both of the above code sections were tested in the same file on the same server with the same PHP + ODBC installation.

PDO returning empty property name

I'm having a strange issue with pdo_odbc and PDO::FETCH_OBJ (and PDO::FETCH_CLASS) that results in the following error message:

PHP Fatal error:  Cannot access empty property

Here's the code:

$dbh = new PDO("odbc:FOO");

$sth = $dbh->query("
  SELECT rolename
  FROM dbc.allrolerights
  WHERE databasename = 'BAR'
");

$result = $sth->fetch(PDO::FETCH_OBJ);

The FOO DSN, for reference, is a Teradata datasource using the tdata.so driver, provided by the tdodbc package.

I believe this is happening because the field name (as returned from the ODBC query) is blank when PDO calls zend_API.h:object_init_ex() to instantiate the stdClass object. If I switch to PDO::FETCH_LAZY and var_dump() the row, I get the following:

object(PDORow)#3 (2) {
  ["queryString"]=>
  string(95) "
  SELECT rolename
  FROM dbc.allrolerights
  WHERE databasename = 'BAR'
"
  [""]=>
  string(30) "FNAR                          "
}

Which seems to back it up. I've tried several different PDO attribute combinations and bunch of different angles to work around the problem. One solution is to fetch an associative array and pass it to the class constructor. However, this doesn't work for certain frameworks and ORMs that are using PDO::FETCH_CLASS directly, behind the scenes.

I want to add that other fetch methods seem to do the right thing, for example, PDO::FETCH_NAMED:

array(1) {
  ["RoleName"]=>
  string(30) "FNAR                          "
}

I'm looking for something I can put in the PDO dbh or sth definition, or in the odbc.ini or odbcinst.ini for the datasource or driver, to resolve this issue. Thank you in advance.

Update: odbc_fetch_object() (i.e. not PDO) works great with the same exact everything. Just wanted to mention it. There clearly doesn't seem to be any serious issues with PHP, unixODBC, or the ODBC driver. It's something in the PDO code. Time to open a bug report... opened

$dbh = odbc_connect("FOO", NULL, NULL)
  or die(odbc_error_msg());

$sth = odbc_exec($dbh, "
  SELECT rolename
  FROM dbc.allrolerights
  WHERE databasename = 'BAR'
");

$result = odbc_fetch_object($sth);
var_dump($result);

And the output:

object(stdClass)#1 (1) {
  ["RoleName"]=>
  string(30) "FNAR                          "
}

Update 2: The situation continues to grow more and more bizarre. I can do a PDO::FETCH_LAZY and see the blank column name as seen in the var_dump() above, but if I try to access the property by name (e.g. $result->RoleName), it works! What are these fetch methods doing so differently that some of them can sometimes access the field names, and others cannot?

Side-by-side comparisons of ODBC traces ("working" cf. "not working") shows no differences (other than different pointer addresses). PDO::FETCH_BOUND works with both numbered and named columns. PDO::FETCH_INTO an object with a RoleName property does not.