Extension talk:External Data/Archive 2017 to 2018

Can't use array of data as array of strings
I use #get_db_data for fetching an array of strings T12U from a database. It works just fine since I'm able to show the data using  , e.g.: M,M,D,D,H,Y,M,Y,. I prepared a template Time unit for conversion these data to a more readable form: However, the following code   shows nothing. Though the template itself works fine, e.g.   produces min. What's wrong? --176.104.0.186 07:25, 16 January 2013 (UTC)


 * The issue is just that #for_external_table can't handle templates very well; it's something I need to look into. Yaron Koren (talk) 16:23, 16 January 2013 (UTC)

Stricter Parameter in New Version causes much work
I didn't find any hints in the release notes (maybe I didn't look proberly`?)

empty MySQL-access password doesn't work anymore
In older Version was it possible to access MySQL with empty user password (what was OK for a complete internal WIKI in an environment where all participants have access on script level) Now I had to add a password in MySQL and LocalSettings.php.

Changes in get_db_data-syntax
following once worked

Now it this has to be changed in

Otherwise you'd get

"Unknown column 'NNVN' in 'field list'"

with

"SELECT VN_NN_Name,NN_VN=NN_VN_Name,Company,Phone FROM `PersonAdresse` WHERE `VN_NN_Name` LIKE 'G%'

Means, "VNNN=" vanished and "NNVN=" is misinterpreted, therefore: Second Topic seams to reopen "Issue with parameters that contain '_'" --Hk sup (talk) 09:46, 23 January 2013 (UTC)
 * no space in combination with "=" and ","
 * no internal names with underscore "_"
 * I'll have to change a lot :-(


 * Hi - I can't think of any change to the External Data code that would have caused either issue. Perhaps the real cause in both cases was an upgrade of MediaWiki itself? Yaron Koren (talk) 13:25, 23 January 2013 (UTC)


 * Simply true, a new MediaWiki needed new External Data Extension. But who has to be addressed for this problem? I updated to the newest version of mediawiki and semantic media bundle and expected less massive change in behaviour. Was my old way to use no mysql password and get_db_data-syntax that far away from the intended standard?
 * --Hk sup (talk) 11:00, 24 January 2013 (UTC)


 * Well, the values of "data=" are meant to be on a single line, yes - although support for putting them on separate lines could probably easily be added to the code. As for underscores in the values, "A_B" and "A B" are two different things (unless it's part of a wiki page name), so I'm not surprised that it doesn't work to use the wrong one. And connecting to MySQL with an empty password - unfortunately I know nothing about that; the External Data code just calls MediaWiki's own database-connection code. You might ask on the mediawiki-l mailing list, or #mediawiki IRC channel. Yaron Koren (talk) 13:59, 24 January 2013 (UTC)


 * Thanks for the clarification --Hk sup (talk) 14:41, 24 January 2013 (UTC)
 * Hello, I wasn't clear from this discussion whether or not underscores can be used in internal names. However, what I found is that the following works for all variables:

Whereas the following only works with the first data variable, par_12 (the difference being that the internal variables now use underscores

variable names breaks queries
I'm not sure in what version things broke, however I just upgraded to 1.5 (from 1.3.3 I believe) and all my previous db queries stopped working. This is because I prefix and postfix my variable names so that I don't get collisions.

In 1.5 if in your data= section you have data=username=user_name,realname=user_real_name,email=user_email everything is peachy. data=username_postfix=user_name,realname=user_real_name,email=user_email is also cool with an underscore on the first variable data=username-postfix=user_name,realname=user_real_name,email=user_email or a dash on the first variable data=username_postfix=user_name,realname_postfix=user_real_name,email=user_email data=username-postfix=user_name,realname-postfix=user_real_name,email=user_email Doing it on the second or 3rd variable however, breaks the query. You'll get an error for "EDUtils:SearchDB" db error "1056: Unknown column 'realname' in 'field list' (192.168.1.1)".

What is also interesting is that in the output of the SQL query, the first field 'user_name' will not have a variable assigned to it, only the 2nd+ do.

No changes were made to the wiki itself, all these queries and templates used to work, it looks like the error is in the parsing code for variables, which must have changed.

--Finlay Beaton (talk) 19:26, 1 February 2013 (UTC)


 * I downgraded External Data to 1.4.3 and the problem still occurs.
 * I then downgraded to 1.2.2 (the only tar I had lying around of pre-git days) and the problem does not occur and everything works as expected. I'll dig around and see if I have 1.3.3 lying around and use that but until it's fixed I can't run >1.4 series.
 * --Finlay Beaton (talk) 19:35, 1 February 2013 (UTC)


 * Yes, I'm aware of that problem - it was just discovered on the SMW mailing list two days ago, by coincidence. It's due to a change that was made in version 1.4.2. I hope to have it fixed soon. Yaron Koren (talk) 19:46, 1 February 2013 (UTC)


 * Hi Yaron. We're using version 1.6.1 and still having problems with multiple parameters in data field. Has this issue been fixed yet?
 * ~Aykut Simsek (talk) 16:21, 5 June 2013 (UTC)

Why get_db_data does not contain GROUP BY?
Why get_db_data does not contain GROUP BY? Are any plans to introduce this option? --178.214.207.74 13:31, 8 February 2013 (UTC)


 * No strong reason - it just didn't seem that helpful, I guess, to get a breakdown of values without actually getting the values themselves. Yaron Koren (talk) 03:33, 13 February 2013 (UTC)

Max column characters
I am having an unknown issue with only one column where the name is 32 characters long. Could the length of the column name be the issue? --Dgennaro 17:34, 8 March 2013 (UTC)


 * Hi - it's possible, although I don't think there's anything in the External Data code that restricts the column name length... Yaron Koren (talk) 20:31, 8 March 2013 (UTC)

Error: Incomplete information for this database ID
I installed the extension without any problem. I created a new database in the same SQL database as MediaWiki is. Placed below information in LocalSettings.php: $edgDBServer['employee-db'] = "localhost"; $edgDBServerType['employee-db'] = "mysql"; $edgDBName['employee-db'] = "test"; $edgDBUser['employee-db'] = "testuser"; $edgDBPass['employee-db'] = "testpassword"; Then I placed following code in one of the wikipages to test (with the syntax before and after): get_db_data: db=employee-db The firstname is the name of one of the columns in the table I try to get data from. The information I recieve is following "Error: Incomplete information for this database ID." I am not sure what the problem might be! Any ideas?
 * from=agenter
 * data=firstname=firstname

Never mind. It was me who was dumb. I placed database settings above the add extension line which caused the problem.

Table not update directly
I use the External Data extension to display a table of users which works just fine. But if I add a new user or delete a user I need to press edit and then save to update the table. How can I get around that problem?


 * You don't need to save the page - you just need to refresh/purge it. You can do that by adding "action=purge" to the URL; or, conversely, you can just wait about 24 hours, for the page cache to expire. Yaron Koren (talk) 21:07, 13 March 2013 (UTC)

I saw some information regarding the cache. Some managed to get a workaround installing a extension MagicNoCache but that didn´t solved it for me. I made a change yesterday at 4:00 PM and today at 08:00 AM it still hadn´t made the change. Is there a possibility to minimize the cache time for that extension only? I changed the $wgParserCacheExpireTime in DefaultSettings.php (found in includes) to 60 seconds as a workaround. Best would be to have the change only for External Data extension if that would have been possible.


 * Hi - for MagicNoCache to work, you need to add the " __NOCACHE__ " tag to that page - did you try that? That's actually the best way to ensure that External Data calls are always up-to-date. Yaron Koren (talk) 14:10, 14 March 2013 (UTC)

I missed that part, thank a lot!

Connecting to an SQL server instance
We have an SQL server instance running on our local network, that can be normally be accessed (for example, using ADO connections) by entering the server name in the format. Using  though, this doesn't work. I've tried setting  to all non-silly combinations of hostname, host IP address and instance name, but haven't had any success - either there's a timeout, or a "No such host is known" error. Is it possible to connect to a specific SQL server instance, and if so what is the syntax? Monomix (talk) 17:16, 15 March 2013 (UTC)


 * Did you follow the special instructions for connecting to SQLServer? Yaron Koren (talk) 22:14, 15 March 2013 (UTC)


 * The problem turned out to be a version issue with the Microsoft drivers for PHP for SQL Server. After updating and going through the special instructions, everything's working nicely. Thanks! Monomix (talk) 12:58, 18 March 2013 (UTC)

Retrieving data from Restful webservice format
Hello,

I would like to use data from ParisOpenData website which some are in Restful webservice format Exemple.

Anyone knows how to manage this format ?

Cheers

Nicolas NALLET (talk) 09:04, 4 April 2013 (UTC)


 * "RESTful" isn't a format, it's an approach; and it's actually the only kind of API/web service that External Data can deal with. What actual format is the data in, do you know? Yaron Koren (talk) 12:26, 4 April 2013 (UTC)

Some are in "esri shapefile" format exemple.

Thanks Nicolas NALLET (talk) 17:44, 4 April 2013 (UTC)


 * Ah - External Data can't handle the Shapefile format, although in theory someone could add support for it to the code. Yaron Koren (talk) 17:57, 4 April 2013 (UTC)

Created new OdbcDatabase extension to help with SQL Server data on Linux
As a result of my own previous request in the 2012 archives of this discussion, I created a way to access MS SQL Server from Linux using ODBC. I had trouble using other methods, including MSSQLBackCompat, so I created a new extension to solve this problem: OdbcDatabase. I have put it to good use already in our wiki. Hopefully it will help someone else.

Roger Cass


 * Cool, that's great! Could this extension potentially be used to access other database systems that MediaWiki doesn't support, like Sybase? Yaron Koren (talk) 16:51, 10 April 2013 (UTC)

Numbered rows?
Is there a way to make autonumbering on the rows? I have found some javascript functions that makes it in static tables but it doesn´t work on a table that is being created using External Data Extension. I would lika to have the possibillity to show numbers for all the rows. MWMP (talk) 12:24, 22 April 2013 (UTC)


 * Are you talking about the #for_external_table function? Yaron Koren (talk) 14:10, 22 April 2013 (UTC)


 * I´m sorry. Yes I mean for the #for_external_table function! MWMP (talk) 11:06, 24 April 2013 (UTC)


 * The NumerAlpha extension may allow you to do that. Yaron Koren (talk) 13:13, 24 April 2013 (UTC)


 * Hi again, thanks for the quick reply! I tried NumerAlpha but it doesn't manage to count allthw rows.

It looks like this (with the a. in the middle only: Nr. Name         Skillset      John Doe     2 DK, 1 SE       Karl Doe     4 DK, 2 SE a.   Moe Doe      8 SE, 3 DK      Joe Doe      5 FI, 7 NO      Soe Doe      2 NO, 6 SE

I don´t know maybe my #for_external_table function is wrong? It looks like this:


 * Well, I would think you should have only one #for_external_table call, and the &lt;ia /&gt; should be within it. See the main documentation for how to generate a table within a #for_external_table call. Yaron Koren (talk) 20:18, 24 April 2013 (UTC)


 * I have tried to follow your examples but it wouldn´t work with table.
 * I am able to print everything as long as I don´t tries to add table.
 * The above example was the only way I managed to print it within a table.
 * Even if I just copy from your example it looks weard.
 * I´m out of ideas how to write it in order to get it to work. MWMP (talk) 08:46, 25 April 2013 (UTC)
 * I found an old question and created the template page (didn´t understood that from the examples). Now i renders a table correct.


 * Allthough when trying to use NumerAlpha within one #for_external_table it just calls every line "a.".

Nr. Name        Skillset a.  John Doe     2 DK, 1 SE  a.   Karl Doe     4 DK, 2 SE a.   Moe Doe      8 SE, 3 DK a.   Joe Doe      5 FI, 7 NO a.   Soe Doe      2 NO, 6 SE

MWMP (talk) 09:41, 25 April 2013 (UTC)


 * That's unfortunate. I can't think of a good solution, then. Perhaps you could add an "ID" field to the database table that holds the row number, and then query on that? Yaron Koren (talk) 18:13, 25 April 2013 (UTC)

LDAP Query not displaying certain attributes
I've got the LDAP query working (for the most part) to Active Directory. However, a few attributes refuse to either be read from LDAP or displayed on the page (not sure where the problem is). Specifically the streetAddress and sAMAccountName attributes. I've confirmed there is data in these attributes (obvious for sAMAccountName). Other data such as name, title, etc works perfectly.

Any suggestions?


 * Unfortunately, I know very little about the LDAP code - that part was written by David Macdonald, who I don't think is involved in MediaWiki development any more. My only guess is that it might be an uppercasing/lowercasing issue. Yaron Koren (talk) 13:12, 1 May 2013 (UTC)
 * Indeed it was a casing issue. The attributes in AD are sAMAccountName and streetAddress however the query only works with samaccountname and streetaddress.  Thanks.--Shizzire (talk) 19:18, 1 May 2013 (UTC)

#arraymap inside of #for_external_table
I'm getting data from the database where Parameter2 is in the format: data1, data2, data3, data4 And I'd like to use #for_external_table to populate a wikitable with results and have the individual data items wikilinked to their respective pages. I've tried the following code to interpret the Parameter2 data as an #arraymap:

I've tried different combinations of the pipe and Template:! and this one seems to work the best(but not completely working). I'm expecting for the result to be like: data1 data2 data3 data4 But the result is: data1, data2, data3, data4 I'll also note that this works relatively well using the #external_value with a single database result: But using this inside of #for_external_table has this result: data1 data1 data1 data1


 * I don't think that can be made to work, unfortunately. You could try calling #store_external_table to do the data storage, using the "#list" syntax - it's not mentioned in this documentation, but you can see it described in the #set_internal documentation. Yaron Koren (talk) 13:35, 7 May 2013 (UTC)

Bug on page with category
I have this strange problem when I insert a category to any page that use this extension: "WikiPage::updateCategoryCounts". Database returned error: "25P02: ERROR: current transaction is aborted, commands ignored until end of transaction block ".

I use Postgresql, so this can be related. If the page does not use the extension, I have no problem with category.


 * I assume this only happens on pages that call #get_db_data? Yaron Koren (talk) 16:47, 31 May 2013 (UTC)
 * Yes, only when I call #get_db_data


 * One more question: is the database you're querying the MediaWiki database, or is it a different one? Yaron Koren (talk) 15:40, 2 June 2013 (UTC)


 * sorry, I wrote few informations. Postgresql is version 9.2. It is just a single postgres installation, but wiki db and the database where I get data is another one. User used has superuser role. Sadly it is an internal wiki, not accessible from outside, so I cannot show :-( Mediawiki version is 1.21.1 and extension version is 1.6.1. I had a similar problem on another wiki, with older version, but I managed to: 1, create page with the call of #get_db_data, save and then edit and add category. But in this case it does not work and I have the error (I can show you this old wiki if interested).


 * I can guess what's causing the issue - the same Postgres-accessing code is called for both #get_db_data and the category save, and there's some sort of conflict there - but I don't know if there's a solution. One possibility you can try is using the OdbcDatabase extension, which will use different code for #get_db_data. Another is just not to have additional categories... I know it sounds extreme, but if you use Semantic MediaWiki, they mostly become unnecessary. Yaron Koren (talk) 21:58, 3 June 2013 (UTC)


 * I investigated again. After I obtained the postgresql trace, I see an error, where mediawiki seems to add the category with the same name, and I obtain: CEST ERROR: duplicate key value violates unique constraint "category_title" Anyway I will dig deeper :-)

does #get_db_data support "union"?
I want to query from a lot of collections and union the result together, does this extension support union, and how can I write the statement? Thanks


 * Hi. I'm the guy who expanded on Yaron's great MongoDB integration work to allow native mongodb.find support. In a nutshell, ExternalData has a very thin wrapper around MongoDB.  The find query is simply passed through (be careful about adding whitespaces around your curly braces so as not to confuse the MW parser!) as a JSON fragment, so whatever you can do in mongodb find, you can do in ED!  So this is more of a MongoDB question.  Have you tried looking at the MongoDB aggregation framework? Or perhaps, if you're using ED with SMW, taking advantage of #store_external_table and just using SMW ask queries to do disjunctions?  --Jnatividad (talk) 21:35, 13 June 2013 (UTC)

Error message when using #for_external_table
Hi all,

I just started using External Data and created a simple Mysql table (testdata) with two columns (id and description). When I try the following statement I get correct results:

I get my table and all looks fine. When I try to edit the page again it looks like this:

Can someone give me a hint on this?

Thanks in advance!


 * Sorry, I don't understand - the error message literally shows up in the page's wikitext? That seems impossible... Yaron Koren (talk) 20:50, 8 August 2013 (UTC)


 * Thanks already for your time to reply. I created three screenshots to make my problem more visible. Best regards, Stefan Molls 13:50, 9 August 2013 (CST).
 * - Initial Edit


 * - Result


 * - Click on Edit again


 * Okay - well, it still seems impossible. :) If you look in the page history, who made the edit that changed that text? Yaron Koren (talk) 20:58, 9 August 2013 (UTC)
 * I love to create impossible stuff ;-). In the history it just shows the change I did. Maybe this error is connected to another extension that has problems to display the edit text (WYSIWYG Editor). Will be off for the next 3 weeks. Stefan Molls 11:42, 14 August 2013 (CST).

display_external_table not showing anything
I'm trying to use {{#display_external_table:</tt> but the template is never shown. I'm using mediawiki 1.19, PHP 5.4.11 and an Oracle DB.

Here's the call I'm tying to make: {{#get_db_data: db=mydb
 * from=mytable at
 * where=at.table_name='something'
 * order by=at.app_db_cd
 * data=lvAppDbCd=DISTINCT at.app_db_cd}}

{{#display_external_table:template=TableTab|data=table=something,lang=F,source=lvAppDbCd}}

My template TableTab accept 3 parameters: table, lang and source.

Is it because I'm using Oracle? The {{#for_external_table:</tt> works very well with Oracle... Thanks.


 * Are you saying that #for_external_table works with that exact #get_db_data call? Yaron Koren (talk) 23:36, 14 August 2013 (UTC)
 * Yes, #for_external_table works perfectly. I'm using it quite often with Oracle.  Here's what I came up with instead of using #display_external_table to call my template:

{{#get_db_data: db=mydb {{#vardefine:varList|{{#for_external_table:{{{lvAppDbCd}}},}}}} {{#vardefine:varList|{{#sub:{{#var:varList}}|0|-1}}}}
 * from=mytable at
 * where=at.table_name='something'
 * order by=at.app_db_cd
 * data=lvAppDbCd=DISTINCT at.app_db_cd}}

{{#while: {{TableTab|table=something|lang=F|source={{#var:varSource}}}} {{#vardefine:varList|{{#sub:{{#var:varList}}|{{#expr:{{#pos:{{#var:varList}}|,}} + 1}} }} }} }}
 * {{#ifexpr: {{#expr:{{#pos:{{#var:varList}}|,}}+1}} > 1 | true}}
 * {{#vardefine:varSource|{{#sub:{{#var:varList}}|0|{{#expr:{{#pos:{{#var:varList}}|,}} }} }} }}

{{TableTab|table=something|lang=F|source={{#var:varList}}}}

Umlaut Issue
Undefined offset: 1 in /var/www/source/php/mediawiki/extensions/ExternalData/ED_ParserFunctions.php on line 278 appears when the XML data being collected contains Umlauts e.g. ... <Strasse>Münchnerstrasse 122c</Strasse> ... will not work but ... <Strasse>Muenchnerstrasse 122c</Strasse> ... will. The encoding is set to UTF-8 and seems to be correct. Debugging the situation indeed there is: doForExternalTables: variable 'street' has 2 values: M,Ã¼nchnerstrasse 122c Which happens already in the getContent function of ED_Utils getContent: tag='strasse' value='M' getContent: tag='strasse' value='Ã¼nchnerstrasse 122c'

adding xml_parser_set_option($xml_parser,XML_OPTION_TARGET_ENCODING,'ISO-8859-1'); will lead to getContent: tag='strasse' value='M' getContent: tag='strasse' value='ünchnerstrasse 12'

Googling for the issue there is a whole bunch of articles on this: <ul> <li>https://www.lima-city.de/thread/xml-parser-liefert-bei-umlaut-nicht-kompletten-string <li>http://www.php.de/php-tipps-2008/45287-xml-parsen-und-umlaute.html <li>http://www.php-resource.de/forum/showthread/t-97986.html <li>http://stackoverflow.com/questions/12815598/how-to-avoid-php-breaking-umlaut-characters-when-loading-a-xml-file

Character data handler is called for every piece of a text in the XML document. It can be called multiple times inside each fragment (e.g. for non-ASCII strings). <li>http://php.net/manual/en/function.xml-set-character-data-handler.php </ul>

The manual article has a lot of work-arounds. Most of them seem to be better than what was introduced in Version 1.6.2 for $ampersandReplacement e.g. this would fix the issue: diff ED_Utils.php 11c11 <  private static $inside_data = false; --- > 26d25 < 		self::$inside_data = false; 32d30 < 		self::$inside_data = false; 44,53c42,45 < 		if (self::$inside_data) { < 			$l_index=count($edgXMLValues[$edgCurrentXMLTag])-1; < 		 $edgXMLValues[$edgCurrentXMLTag][$l_index] .= $content; < 		} else { < 		 if ( array_key_exists( $edgCurrentXMLTag, $edgXMLValues ) ) < 			 $edgXMLValues[$edgCurrentXMLTag][] = $content; < 		 else < 			 $edgXMLValues[$edgCurrentXMLTag] = array( $content ); < 		} < 		self::$inside_data = true; --- > 		if ( array_key_exists( $edgCurrentXMLTag, $edgXMLValues ) ) > 			$edgXMLValues[$edgCurrentXMLTag][] = $content; > 		else > 			$edgXMLValues[$edgCurrentXMLTag] = array( $content ); and in ED_ParserFunctions.php add an isset ... if ( array_key_exists( $var, $edgValues ) && count( $edgValues[$var] > $i ) && isset($edgValues[$var][$i]) )


 * That's some great detective work - this patch you came up with is potentially very useful. Is there a public API of some sort, that has an umlaut in its data, that I can access to see this problem directly? Yaron Koren (talk) 20:21, 19 August 2013 (UTC)