PDA

View Full Version : Datalink and XML



Keith Gandy
07-05-2010, 07:40 AM
Does anyone have a successful link from a local xml file that accesses an internet mysql database, which then feeds a Datalink/LiveText?

Datalink works great with the text files, but I am yet to successfully find a way to code the xml file required - when it is not localhost. I do have rights to the database - including user and password. A php file exports that needed data to a xml file output. Can anyone help with a way to get a serverside xml set up?

Thanks.

Red_Oddity
07-05-2010, 05:15 PM
I have no experience with the Tricaster what so ever, but you mean to ask if someone has a php script that outputs a XML complient stream from a data base?

This for example returns a xml stream when you enter the adress to this php script.


<?php
header("Content-type: text/xml; charset: utf-8");
$host = "localhost";
$user = "username";
$pass = "password";
$database = "yourdatabase";

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");

$query = "SELECT * FROM xmldb ORDER BY date DESC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\r\n";
$xml_output .= "<data>\r\n";
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
$row = mysql_fetch_assoc($resultID);
$xml_output .= "\t<item>";
$xml_output .= "\t\t<titel>" . $row['titel'] . "</titel>";
$xml_output .= "\t\t<date>" . $row['date'] . "</date>";
$xml_output .= "\t\t<content><![CDATA[" . $row['content'] . "]]></content>";
$xml_output .= "\t</item>\r\n";
}

$xml_output .= "</data>";
echo $xml_output;
?>

Keith Gandy
07-06-2010, 04:45 AM
Thanks. I was wondering if there is a way to access a mysql database without having to install apache/php/mysql on the client side. I am installing Apache and php on the client in order to be able to write a xml file to disk. When that is completed, I will probably use your script as a base - very simple code. Thanks.

Somehow I was hoping that a xml file can access a mysql database directly without a localhost being installed.

SBowie
07-06-2010, 06:08 AM
Somehow I was hoping that a xml file can access a mysql database directly without a localhost being installed.I'm no database or xml guru, but isn't it a matter of providing the correct server name to the connection string in the example given in the manual, Keith?


<!
Example connection string and query for DataLink using MySQL.
This produces the keys %book.1%, %book.2%... etc
-->

<sql_databases>

<database connection_string = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; user=root;password=my_password; database=book_table">

<query>
command = "SELECT title FROM Books;"
key = "book"
timeout = "10000"
multiple_keys = "true"
</query>

</database>
</sql_databases>

Keith Gandy
07-06-2010, 09:57 AM
Steve, I have access to the username, password, etc. Since the mysql database is not localhost, I haven't been able to access the internet-based mysql.

I am in the process of installing apache, php and mysql locally to see if that will give me any further progress. So far, using your code - the one that was sent with Datalink and LiveText - does not output anything other than those coded lines; the data is not accessed and printed.

SBowie
07-06-2010, 10:13 AM
Since the mysql database is not localhost, I haven't been able to access the internet-based mysql.Again, no expert on these things, but I'd expect that there would be no hope of it working correctly without the host server name being supplied (with correct syntax, of course.) I'm not sure at all, but would have expected that a properly formatted URL could serve. Perhaps someone else can chip in ...

Red_Oddity
07-06-2010, 04:21 PM
You could try Xampp, you can extract it to a folder and pretty much run it from there (no installation needed), it contains a completely pre installed server with all the services and tools to test.

http://www.apachefriends.org/en/xampp.html

Keith Gandy
07-06-2010, 10:12 PM
Here are the results from my testing so far:

The code provided with Datalink as a sample is ASP.net, not PHP. That code has not produced any successful access in any combination that I have tried.

When I produce a XML file with PHP those are not read by Datalink.

I can produce a txt file via PHP that dynamically updates Datalink, so certain aspects of Datalink are accessible and working.

Can Newtek upload a XML example that is read by Datalink so that we can actually see something that works?

Keith Gandy
07-06-2010, 10:34 PM
@ Red Oddity: I have Apache running and MySQL installed as localhost. The Mysql functions properly and I can export data in both Text and XML form. Only the Text is recognized by Datalink.

Unfortunately the user manual is limitted to about one page. When we produce successful implementation then we will have to expand the tutorial within this forum.

Keith Gandy
07-07-2010, 02:57 AM
Follow-up: I have registered the ODBC Driver to the mysql database. It tests positive in other programs - and those are set to the exact settings in the test DataLink setup.

<sql_databases>
<database connection_string = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;user=root;password=MYPASS WORD;database=MY_DB;">
<query command = "SELECT title FROM atomtest;" key = "book" timeout = "10000" multiple_keys = "true" />
</database>
</sql_databases>

Keith Gandy
07-07-2010, 10:11 PM
Present status: After putting a tracer on the ODBC driver, LiveText/Datalink does not even show that it is attempting to access the connector. All other programs leave a log statement that they are attempting to access the mysql database (successfully).

It might be that the problem lies elsewhere, but it is not looking like Datalink is working on the database access side.

Keith Gandy
07-07-2010, 10:19 PM
@ Steve - unless a server is set up to allow remote connection - which most are not for security issues - then only a localhost setting will permit access. Remote access can be acquired, but that will mean additional coding, and an additional learning curve just to get up and running.

That means that a url setting for the "server=some ip address" section will generally not work. Most likely it will require a "server=localhost" to receive access to the mysql database.