Extension talk:OdbcDatabase/Flow

Previous page history was archived for backup purposes at Extension talk:OdbcDatabase/LQT Archive 1 on 2015-07-10.

I am trying to access a mssql database. I have followed the steps for setting up the OdbcDatabase, and i can successfully connect to the database with isql

isql -v mysqlserverdb username password

However, I cannot get it to work with the ExternalData extension settings

in LocalSettings.php:

require_once "$IP/extensions/OdbcDatabase/OdbcDatabase.php";

$edgDBServerType['mydb'] = "odbc";

$edgDBServer['mydb'] = "mysqlserverdb";

$edgDBName['mydb'] = "Database";

$edgDBUser['mydb'] = "username";

$edgDBPass['mydb'] = "password";

$edgDBTablePrefix['mydb'] = "dbo";

in Wiki Page:

{{#get_db_data:

db=mydb

|from=Client

|data=Name=Name }}

When I navigate to the page, I get an error: Cannot access the database

Seems like I am missing something here, but not sure if the error is on the OdbcDatabase setup side, or the ExternalData setup

Thanks!

Perhaps similar to the issues with specifying limit I also bumped into an issue when trying to return (with ED) the result of a COUNT.

Function: EDUtils::searchDB
Error: 37000 [unixODBC][FreeTDS][SQL Server]The cursor was not declared. (myodbcname)

This querying an MSSQL server through odbc.

A slightly different error than when trying to specify limit (top=)

Function: EDUtils::searchDB
Error: 37000 [unixODBC][FreeTDS][SQL Server]Incorrect syntax near 'LIMIT'. (myodbcname)

The same queries against a mysql server with similar tables works as expected. Discussion over at ED suggested it's likely this connector. Any way to work around this?

Thanks!

Great question. I am unsure of the answer as I (*ahem*) don't use my own extension any more on our wiki. I did some Googling, but did not turn up much yet.

Ah, ok. Curious, did you find an alternative solution? or did the need go away? The whole php to MSSQL under linux has really been a bit troublesome the last few years. The one incentive I know of to move to windows.

We solved the problem a different way. I have a .NET application with SQL Server DB that has information I wanted to display in the wiki. At first I created pages for each "thing" in the wiki, then did a DB query using OdbcDatasource back to SQL Server to get the details for that "thing" and display them on the wiki page. This meant, among other things, that page loads required a DB call every time, AND the info returned by the query was not part of the wiki page so was not searchable in the wiki.

We chose to reverse the process and have the .NET app, through a service, create, update, or delete wiki pages for each "thing" automatically as the data in the .NET app changed. We now synchronize several thousand wiki pages from external sources using this method. The external updaters only manage a portion of the page (essentially the main template on the page), so users can edit the rest of the page with additional supporting info--which was the whole reason to do this in the first place. User editing the page source see a comment-delimited block warning them not to edit the contents between the comments because it will eventually be overwritten.

Now all the external info is embedded in the wiki page and is fully searchable in the wiki.

It has worked wonderfully.

