Extension talk:External Data

Invalid JSON on #get_web_data

Product	Version
Mediawiki	1.30.0
PHP	7.1.0
ExternalData	REL1_31

I plan on doing some upgrades to mediawiki and possibly php as a shot in the dark, but before doing that I figured I would ask here.

Here is the exact json of the table I'm trying to display. The data itself is junk just to play with.

[{"IPAddress":"192.168.000.001","Type":"Server","Device":null,"Location":null,"Comment":null,"HostnameDNS":null,"OSFirmware":null,"LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.002","Type":"Server","Device":"Virtual Server","Location":"ERA - Server Room","Comment":"File and Print Server","HostnameDNS":"fileserver","OSFirmware":"Server 2016","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.003","Type":"Server","Device":"Virtual Server","Location":"ERA - Server Room","Comment":"Video Server","HostnameDNS":"videoserver","OSFirmware":"Linux","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.004","Type":"Server","Device":"Physical Server","Location":"ERA - Server Room","Comment":"Web Server","HostnameDNS":"webserver","OSFirmware":"IBM OS\/400 7.2","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"},{"IPAddress":"192.168.000.005","Type":"Server","Device":"Physical Server","Location":"ERA - Server Room","Comment":"Mainframe","HostnameDNS":"mainframe","OSFirmware":"IBM OS\/400 7.2","LastVerified":"1\/7\/2021","PlantNetwork":"999Office"}]

I'm getting this from a really generic json_encode( $data ); function in select_ipam.php, which queries a Microsoft SQL server database. I have already verified that my header information is set correctly as another user pointed out.

header("Content-Type: application/json; charset=utf-8");
echo( json_encode( $data ) );

