Extension talk:External Data

Archives

2009 2010 2011 2012

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 {{#for_external_table:{{{T12U}}}, }}, 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:

{{#switch:{{{1|}}}|NS=ns|US=μs|MS=ms|S=s|M=min|H=h|D=day|Y=year}}

However, the following code {{#for_external_table:{{Time unit|{{{T12U}}} }} }} shows nothing. Though the template itself works fine, e.g. {{Time unit|M}} 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

{{#get_db_data:
|server = adr
|from = PersonAdresse
|where = VN_NN_Name LIKE 'G%'
|data=
 VN_NN=VN_NN_Name,
 NN_VN=NN_VN_Name,
 COMP=Company,
 PH=Phone
}}

Now it this has to be changed in

{{#get_db_data:
|server=adr
|from=PersonAdresse
|where=VN_NN_Name LIKE 'G%'
|data=VNNN=VN_NN_Name,NNVN=NN_VN_Name,COMP=Company,PH=Phone
}}

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:

• no space in combination with "=" and ","
• no internal names with underscore "_"
• I'll have to change a lot :-(

Second Topic seams to reopen "Issue with parameters that contain '_'" --Hk sup (talk) 09:46, 23 January 2013 (UTC)

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:

{{#get_db_data:
db=market_data1
|from=sub_core_data c, sub s
|where=s.sub_id=c.sub_id 
|data=par=c.par_12,numberactive=c.number_active,grossportfolio=c.gross_portfolio
}}

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

{{#get_db_data:
db=market_data1
|from=sub_core_data c, sub s
|where=s.sub_id=c.sub_id 
|data=par_12=c.par_12,number_active=c.number_active,gross_portfolio=c.gross_portfolio
}}

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)

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
|from=agenter
|data=firstname=firstname

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?

---

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 hostname/instancename. Using #get_db_data though, this doesn't work. I've tried setting $edgDBServer['databaseID'] 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:

{{#get_db_data:
db=employee-db
|from=agents
|order by=country ASC
|data=firstname=firstname,lastname=lastname,country=country,skillset=skillset
}}{| class="wikiTable"
! Nr.
! Name
! Skillset
|-
|<ia />
|{{#for_external_table:{{{firstname}}} {{{lastname}}} 
 }}
|{{#for_external_table:{{{skillset}}} 
 }}
|}

Well, I would think you should have only one #for_external_table call, and the <ia /> 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.

{{#get_db_data:
db=employee-db
|from=agents
|order by=country ASC
|data=id=id,firstname=firstname,lastname=lastname,country=country,skillset=skillset
}}
{| class="numberedTable"
! Nr.
! Name
! Skillset {{#for_external_table:
|-
| <ia />
| {{{firstname}}} {{{lastname}}}
| {{{skillset}}} }}
|}

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.

{{#get_ldap_data:
domain=domainname
|filter=(sAMAccountName=myusername)
|data=sAMAccountName=sAMAccountName,streetAddress=streetAddress
}}

{{#external_value:sAMAccountName}}
{{#external_value:streetAddress}}

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:

{{#get_db_data:
db=DataBase1
|from=AssetTable
|order by=Parameter1
|data=Parameter1=Parameter1,Parameter2=Parameter2,Parameter3=Parameter3,Parameter4=Parameter4
}}
{| class = "wikitable"
! align="left"|Parameter 1
! Parameter 2
! Parameter 3
! Parameter 4
|-
{{#for_external_table:<br/>
{{!}}-
{{!}} {{{Parameter1}}}
{{!}} {{#arraymap:{{{Parameter2}}}|,|x|[[x]]|<br/>}}
{{!}} {{{Parameter3}}}
{{!}} {{{Parameter4}}}
}}
|}

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:

{{#arraymap:{{{#external_value:parameter2}}}|,|x|[[x]]|<br/>}}

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)