Extension talk:External Data/Archive 2017 to 2018

Empty password not allowed – bug or feature?
I am using External Data to query MySQL database with passwordless read permission. I have noticed that supplying empty password in the configuration results in  being raised. However, I can get around by patching the ED_Utils.php (diff attached):

but that's not the way I would like to do it. I would like to know whether it's a bug or if there is any rationale behind asking non-empty password as open databases often provide passwordless access? Powermelon (talk) 10:56, 5 January 2015 (UTC)


 * Ah, good point; that was indeed a bug. I just checked in the change you suggested. Yaron Koren (talk) 17:14, 5 January 2015 (UTC)


 * Thank you very much! Powermelon (talk) 10:44, 6 January 2015 (UTC)

#get_db_data: stored procedure
Hi Guys,

I am using #get_db_data a lot on my internal Wiki and wonder whether #get_db_data can call a stored procedure in a SQL database? If yes, what would be the syntax for that?

Many thanks, Reiner


 * I don't think that's possible, unfortunately. If you're using MySQL, you might be able to create a "view" instead, and query on that - it can be queried like a regular DB table. Yaron Koren (talk) 18:32, 29 January 2015 (UTC)

Cannot connect to sqlite database
Hey all, some assistance on troubleshooting this would be appreciated. No matter how simple of a query I try, the page comes back with a 'Cannot contact the database server' error. This is what we have in settings: include_once "$IP/extensions/ExternalData/ExternalData.php"; $edgDBServerType['gameDB'] = "sqlite"; $edgDBDirectory['gameDB'] = "/public_html/repopnation.com/wiki/extensions"; $edgDBName['gameDB'] = "repopdata"; Is there somewhere I can go for more detailed logs? I've been assured that the file exists in that directory (with .sqlite extension), and the appropriate r/w permissions are on it. What else is there to check? Thanks, Echelar (talk) 20:55, 2 February 2015 (UTC)


 * I don't know; I've never actually connected to SQLite. Maybe someone else can help. Yaron Koren (talk) 21:27, 2 February 2015 (UTC)

Undefined index: ... ED_ParserFunctions.php on line 494
I keep getting error messages like: And it seems this is a timing / race condition issue. If I create a template in which I define #get_db_data and #display_external_table calls like this: Then I can't submit this wikitemplate in one step. The order: works. So when editing this I need to comment out the call to the template again to avoid the probably buggy notice error message. Very strange and would be nice if this would be fixed. --WolfgangFahl (talk) 12:48, 4 February 2015 (UTC)
 * 1) define #get_db_data
 * 2) define #display_external_table
 * 3) then add example call in

work-around
--91.40.116.96 16:04, 23 February 2015 (UTC)

Notice: Undefined index: odbc in D:\xampp\htdocs\SNWiki\includes\db\Database.php on line 915
I Configured External Data and ODBC For Get Data from SQL Server When Show a Record I am getting Error

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

But SQL Server Connection was fine because when i am give a wrong table i am getting error like invalid table name but when typing correct table i am getting only Notice: Undefined index: odbc in D:\xampp\htdocs\SNWiki\includes\db\Database.php on line 915

plese do the needful

Ragu


 * Sorry, I don't know. What version of MediaWiki are you using, and what's on that line in that file? Yaron Koren (talk) 14:22, 12 February 2015 (UTC)


 * Thanks for your Reply Yaron Koren.

I am Using MediaWiki 1.24

for getting sql server am data adding

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

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


 * That's a lot of information! Well, my guess is that this is an issue with the OdbcDatabase extension - that it needs to be updated for use with MW 1.24. I would suggest contacting the author of that extension. Yaron Koren (talk) 14:09, 13 February 2015 (UTC)

Retrieves nested variable before primary
Hi,

I'm trying to receive data from this URL, which lists data in JSON. However, whenever I try to retrieve "name", I get "chlorophyll" returned, not "bulbasaur". The top-level "name" is "bulbasaur", while "chlorophyll" is inside "abilities". How would I say I want to top-level instead of the first found?--Immewnity (talk) 17:18, 19 February 2015 (UTC)

Here's what I've got right now:


 * I don't think it's possible, unfortunately. If this were XML, I'd recommend using the XPath option; there is a comparable thing known as JSONPath, but it's poorly supported and External Data doesn't handle it. The only solution I can think of is to create some sort of web-based script that reads and parses that other URL, then displays only the relevant information in a format that External Data can then in turn read from. Yaron Koren (talk) 18:48, 19 February 2015 (UTC)