{{#clear_external_data:}}
{{#get_web_data: url=http://192.168.0.31/api/wiki/select_ipam.php
 |format=JSON
 |data=IPAM ipaddress=IPAddress,type=Type,device=Device,location=Location,comment=Comment,hostnamedns=HostnameDNS,osfirmware=OSFirmware,lastverified=LastVerified
}}
{| class="wikitable"
! IPAddress
! Type
! Device
! Location
! Comment
! HostnameDNS
! OSFirmware
! LastVerified
{{#display_external_table:template=IPAM info row}}
|}

I also tried using the "use jsonpath" approach but it just gives me the same thing.

I'm not sure what the exact problem you're seeing is, but I would recommend switching to the latest version of External Data - the version you're running is rather old. Yaron Koren (talk) 00:46, 8 January 2021 (UTC)

I was able to get it working with some different syntax not using a template and a version update. Now if I can just figure out how to get it to update more frequently. When I click edit and save the page, without making any other changes it updates the table, but otherwise it doesn't want to grab new data from the php post. Are there any plans to integrate an "ajax mode" that would allow the table data to be refreshed say every 10 seconds without a full page reload?

Okay, that's good. No... that sounds like it would require a whole different approach - JavaScript-based instead of PHP-based. Yaron Koren (talk) 20:18, 8 January 2021 (UTC)

get_web_data error after updating mediawiki

updating to mediawiki 1.35.1 and using the latest external data 2.1 (2a02f87) 01:42, 13 July 2020 results in my template:

{{#get_web_data: url={{SERVER}}{{SCRIPTPATH}}/api.php?action=query&list=categorymembers&cmtitle=Category:CHOPs&format=xml&cmlimit=500
  | format=XML
  | data=title=title}}
{|
! class="navBox"|CHOPs
|-
|{{#display_external_table:template=catList
  | delimiter=• 
  | data=title=title}}
|}

erroring with:

Could not get URL http://localhost/api.php?action=query&list=categorymembers&cmtitle=Category:CHOPs&format=xml&cmlimit=500 after 3 tries.

Clicking the url though returns the expected XML..

--Wuestenarchitekten (talk) 21:47, 13 January 2021 (UTC)

Silly question, but did this work before? Yaron Koren (talk) 14:00, 14 January 2021 (UTC)

#get_db_data with 'join on' statement returning blank table

Apologies in advance, I am somewhat new to SQL but I have some experience. I am attempting to use #get_db_data to pull data from a local server DB to display sports statistics. One of the tables has a field (Position) that I need for the wikitable, so I was hoping to join these two tables using the 'PlayerID' field. I can't do this server-side because it would add considerable time to processing and, again, I don't have enough experience.

Using the aliases throughout and executing the query returns no syntax issues or DB errors, but #for_external_table returns an empty table. I get the appropriate number of rows in the table, so I assume it's returning nulls or default values. Without the ability to debug there's no way to track down where in the process it's losing the values.

Here's the code I wrote:

{{#get_db_data:
 db=myDatabaseName
 |from=PlayerSeasonStats=statistics,Players=players
 |join on= statistics.PlayerID = players.PlayerID
 |where= statistics.Season='2017'
 |order by= statistics.PassYds ASC
 |data=position=players.Position,playerID=statistics.PlayerID,team=statistics.Team,games=statistics.G,gameStarts=statistics.GS,passAtt=statistics.PassAtt,passComp=statistics.PassCmp,passYds=statistics.PassYds
}}

{| class="wikitable sortable" 
|-
! colspan="8"|Passing
|-
! Pos
! Team
! G
! GS
! PA
! PC
! Yds{{#for_external_table:
{{!}}-
{{!}} {{{position}}}
{{!}} {{{team}}}
{{!}} {{{games}}}
{{!}} {{{gameStarts}}}
{{!}} {{{passAtt}}}
{{!}} {{{passComp}}}
{{!}} {{{passYds}}}
}}
|} 

Documentation is limited for the 'join on' statement for this extension, and there aren't even any reported issues from the past (I've checked the archives). Should I create a template and use #display_external_table instead? Any help would be appreciated.

2603:8001:4948:DF00:7D69:EB88:902:704 09:50, 26 January 2021 (UTC)gplehner

I don't know. My guess is that the problem is in the "data" parameter, somehow - that External Data is failing to find the external variables listed. I would try taking out those table aliases, i.e. keeping the original table names... there's a chance that that will fix the problem. Yaron Koren (talk) 15:11, 26 January 2021 (UTC)

Thanks, I had time to come back to this today. Removing the table aliases from the "data" parameters returned Error 1052. I incrementally removed each alias until I narrowed the issue to two parameters, team and playerID. Both tables have a field with the name 'Team' and 'PlayerID'. It doesn't seem to matter which one I request in the "data" parameter, both print out the default/'else' values I set. The 'data' parameter certainly is understanding the alias names.

I should also mention that I switched to using '#display_external_table' in an attempt to pass data parameters that way but it didn't work either. Thanks for your response & suggestions. 45.51.157.21 08:21, 28 January 2021 (UTC) -gplehner

If you have database server access you should create a VIEW on the database and then do the select on that view.

In the view you can join whatever table you want without touching the tables.

See: https://www.w3schools.com/sql/sql_view.asp

In your case you create a view called PlayerSeasonStats. --Felipe (talk) 13:14, 28 January 2021 (UTC)

I meant removing table aliases from the entire query, by the way, not just from "data". Yaron Koren (talk) 16:26, 28 January 2021 (UTC)

I'm seeing the same issue in the current version. "data" doesn't seem to be able to access fields when they are prefixed with their table alias. Seems this behavior has introduced with REL1_31

• Can you reproduce undesired behaviour on this database?
  Alex Mashin (talk) 06:30, 3 August 2021 (UTC)

function to check whether any line of external data matches a string

My use case for External data is reading a single csv file, and then displaying different tables for lines that contain video/pdf/image etc, indicated by a type column. While I can use {{#ifeq:{{#external_value:type}} to check the first line of data, the header, has been read, I can't decide to only display the video table if videos are available. How hard would it be to have a function that checked all entries in a particular column against a search string, returning true if any matched? At the moment I have lots of tables with headers and no content. Vicarage (talk) 16:48, 7 March 2021 (UTC)

If I understand this question correctly, you may be able to accomplish this with the Variables extension - instead of displaying the table directly, set it in a variable, and then only display that variable (and its header) if it's non-empty. Yaron Koren (talk) 02:12, 8 March 2021 (UTC)

Thanks, I'll look at that. My initial workaround was to create a counts.csv file, with no header, and check against {{#ifexpr:{{#external_value:imagecount}} > 0, where imagecount=column 4 of the file. Vicarage (talk) 11:57, 8 March 2021 (UTC)

retrieving a template reference from external_data.

I'd like to be able to retrieve a json object that contains one key ('data') and a string that contains mediawiki formatted text. Is there a way to parse that retrieved string, so that it displays on the page as formatted data, rather than plain text?

Example: I'd like to retrieve {{TemplateName|Data|Data|Data}} from an external source, and have it render the template

173.16.242.134 04:23, 8 March 2021 (UTC)

• Try using Lua bindings and frame:preprocess().
  Alex Mashin (talk) 14:54, 24 July 2021 (UTC)

display_external_table should do nothing if its template function returns nothing

In http://wiki.johnbray.org.uk/Problem1 I read a 2 column file, and want to put items from the second column in a table ONLY when the first column has value "good". The template function has a switch so nothing is returned, but display_external_table adds a blank line to the last table box, which spoils the format. Using delimiter= just makes the table wider.

As this is a key use case for display_external_table, it would seem to make sense to just do nothing if there was nothing to display. Vicarage (talk) 09:52, 10 March 2021 (UTC)

Fatal exception of type "InvalidArgumentException" When trying to access mysql database.

Recently updated to MediaWiki 1.35 from 1.31, and updated the extension to the latest version for 1.35. Previously the get_db_data queries were working fine, but now they are throwing an Invalid Argument Exception.

When I run a debug log I get the following output, related to one of the exceptions.

[exception] [YGRqrMgm-gbpiKaSuVS3ewABaww] /index.php?title=Template:CementKiln&action=submit InvalidArgumentException from line 68 of /home1/wikiwast/public_html/includes/libs/rdbms/database/domain/DatabaseDomain.php: Prefix must be a string.
#0 /home1/wikiwast/public_html/includes/libs/rdbms/database/Database.php(291): Wikimedia\Rdbms\DatabaseDomain->__construct(string, NULL, NULL)
#1 /home1/wikiwast/public_html/includes/libs/rdbms/database/DatabaseMysqlBase.php(111): Wikimedia\Rdbms\Database->__construct(array)
#2 /home1/wikiwast/public_html/includes/libs/rdbms/database/Database.php(433): Wikimedia\Rdbms\DatabaseMysqlBase->__construct(array)
#3 /home1/wikiwast/public_html/extensions/ExternalData/includes/EDUtils.php(238): Wikimedia\Rdbms\Database::factory(string, array)
#4 /home1/wikiwast/public_html/extensions/ExternalData/includes/EDParserFunctions.php(377): EDUtils::getDBData(string, string, array, string, array, NULL, array)
#5 /home1/wikiwast/public_html/includes/parser/Parser.php(3340): EDParserFunctions::doGetDBData(Parser, string, string, string, string)
#6 /home1/wikiwast/public_html/includes/parser/Parser.php(3047): Parser->callParserFunction(PPFrame_Hash, string, array)
#7 /home1/wikiwast/public_html/includes/parser/PPFrame_Hash.php(253): Parser->braceSubstitution(array, PPFrame_Hash)
#8 /home1/wikiwast/public_html/includes/parser/Parser.php(2887): PPFrame_Hash->expand(PPNode_Hash_Tree, integer)
#9 /home1/wikiwast/public_html/includes/parser/Parser.php(1556): Parser->replaceVariables(string)
#10 /home1/wikiwast/public_html/includes/parser/Parser.php(651): Parser->internalParse(string)
#11 /home1/wikiwast/public_html/includes/content/WikitextContent.php(374): Parser->parse(string, Title, ParserOptions, boolean, boolean, NULL)
#12 /home1/wikiwast/public_html/includes/content/AbstractContent.php(590): WikitextContent->fillParserOutput(Title, NULL, ParserOptions, boolean, ParserOutput)
#13 /home1/wikiwast/public_html/includes/EditPage.php(4282): AbstractContent->getParserOutput(Title, NULL, ParserOptions)
#14 /home1/wikiwast/public_html/includes/EditPage.php(4187): EditPage->doPreviewParse(WikitextContent)
#15 /home1/wikiwast/public_html/includes/EditPage.php(2965): EditPage->getPreviewText()
#16 /home1/wikiwast/public_html/includes/EditPage.php(701): EditPage->showEditForm()
#17 /home1/wikiwast/public_html/includes/actions/EditAction.php(71): EditPage->edit()
#18 /home1/wikiwast/public_html/includes/actions/SubmitAction.php(38): EditAction->show()
#19 /home1/wikiwast/public_html/includes/MediaWiki.php(527): SubmitAction->show()
#20 /home1/wikiwast/public_html/includes/MediaWiki.php(313): MediaWiki->performAction(Article, Title)
#21 /home1/wikiwast/public_html/includes/MediaWiki.php(940): MediaWiki->performRequest()
#22 /home1/wikiwast/public_html/includes/MediaWiki.php(543): MediaWiki->main()
#23 /home1/wikiwast/public_html/index.php(53): MediaWiki->run()
#24 /home1/wikiwast/public_html/index.php(46): wfIndexMain()
#25 {main}

I have re-loaded the extension several times, and tried a couple of different databases.

the LocalConfig.php entries are..

$edgDBServer['engy']="localhost";
$edgDBServerType['engy']="mysql";
$edgDBName['engy']="wikiwast_energy";
$edgDBUser['engy']="wikiwast_energy";
$edgDBPass['engy']="<password>";

The query I used was, being called from a template. Where the value of EPR is passed to it through the template call. All the data values are correct and contained within the mysql database.

{{#clear_external_data:}}
{{#get_db_data:
db=engy 
|from=cementkilns
|where=epr='{{{EPR}}}' 
|data=site=site,epr=epr,company=company,parent=parent,clinker=clinker
}}

I am at a loss as to what the problem is, I hope that someone might see this and be able to help.

For External Data - and really any extension whose compatibility policy is "Master maintains backwards compatibility", which includes all of my extensions - you should not use a REL_ branch, but instead just use the latest code - either the truly latest code, or the latest released version. Using either one may fix this specific problem. Yaron Koren (talk) 13:12, 31 March 2021 (UTC)

Thanks a Million Yaron that has fixed the issue. Will remember that in future.

Usage in DE:WP

Hy! This extension seems not to be installed on any Wikipedia, even on Mediawiki not! Tried a usecase on DE:WP, without success. Any possibility to get it enabled on a local Wikipedia? Regards, Uwe Martens (talk) 20:44, 25 April 2021 (UTC)

That's outside of my domain. But it would be great if this extension could be installed on a Wikimedia site! Yaron Koren (talk) 12:26, 26 April 2021 (UTC)

Thanks for your response! I'll discuss it with a responsible admin. I hoped one of them is active here, but anyway, best regards, Uwe Martens (talk) 13:07, 26 April 2021 (UTC)

No data with XML namespace

Hi Yaron, I bumped into something weird. It seems that External Data is not always able to fetch data when the XML file contains a URL for xmlns. I guess the reason could be, in some cases, that the URL is not accessible (you'd be surprised how often that happens) or in others, that the URL redirects to a new one. Lots of sites still use xmlns="http://www.w3.org/1999/xhtml", with that URL redirecting to the more secure https://www.w3.org/1999/xhtml/. I've created a snippet that you can try out for yourself:

<?xml version="1.0" encoding="utf-8"?>
<article xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/1999/xhtml" id="595959383" load-date="October 2009">
<author-group>
<author>
<firstname>Jo</firstname>
<surname>Nesbø</surname>
</author>
</author-group>
</article>

Cavila 11:13, 19 August 2021 (UTC)

Use default xmlns prefix and add it to the XPath query:

{{#get_file_data: file = xmlns

| format = xml

| use xpath

| data = res=//ns:author/ns:surname

| default xmlns prefix = ns

}}

res: {{#external_value:res}} Alex Mashin (talk) 11:43, 19 August 2021 (UTC)