Roku Developer Program

Developers and content creators—a complete solution for growing an audience directly.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

retrieve data from mysql

hi guys... this is the last question on the subject.
i wonder how to retrieve data from mysql. I know that i can't have use of mysql directly with brigthscript.
Sorry for the inconvenience, many thank you very much.
0 Kudos
8 Replies
gonzotek
Level 8

Re: retrieve data from mysql

"ludwig.benitez" wrote:
hi guys... this is the last question on the subject.
i wonder how to retrieve data from mysql. I know that i can't have use of mysql directly with brigthscript.
Sorry for the inconvenience, many thank you very much.

You'll need to create a server-side script in one language or another with which you are comfortable. The Roku code will call the script (e.g. http://server.com/getVideos.php?genre=SciFi&year=1968&director=Kubrick). The script will connect to your mysql server instance, perform the necessary query, retrieve the results, then format them into an xml(or JSON or other structured data format) string, then send that formatted data to the Roku.
Remoku.tv - A free web app for Roku Remote Control!
Want to control your Roku from nearly any phone, computer or tablet? Get started at http://help.remoku.tv
by Apps4TV - Applications for television and beyond: http://www.apps4tv.com
0 Kudos

Re: retrieve data from mysql

"gonzotek" wrote:
You'll need to create a server-side script in one language or another with which you are comfortable. The Roku code will call the script (e.g. http://server.com/getVideos.php?genre=SciFi&year=1968&director=Kubrick). The script will connect to your mysql server instance, perform the necessary query, retrieve the results, then format them into an xml(or JSON or other structured data format) string, then send that formatted data to the Roku.


Thanks! I now understand something! first, I create the xml file with php, then I comment again. thank you very much! :mrgreen:
0 Kudos

Re: retrieve data from mysql

"gonzotek" wrote:
... then format them into an xml(or JSON or other structured data format) string, then send that formatted data to the Roku.