Difficulty querying MongoDB
Hello, I'm trying to use this extension with a local instance of MongoDB, still testing it and am getting an "Error: No "from" parameter specified." Here's the config I placed into LocalSettings.php:

$edgDBServer['foo-db'] = "127.0.0.1"; $edgDBServerType['foo-db'] = "mongodb"; $edgDBName['foo-db'] = "foo"; $edgDBUser['foo-db'] = "foo"; $edgDBPass['foo-db'] = "foobar";

I then restarted the apache service and created a test page with the following (the Mongo Collection name is "entries"):

The test page then shows: Error: No "from" parameter specified. What is the correct syntax for using the |find query parameter?


 * I don't know much about MongoD querying, but I do know you need to specify a "from" parameter - it should be the name of the "collection" that you're querying. Yaron Koren (talk) 21:27, 28 May 2015 (UTC)

Using external values in templates
I read that "parser functions can't be used within #for_external_table" only after I'd tried 100 different ways of trying to make it happen anyway. I would like to use one of my external variables to look up an attribute in a semantic search and displaying that as an extra column in the table. Any ideas for workarounds?


 * Did you try using #display_external_table instead? Yaron Koren (talk) 21:29, 28 May 2015 (UTC)


 * Thanks for your reply! Unfortunately we're stuck on v1.3.6 as we're on MediaWiki 59602 at the moment. I'll file a request for an update when we transfer to a later version of wiki. Thanks for all the great work that went into this extension!!

Conditionally include external data
Having great success retrieving customer specific data from an external database based on a code that has been assigned to the customer in a form field. However, if that field is left empty, the SQL statement fails. So I want to trap that case in my template and simply not make the call. Something like this:

However, what happens instead is that the whole page rendering fails because the pipe characters inside the get_external_data call interfere with the parsing of the #if. Any suggestions on how to solve this problem would be greatly appreciated.


 * Hi - by "get_external_data", I assume you mean get_db_data. Are you sure the pipe characters conflict? I haven't tried it, but I would think something like what you have there should work... Yaron Koren (talk) 23:18, 7 June 2015 (UTC)


 * We use a similar solution, you need template:! then change your code to look like this:


 * You could also assign a default value for, something like:   that retrieves  , if this is possible from a database point of view. Regards, --Felipe (talk) 10:24, 8 June 2015 (UTC)
 * Interesting idea, involving the database layer especially now that the problem has moved to trying to tell when the database returns no values; how does one test for that?


 * Well that all depends on what you need but I assume you don't want messages like.
 * This means that there is either:
 * A set of values returned by the query but no database NULL's.
 * Or you don't query for the data.
 * I am not sure how database NULL is handled by the current version of External Data but we always make sure that a field is empty (empty string) on the external data table and not NULL. I do not have time to test this at the moment. A couple of options:
 * Return a default set when  is empty:  . The default value returns a "default" set of values from the database but the handling is the same as the "real" values.
 * If you are sure the query will always return a result set when  is NOT EMPTY and you want to let your user know when it is EMPTY by doing something completely different then you could first check if   is empty or not, like in the earlier example. This will also prevent the query from executing when   is empty, less expensive.
 * In our experience explicitly "telling the wiki" what you want works best. We "always" use parameters with a default values, either with  or with a "empty" value  . You can make combinations with the above and there are lots of other scenarios and solutions that also work, just experiment with them and see what is the best solution in your situation. --Felipe (talk) 10:10, 15 June 2015 (UTC)

MSSQLBackCompat and mssqlold under MW 1.23.2
Ever since PHP went to windows-only sqlsrv we've been without a standard way to get data pulled into MW from MSSQL databases. Subject extension sounds like what ED could use to bridge the gap using the TDS stuff, but when I try to use it the following error is generated:  Fatal error: Cannot make non static method DatabaseType::getSoftwareLink static in class DatabaseMssqlold in /mediawiki/extensions/MSSQLBackCompat/DatabaseMssqlOld.php on line 940 Could this be ED related? or an issue with MSSQLBackCompat? (it's documentation for adjusting the MW core by modifying /includes/db/Database.php appears old. Database.php looks to have been overhauled since then).