VERY interesting. Since my information is basically numbers/dates, leaving it on the server is better for this particular application, but just the idea that you are leveraging automatic article edits (which I've only begun to explore) to your wiki to give you both access to controlled information AND the ability for your users to contribute to it in one spot gives me so many ideas!! Thanks!

To add to your mental soup, we went further by creating a special template in the wiki that represented the pattern for retrieving data from the .NET entity model. This allows me to write the sync engine in .NET once and control what data gets retrieved using a wiki page.

So for a given entity in my DB (say a widget), I have two templates. The first is Template:Widget that gets placed on every page that represents a widget, and which has parameters for each piece of information I want to get from my DB.

The second is "Template:Widget (DB)", which contains what looks like a template (so it doesn't mess up the wiki), but the parameter references are really references to fields on the entity itself (e,g. { { {Description}}}, { { {CreatedTime}}}, { { {Aliases.Name}}}). The engine knows how to use reflection in C# to get the values of those fields and stuff them into the text of the wiki page as values of the template parameters, handling lists and nested fields. It works out very well. If I want to retrieve different data from the DB, I just change the DB template to pull other fields and stuff them into the right places in the page's template.

The DB template also has special control fields that tell the engine how to locate the correct wiki page given a DB identifier. The engine performs a Semantic MediaWiki ASK query to find all pages in list of categories with a list of property/value pairs. If no pages come back, the page is created. If one page comes back it is updated. If more than one page comes back it is an error.

Hi Roger,

Do you have a version of this extension that would work with mediawiki 1.15?

Ed

This post was posted by Quick73, but signed as Equick.

Ed,

Sorry to not reply until over a year later. I am not sure why I didn't get notified. I have only one MediaWiki install, so I have not attempted to test with an older version.

That's ok. It was a good incentive to upgrade ;)

This post was posted by Quick73, but signed as Equick.

This extension works great for retrieving data from our SQL2012 databases. However, when I use a legit query that happens to have an empty result set, the interface between ExternalData and this ODBC Extension fails and a php stack trace is all I see. Am I supposed to first retrieve a count of rows before proceeding to actually retrieve records? That doesn't sound right. Please advise.

I'll answer my own question I guess, the patch listed below avoids complaining about there being no row to retrieve when 0 zero rows were returned by the query:

--- OdbcDatabase.body.php.original	2014-12-01 18:14:08.000000000 -0500
+++ OdbcDatabase.body.php	2015-06-04 23:04:34.127805703 -0400
@@ -128,16 +128,18 @@
 		}
 
 		$array = null;
-		$row = odbc_fetch_row( $res );
-		if ( $row ) {
-			$this->mRowNum++;
-			$nCols = odbc_num_fields( $res );
-			for ( $i = 0; $i < $nCols; $i++ ) {
-				$array[$i] = odbc_result( $res, $i+1 );
-			}
-		} else if ( $this->mRowNum <= $this->mAffectedRows ) {
-			if ( $this->lastErrno() ) {
-				throw new DBUnexpectedError( $this, wfMessage( 'odbcdatabase-fetch-row-error', $this->lastErrno(), htmlspecialchars( $this->lastErro
+		if ( $this->mAffectedRows > 0 ) {
+			$row = odbc_fetch_row( $res );
+			if ( $row ) {
+				$this->mRowNum++;
+				$nCols = odbc_num_fields( $res );
+				for ( $i = 0; $i < $nCols; $i++ ) {
+					$array[$i] = odbc_result( $res, $i+1 );
+				}
+			} else if ( $this->mRowNum <= $this->mAffectedRows ) {
+				if ( $this->lastErrno() ) {
+					throw new DBUnexpectedError( $this, wfMessage( 'odbcdatabase-fetch-row-error', $this->lastErrno(), htmlspecialchars( $this->
+				}
 			}
 		}
 		return $array;

Actually, this patch is better for two reasons:

• the exception logic only triggers on failures
• it handles the single row case!

--- OdbcDatabase.body.php.original	2014-12-01 18:14:08.000000000 -0500
+++ OdbcDatabase.body.php	2015-06-04 23:37:04.084804367 -0400
@@ -136,7 +136,7 @@
 				$array[$i] = odbc_result( $res, $i+1 );
 			}
 		} else if ( $this->mRowNum <= $this->mAffectedRows ) {
-			if ( $this->lastErrno() ) {
+			if ( ($this->mAffectedRows > 0) && $this->lastErrno() ) {
 				throw new DBUnexpectedError( $this, wfMessage( 'odbcdatabase-fetch-row-error', $this->lastErrno(), htmlspecialchars( $this->lastError() ) ) );
 			}
 		}

I am Using MediaWiki 1.24

I Downloaded ExternalData and OdbcDatabase extensions for for getting sql server am adding these lines

require_once "$IP/extensions/OdbcDatabase/OdbcDatabase.php";

include_once "$IP/extensions/ExternalData/ExternalData.php";

$edgDBServerType ['mydb'] = "odbc";

$edgDBServer ['mydb'] = "my_dsn";

$edgDBName ['mydb'] = "UNUSED"; 

$edgDBUser ['mydb'] = "sa";

$edgDBPass ['mydb'] = "sa@123";

$edgDBTablePrefix ['mydb'] = "dbo";

and i created my_dsn odbc in my windows wiki server in wiki page

{{#get_db_data: db=mydb |from=Testtable |data=test1=test1,test2=test2}}

now submit the page i got a below error message on top of the page

Undefined index: odbc in D:\xampp\htdocs\SNWiki\includes\db\Database.php on line 915

In Database.php the line 915 is below bold line

if ( class_exists( $class ) && is_subclass_of( $class, 'DatabaseBase' ) ) {

$params = array(

'host' => isset( $p['host'] ) ? $p['host'] : false,

'user' => isset( $p['user'] ) ? $p['user'] : false,

'password' => isset( $p['password'] ) ? $p['password'] : false,

'dbname' => isset( $p['dbname'] ) ? $p['dbname'] : false,

'flags' => isset( $p['flags'] ) ? $p['flags'] : 0,

'tablePrefix' => isset( $p['tablePrefix'] ) ? $p['tablePrefix'] : 'get from global',

'schema' => isset( $p['schema'] ) ? $p['schema'] : $defaultSchemas[$dbType],

'foreign' => isset( $p['foreign'] ) ? $p['foreign'] : false

);

If i type wrong table name in wiki page it showing Below Errors

Notice: Undefined index: odbc in D:\xampp\htdocs\SNWiki\includes\db\Database.php on line 915

Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'Testtable1'., SQL state S0002 in SQLExecDirect in D:\xampp\htdocs\SNWiki\extensions\OdbcDatabase\OdbcDatabase.body.php on line 26'

so i think connectivity is not a problem.

Kindly Help me

Ragu, great question. I have not yet tested on 1.24.x as we are stuck on 1.21. There may be a change in Database.php that adds additional logic around the $defaultSchemas array. I am sure the value 'odbc' does not appear in the $defaultSchemas array. Since OdbcDatabase builds on the ExternalData extension, there may be a dependency there I have not yet tested where we need to pass $p['schema'] in somehow. ExternalData may not yet support a config variable (e.g. $edgDBSchema) to support specifying the schema.

In the short term, you might be able to find where $defaultSchemas is defined and add a reasonable value for 'odbc'. A more elegant solution will require more time.

I am in the process of upgrading our MediaWiki to the current release (finally!), so may be able to update the OdbcDatabase extension in the near future.

Thanks for your reply. now i am adding element in defaultschema for odbc in database.php

                       $defaultSchemas = array(
			'mysql' => null,
			'postgres' => null,
			'sqlite' => null,
			'oracle' => null,
			'mssql' => 'get from global',
                        'odbc' => null 

now data fetching is fine.

--Ragu

I found that using the limit parameter in #get_db_data: produce a SQL syntax error.

The builded query is something like SELECT a,b,c FROM t LIMIT x. This is a SQL syntax error in MS SQL Server; it should be SELECT TOP x a,b,c FROM t.

Roberto,

Good catch, I am sure there is a place where we can update the translation to be correct for SQL Server. Interestingly, since OdbcDatabase is supposed to work using the ODBC driver for ANY database, not just SQL Server, I am not sure that is a problem with the OdbcDatabase extenstion, but with the ExternalData extension.

If, however, we assume that OdbcDatabase would really never be used except when accessing SQL Server from Linux, then it is likely safe to somehow coerce the combination of OdbcDatabase and ExternalData to use SQL Server=specific SQL constructs.

A good thing to test.

There's a couple of typos in the current version of the i18n/*.json files, which was causing my setup to fail whenever a query returned no rows:

i18n/en.json Line 12: replace "odbcdatacase-fetch-object-error" with "odbcdatabase-fetch-object-error" Line 13: replace "odbcdatacase-fetch-object-error" with "odbcdatabase-fetch-row-error"

i18n/qqq.json Same on lines 11-12.

Pending gerrit:129877

YDone with gerrit:129877

I got blank pages using ExternalData and OdbcDatabase with Mediawiki 1.22.

I found that the problem was the getSoftwareLink() method defined in OdbcDatabase.body.php; so I removed the static keyword and the extension began to work.

I think the problem come from this: https://gerrit.wikimedia.org/r/#/c/63824/

Hope this helps.

Good find - I just added a note about this to the documentation.