Graphics

Is it possible to modify the query that a DataLinq object refers to? I am using the DataLinq object in VisualLogic. The query is a SQL stored procedure which takes a number of arguments. I need to modify the arguments in the query.

If you use the regular datalinq properties of a text object (rather than the one in VL), then yes you can modify it through scripting. In the API you'll find a "datalinq" property of the xpTextObject, and using that you can modify the Row/Column/Table fields (assuming you aren't using "Fixed tables" that are controlled by the datalinq server and cannot be modified from XPression Studio). The text object will then requery the datalinq using the new values; or you can force it by calling RefreshDatalinqs in the xpScene api.

Refer to the XPression SDK in the start menu to see the properties.

#XPression

For example, the DataLinq server has a DataLinq with a query for race results, like this: "race_results 'HOUSE', 'PA', 18".

But, really there are hundreds of races happening, and it's not practical to create a DataLinq for every race. Instead, I want the XPression scene to have combo boxes for the user to select select the the race type (HOUSE, SENATE, GOVERNOR, PRESIDENT), the state ('PA', 'NY', etc), and the district number.

So, the XPression scene must change these arguments to the SQL stored procedure. In this case, I don't believe modifying Row/Column/Table would have the effect of changing the stored procedure arguments in the DataLinq server.

Am I missing something, or is there no way to accomplish this?
#XPression

Hello Jonas,

you can send specific SQL queries to datalinq server but you have to :

• do NOT use "Fixed tables" . This is a configuration in the "Datalinq Server" application .
• in XPression : for the datalinq value "table" bound to your datasource's object : instead of using only the table name, write your own query like "SELECT * from myRaceTable where sate = 'PA' "

The thing is : as soon as you write "SELECT " , Xpression will send your own query and not the default "SELECT * FROM table_name".
You can check that behavior in the Log window of Datalinq Server.

I recommend to use a view instead of a stored procedure : Xpression will be able to use the keywords order by , limit if required.

You can have something like : SELECT * from myRaceView where type = 'HOUSE' and state = 'PA' and district = 18

As Brian explain, you can modify the table property with scripting to dynamically change your filters.

see sample code :

[FONT=courier new]Dim txtObjText as xpTextObject

Dim strParamType as string
Dim strParamState as string
Dim strParamDistrict as string
Dim strQuery as string

strParamType = "HOUSE" ' or retrieved info from another text object
strParamState = "PA" ' or retrieved info from another text object
strParamDistrict = "18" ' or retrieved info from another text object

Scene.GetObjectByName("MyText", txtObjText) ' text object named "MyText" that is bound with datalink

strQuery = "SELECT * from myRaceView where type = '" & strParamType & "' and state = '" & strParamState &"' district = " & strParamDistrict & ";"

txtObjText.Datalinq.Table = strQuery ' update the query sent to datalink server[/FONT]



#XPression

Before changing the query with a script, I simply created a DataLinq entry without a query (so, no fixed table), and I added this DataLinq source to a text field. Then I entered the SQL query into the Table field of the DataLinq source properties along with the correct Row and Column. At this point I see no update to my text object.

So, I guess I first need to know how to setup a DataLinq to a text object where the query is not specified in the DataLinq server.
#XPression

When Table field contains just a table name (say my_table) , then it will send to database this kind of query : "SELECT * from `my_table` limit 0, 200" (mysql syntax)

Xpression allow to manually change the query as soon as it contains the SELECT keyword (followed by a space).
So if Table Field is "SELECT * from my_table where id > 10" , it will send the exact same query to Database

if you put in Table field a query like "Call myprocedure('HOUSE', 'PA', 18")" it won't work.


This video tutorial https://www.rossvideo.com/graphics-s...al-datalinq04/ may help you .
If you have permission to read your database tables and views , you can try to reproduce it.
#XPression

It sounds like what I need is not achievable.
What I need is a way to change my SQL query from inside the XPression scene. The SQL query is a stored procedure.
#XPression

Hi Jonas,
You need to uncheck the "force datalinq to use a fixed table" checkbox on the datalinq server.
Then you can put your SQl query into the table field of the text object.
#XPression

Hi Garner,
"force datalinq to use a fixed table" was unchecked.
#XPression

It would be helpful to get confirmation that this is not achievable. That is, there is no way to change a SQL query from inside the XPression scene when the query is a stored procedure.

Or, if there is another way around this, that would be good to know too.
#XPression

if you are allowed to develop on the database : create a view instead of using your stored procedure, then select from the view with filter, it will work.


#XPression

Can a view be defined with parameters like a stored procedure? For example, MY_VIEW 'x', 'y', 'z'.
#XPression

I think the answer is no, but instead I would write: select * from MY_VIEW where arg1='x' and arg2='y' and arg3='z'

#XPression

That's it !

Copy the code of your stored procedure without the where clause on your parameters to make your view.

The view will return all data, but you will be able to filter with select * from MY_VIEW where arg1='x' and arg2='y' and arg3='z' in Xpression.





#XPression

Is there any trick to getting a simple SELECT statement to work when entered into the Table field in the DataLinq object properties in XPression?
The DataLinq source it refers to has "force datalinq to use a fixed table" unchecked.
#XPression

When I enter "select * from MY_TABLE" into the Table field, the Browse button returns nothing.
However, when I enter simply "MY_TABLE", Browse returns the table.
How were you able to use "select * from..."? I will need this when I want to specify arguments with "select * from MY_VIEW where...".
#XPression

with SELECT in uppercase it should be better
#XPression

Thank you
#XPression

Final (I think) note. The suggestion of using a SQL view will not work in my case, I think. I have never used views so I'm not 100% sure this is true. But, from my googling it looks like a view cannot call a stored procedure.
#XPression