I fear I'll have to start from scratch. Should I stop dreaming about being able to do this? Cheers. - Lbillett (talk) 13:08, 12 June 2015 (UTC)


 * That error by itself looks pretty straightforward to fix... but I've never tried connecting to MSSQL, so I'm probably not the best person to ask. Hopefully someone else can help here. Yaron Koren (talk) 13:57, 12 June 2015 (UTC)


 * Got to playing around with this today thinking, "What if I trying going through odbc?". So I set up a DSN on my machine with freetds. Setting the dbtype to odbc then trying to fire it up yields a very similar error:

 Fatal error: Cannot make non static method DatabaseType::getSoftwareLink static in class DatabaseOdbc in /mediawiki/extensions/OdbcDatabase/OdbcDatabase.body.php on line 342
 * Getting the same error with the 2 different extensions makes me wonder if I'm not configured properly? - Lbillett (talk) 19:24, 14 August 2015 (UTC)


 * Aha - looking through the code history, it looks like this change, from May 2013, is the issue - getSoftwareLink was made non-static, but these two extensions haven't been updated yet to reflect the change (assuming you're using the latest version of both). It might just be a matter of removing the word "static" from the getSoftwareLink header in both... Yaron Koren (talk) 23:02, 14 August 2015 (UTC)


 * Took 'static' out. Everything started working. (guess this was there a couple years back). ED working super-awesome now. Thank you! - Lbillett (talk) 12:48, 15 August 2015 (UTC)


 * That's great to hear! I just added a note about that to the documentation. Hopefully someone will fix the extensions themselves... Yaron Koren (talk) 02:11, 17 August 2015 (UTC)

Special:GetData Problem...
Hi Yoren - I've been playing around with this all day but I can't get this to run.



...then attempt to print a test display



The error:

 No Contents Found at URL http://lamb3rt.net/MediaWiki/Special:GetData/PropertyValuesCSV?Property_name=procedureType

The page with data PropertyValuesCSV is populated with CSV data

I believe I installed ExternalData as a part of SemanticForms. I went to the subdirectory where extensions are placed, and found in comparison to a new download, that what was installed did not match up - so reinstalled, made sure that the include_once (require_once?) statement was included in LocalSettings...but alas...still doesn't run.

Any thoughts?

Thanks as always,

Dmlddsoms (talk) 00:48, 20 June 2015 (UTC)


 * Is that literally the URL you are using? Because that one does in fact contain no contents... Yaron Koren (talk) 12:16, 21 June 2015 (UTC)
 * Ok...I finally figured it out...

Issue with special characters
I try to get a string from 2 different external API's. In the first case special character "ä" is extracted just fine but in the second case the string is truncated. See full live example with explanation (and version info). MediaWiki 1.24.0 External Data 1.6.2 (1f1de17). --Ioannis Protonotarios 11:53, 7 August 2015 (UTC)


 * That's very comprehensive! Thanks for putting it together. But you're using a rather old version of External Data - why not try upgrading? Yaron Koren (talk) 13:34, 7 August 2015 (UTC)


 * I know! I'd tried a full upgrade a while ago but I've faced some serious issues with the Wikilog extension. I'll try to upgrade External Data only and get back to you. --Ioannis Protonotarios 13:53, 7 August 2015 (UTC)

Some float encoding problems (?) through odbc
Having lots of fun connecting folks to data on MSSQL databases through odbc (via Extension:OdbcDatabase) on a linux php setup. The new # for display_external_table functionality has really improved! Sending all the results as parameters to a template opens up all kinds of display options for us. Anyway, got into some weird output when displaying results that are stored as floats. It only does this for values having a decimal (stored whole numbers work fine.). Example output for a field as: 17142.85�B¢·����� Which, when browsing the database (with toad) is: 17142.8571428571 I don't have sufficient permissions to change the db. I tried #exploding on the decimal, which sort of got the job done, but seems to mess up some other display bits (has superfluous new lines in the result). Any tweaks I can make to improve this? Thanks! - Lbillett (talk) 13:50, 24 August 2015 (UTC)


 * That's great that it's (mostly) working for you! I assume you meant #display_external_table? Anyway, it's too bad about the float weirdness. What does your #explode call look like? Yaron Koren (talk) 19:07, 24 August 2015 (UTC)


 * Bah. Yes, I meant #display_external_table. And after painstakingly trying to describe nature of the extra new lines it turned to be my template (had a newline before a closing includeonly tag. *facepalm*). The explode works as expected for chopping off the 'gunk' at the end of the numbers. Suits my needs for now, until the day I need more than 2 decimal places from a float field in that database which... will probably be very soon. Is it likely an odbc configuration problem? I can bother other nice folk if that's the case. - Lbillett (talk) 19:38, 24 August 2015 (UTC)


 * Alright, great. I don't know what's causing that float problem... you might be able to round the number to some number of decimal places, though, using ParserFunctions - see here. Yaron Koren (talk) 18:45, 25 August 2015 (UTC)