Dear gonzotek,
I have done the xml file on the server, written in php (https://www.dropbox.com/s/84ll97q5h4v1jef/xmlroku.png)

Now, you said: "then send that formatted data to the Roku."
How do I retrieve data from the roku channel?. With great humility I welcome your comments. Thanks!
0 Kudos
Highlighted
gonzotek
Level 8

Re: retrieve data from mysql

"ludwig.benitez" wrote:
"gonzotek" wrote:
... then format them into an xml(or JSON or other structured data format) string, then send that formatted data to the Roku.


Dear gonzotek,
I have done the xml file on the server, written in php (https://www.dropbox.com/s/84ll97q5h4v1jef/xmlroku.png)

Now, you said: "then send that formatted data to the Roku."
How do I retrieve data from the roku channel?. With great humility I welcome your comments. Thanks!

You can retrieve the data using the roUrlTransfer system component, then parse it with roXMLElement:
transferObj = CreateObject("roUrlTransfer")
transferObj.SetURL("http://server/suscripcion.php")
xml = CreateObject("roXMLElement")
xml.Parse(transferObj.gettostring())
print xml.post.titulo.getText()

If you haven't already, I highly recommend reading the developer documentation available at http://sdkdocs.roku.com. My recommended reading order: Start with the Developer Guide to get a high-level overview, move on to the BrightScript Language reference, Design Guidelines, Channel Packaging and Publishing, and then the Component Reference. Then at least skim over the rest. You don't have to memorize the entire component reference, but it helps tremendously to have a general familiarity with the built-in functions and overall system architecture.
Remoku.tv - A free web app for Roku Remote Control!
Want to control your Roku from nearly any phone, computer or tablet? Get started at http://help.remoku.tv
by Apps4TV - Applications for television and beyond: http://www.apps4tv.com
0 Kudos

Re: retrieve data from mysql

"gonzotek" wrote:
You can retrieve the data using the roUrlTransfer system component, then parse it with roXMLElement:
transferObj = CreateObject("roUrlTransfer")
transferObj.SetURL("http://server/suscripcion.php")
xml = CreateObject("roXMLElement")
xml.Parse(transferObj.gettostring())
print xml.post.titulo.getText()


Thanks guys for the answers. Now, i have this:

Function ShowMessageDialog() As Void 
'create here the transfer with script on the server
transferObj = CreateObject("roUrlTransfer")
transferObj.SetURL("http://www.rodeobiobio.cl/Suscripcion/file.xml")
xml = CreateObject("roXMLElement")
xml.Parse(transferObj.GetToString())
var_uno = xml.rsp.photos.photo[0].GetText()


port = CreateObject("roMessagePort")
dialog = CreateObject("roMessageDialog")
dialog.SetMessagePort(port)
dialog.SetTitle("blablabla")
dialog.SetText("más blablabla")
dialog.SetText("var_uno")

dialog.AddButton(1, "Validar")
dialog.EnableBackButton(true)
dialog.Show()
While True
dlgMsg = wait(0, dialog.GetMessagePort())
If type(dlgMsg) = "roMessageDialogEvent"
if dlgMsg.isButtonPressed()
if dlgMsg.GetIndex() = 1
exit while
end if
else if dlgMsg.isScreenClosed()
exit while
end if
end if
end while
End Function


I read the documentation, but I can not do this. Please. Humbly await your help. pleaseeee Smiley Sad
0 Kudos
gonzotek
Level 8

Re: retrieve data from mysql

I see three possible issues -
First issue:
var_uno = xml.rsp.photos.photo[0].GetText()

I think the xml parser subsumes the root element (rsp in this case) of the xml, so you would want to do this instead:
var_uno = xml.photos.photo[0].GetText()

You can print the result to the debug console to verify that your var_uno is getting the data it should. To check this easily, add the following line after the above line, then telnet to port 8085 while your development channel is running. You should see the value of the text node of the first photo element. BUT see issue #3 before you try any of this!
print var_uno


Second issue:
dialog.SetText("var_uno")

You've placed quotes around the variable name, so the system thinks it's a text string. Change the line to:
dialog.SetText(var_uno)


Third issue:
Your example xml (at http://www.rodeobiobio.cl/Suscripcion/file.xml) is valid xml, BUT doesn't appear to have text nodes! So the line "xml.photos.photo[0].GetText()" will return nothing. If you modified the xml of the first photo item to look like this, I think it would work:
<photo id="3131875696" owner="21963906@N06" secret="f248c84625" server="3125" farm="4" title="VNY 16R" ispublic="1" isfriend="0" isfamily="0">HERE IS SOME TEXT.  THIS TEXT SHOULD APPEAR IN WHEN var_uno IS PRINTED!</photo>
Remoku.tv - A free web app for Roku Remote Control!
Want to control your Roku from nearly any phone, computer or tablet? Get started at http://help.remoku.tv
by Apps4TV - Applications for television and beyond: http://www.apps4tv.com
0 Kudos
Roku Employee
Roku Employee

Re: retrieve data from mysql

The URL you have in that function is a valid one, it only returns this:

<?xml version="1.0" encoding="utf-8" ?> 
<validacion>
<cliente id="3872" />
</validacion>


To handle that data:


BrightScript Debugger> xfer=createobject("roURlTransfer")
BrightScript Debugger> xfer.seturl("http://www.rodeobiobio.cl/Suscripcion/file.xml")
BrightScript Debugger> test=xfer.gettostring()
BrightScript Debugger> ?test
<?xml version="1.0" encoding="utf-8" ?>
<validacion>
<cliente id="3872" />
</validacion>
BrightScript Debugger> xml=createobject("roxmlelement")
BrightScript Debugger> ?xml.parse(test)
true
BrightScript Debugger> ?xml.cliente@id
3872
BrightScript Debugger>


So your call to your server is probably not returning the data you want. When you get that sorted out (by testing the URLs of your API in a web browser, or by using cURL on the command line), then you will go to the next step of extracting the data from the roXMLElement into variables you can use.

- Joel
0 Kudos

Re: retrieve data from mysql

Messrs, I will test with answers, and I will comment. Thanks you very much! Gracias amigos!! :mrgreen:
0 Kudos