Limit statement for MSSQL
I get a 'database error' when specifying a limit. Guessing the MSSQL database is expecting a 'top' statement instead of 'limit'? I could work around this with #store_external_table and regular update script. Curious if there was a way to do it without that. Cheers! - Lbillett (talk) 13:16, 1 September 2015 (UTC)


 * I had no idea that MS SQL doesn't support "LIMIT"! Very strange. Anyway, External Data doesn't construct the SQL queries itself; it lets the DB connector class do that. So this might be a bug in the OdbcDatabase extension, assuming you're using that. Yaron Koren (talk) 00:27, 2 September 2015 (UTC)


 * Ah. Will leave some notes over there. I went ahead an 'cached' the data of interest with some subobjects. I started with #store_table_data but got into a problem where a date field (of format 'yyyy-mm-dd 00:00:00.000') wouldn't save as a proper date property. Tried applying #time inside the #store_table_data call to format it, but I see it doesn't parse the Myproperty= bits. Ended up using #display_external_table and set the subobjects in the target template. Worked great. Though I did immediately run into a use for something equivalent to the #ask userparam= parameter. Which, now that I think of it, could be neatly accommodated by the variables extension. Thanks a ton. - Lbillett (talk) 12:40, 2 September 2015 (UTC)

Cannot retrieve a json value #get_web_data
Hi There, Please look into this return JSON format: 635: { tag_text: "ABC", tag_id: 635, tag_type: "symbol", symbol_id: 973, code: "ABC", name: "ABCompany", exchange_id: 1, symbol_type: "stock", symbol_state: "ok", gics: "Materials", isStock: true, exchangeCode: "ABC", page_slug: "abc", market_cap: { tag_text: "ABC", symbol_id: 973, date: "2015-07-24", close: 25.27, total_shares: 3224483838, create_at: 1437753601 }, market_data: { tag_text: "ABC", symbol_id: 973, last_traded_at_date: "2015-07-24", last_traded_at: 1437718336, bid: 2526, offer: 2528, first: 2522, high: 2557, low: 2510, last: 2527, change: -23, volume: 8502829, value: 215100338.808, trade_count: 16712, create_at: 1437721141, price: 2527, price_change_pc: -0.902, price_change_pc_pretty: "0.90", price_change_dir: "lose" }, }

My External Data API call:

Outputs:
 * = ABC
 * = ABCompany
 * -> no output???
 * -> no output???

I expect that the whole market_cap and market_data json object will be displayed as string: market_cap: { tag_text: "ABC", symbol_id: 973, date: "2015-07-24", close: 25.27, total_shares: 3224483838, create_at: 1437753601 },

Any suggestions/answers on how I can get the desired output?? Appreciate your help..


 * Yes - get each individual value, and then display them all together in that JSON format. Yaron Koren (talk) 13:10, 4 September 2015 (UTC)

Error: No contents found at URL
I've been using the extension for a while now, but this is the first time I tried #get_web_data.

No matter which URL I pass, I get the error Error: No contents found at URL http:// . I get the same error no matter what I pass. Valid and invalid URLs alike.

I then tried calling the following function with a test script from command line on the server.

I get below error message

Am I missing a prerequisite on PHP installation or is this a custom class and I'm missing something on my extension/wiki installation? --Wikimantis (talk) 05:48, 10 September 2015 (UTC)


 * The test script won't work, unless it includes all the MediaWiki code. I don't know why the code is not able to access the URLs - I'm guessing that it's due to some issue with PHP's Curl library on your server, but I could be wrong. This may be a sill question, but - have you tried accessing a publicly-viewable website, or are they all private? Yaron Koren (talk) 14:57, 10 September 2015 (UTC)


 * Yes, that's correct. Had to include mediawiki code. I no longer get any error from the test script. But Http::get wont still return anything. Like you said, could be an issue with Curl library on the server.
 * And yes, I tried with publicly accessible URLs that are reachable by the server. Did fetch those with wget in CLI just to make sure. --Wikimantis (talk) 18:47, 10 September 2015 (UTC)


 * After some troubleshooting, I was able to figure out what the problem is. It seems, for some reason, DNS lookup fails. When I provide IP addresses instead of URLs extension works. My server is able to resolve DNS in CLI perfectly but for some reason, curl fails to do that. If anyone has any idea what's going on... please share your thoughts on how to fix this --Wikimantis (talk) 18:28, 15 September 2015 (UTC)


 * Okay figured out why this is happening. It turns out curl tries to resolve both IPv4 and IPv6 DNS for the URL. Since I had IPv6 module loaded on the server, but no DNS that can do v6 resolution, it fails. I unloaded the v6 module completely (followed this url in my case), rebooted the server and now the extension is working like it's supposed to. --Wikimantis (talk) 18:54, 15 September 2015 (UTC)


 * That's great! And good to know. Yaron Koren (talk) 01:18, 16 September 2015 (UTC)

multiple Documents in XML-File
Hi,

I'm trying to set up the following:

I got an example XML file out of an Oracle Database:

There are a total of 8 Documents listed in the Database that are related to doc_ref_key 258. I now want to make a Mediawiki-Template that prints the Link to the Document () of each given document on the wikipage.

The Problem is, the number of documents related to the key is variable. 258 has 8 documents and 259 has for example only 4 documents. The question now is, how do I make a template, that's flexible and can print a n-Number (unknown) of documents?

I've got a static example:

But if there are 10 Documents, 2 will not be listed and when there are only 2 documents I get a big Gap at the bottom of the wiki-article.

Any Ideas? Thanks for your answers.


 * You could use #for_external_table or #display_external_table, and then you wouldn't need XPath... Yaron Koren (talk) 14:04, 18 September 2015 (UTC)
 * Got it to work. I didn't thought that it is that simple. The function #for_external_table is somewhat hard to understand, but now I get it. Thanks anyways for your fast reply.
 * Edit: I've got another Question. Is there any way I can filter for a specific value? If I as example only want to show documents with Type2 on my wiki Page? Thanks!


 * You should really make a separate section for a separate question. But: if the web API lets you set that kind of filter through the URL, then you can easily do it. Otherwise, this may require storing the data via Semantic MediaWiki or Cargo (see documentation for either one), then displaying the filtered data with a query. Yaron Koren (talk) 13:35, 21 September 2015 (UTC)
 * Could set it trough the URL. I should open my eyes before asking these questions. Thanks anyway!

Handling JSON with array values
I have this situation where returned JSON data has always one row of data, but one of the external values is actually an array with a variable number of elements. It looks like this:

The array "origin" can have any number of elements. For each element I need to create a line of text. But I don't get it how to address the values inside the array "origin". I've tried everything imaginable...


 * I'm not entirely sure what you're trying to do, but my guess is that this would require the use of JSONpath - the JSON equivalent of XPath. Unfortunately, JSONpath is poorly supported in general, and External Data doesn't handle it. So the only solution I can think of is to create your own "wrapper API" - an online script that gets the data from the real API, does its own processing on it, and outputs the data in a simple format that ED can parse. Yaron Koren (talk) 13:46, 21 September 2015 (UTC)


 * I was affraid that this will be the answer :) I'll take your advice, thanks.

There is already a transaction in progress
I am using PostgreSQL 9.4. My query

From the database log, I see this WARNING: there is already a transaction in progress

I see that it has two transactions going on. Why?

Discovered the problem: The mediawiki and the SMS database (that I want to query) are from the same postgreSQL database.

$edgDBTablePrefix option does not work
Settings in LocalSettings.php are: $edgDBServer    ['cargo'] = $wgDBserver; $edgDBServerType ['cargo'] = $wgDBtype;. $edgDBName      ['cargo'] = $wgDBname; $edgDBUser      ['cargo'] = $wgDBuser; $edgDBPass      ['cargo'] = $wgDBpassword; $edgDBTablePrefix['cargo'] = 'cargo_'; but you still have to write: --StasR (talk) 19:28, 22 October 2015 (UTC)


 * That's not good. What version of ED are you using? Yaron Koren (talk) 23:18, 22 October 2015 (UTC)
 * Both, REL1_25 and master (downloaded from Git yesterday) --StasR (talk) 09:19, 23 October 2015 (UTC)