PDA

View Full Version : problem with odbc connectivity and mysql relation



djtropez
2013-09-10, 03:12
Good morning, i have established a connection to my database MySQL with ODBC, now i try to established some relations (not the relations between the key field, i need to do a relation one to many) between the grids but i have some difficulties so i need to know what is the best practice to do this one.

Thank you,
Simone

barrymavin
2013-09-10, 03:31
In the section attributes there is an attribute "Automatically relate".

If this is unchecked then you can requery() the Virtual Table in the "ParentDataChanged" delegate.



ca = cursorAdaptor("the_alias_of_your_virtual_table")
ca.requery("amount > 0 and customer='{customers.name}'")
Lianja.getElementByID("id_of_your_grid").refresh()


Notice how i reference the parent section data using the {customers.name} macro which will be expanded in requery().

After requerying the MySQL data you then refresh() the grid by accessing it using Lianja.getElementByID().

Once your grid is refreshed, Lianja will then cascade the sections relationships down the page.

Now after telling you all that, unfortunately the "Automatically relate" attribute was disabled in RC8 but it is available in the latest RC9 build. Assuming you are on Windows we can make this available to you. If you are on linux you will need to wait for it.

djtropez
2013-09-10, 03:57
In the section attributes there is an attribute "Automatically relate".

If this is unchecked then you can requery() the Virtual Table in the "ParentDataChanged" delegate.



ca = cursorAdaptor("the_alias_of_your_virtual_table")
ca.requery("amount > 0 and customer='{customers.name}'")
Lianja.getElementByID("id_of_your_grid").refresh()


Notice how i reference the parent section data using the {customers.name} macro which will be expanded in requery().

After requerying the MySQL data you then refresh() the grid by accessing it using Lianja.getElementByID().

Once your grid is refreshed, Lianja will then cascade the sections relationships down the page.

Now after telling you all that, unfortunately the "Automatically relate" attribute was disabled in RC8 but it is available in the latest RC9 build. Assuming you are on Windows we can make this available to you. If you are on linux you will need to wait for it.



I see that there is the checkbox "automatically relate" but is disabled, i can not change and uncheck the box.... ok i'm waiting but what i can do?

barrymavin
2013-09-10, 04:10
I see that there is the checkbox "automatically relate" but is disabled, i can not change and uncheck the box.... ok i'm waiting but what i can do?

Yes as I mentioned:

"Now after telling you all that, unfortunately the "Automatically relate" attribute was disabled in RC8 but it is available in the latest RC9 build. Assuming you are on Windows we can make this available to you. If you are on linux you will need to wait for it."

djtropez
2013-09-10, 04:13
Assuming you are on Windows we can make this available to you.


What does it means.... i'm sorry but i don't understand so much the english.....i'm in windows you can activate this option for me?

barrymavin
2013-09-10, 04:28
If you are running Lianja on Windows, we can give you access to the RC9 beta. This enables "Automatically relate" allowing you to check or uncheck this checkbox.

djtropez
2013-09-10, 05:44
Yes i have windows... what i have to do i can hav the access? thank you very much i have to developpe an application opensource for the university and i like to use lianja for this.... thank you

yvonne.milne
2013-09-10, 06:04
Hi,

You can access this build now from Downloads/Windows/beta.

Regards,

Yvonne

djtropez
2013-09-10, 06:26
thank you so much, i will send you a report if will be some problems!
Best regards, Simone

djtropez
2013-09-11, 03:24
Good Morning i'm unchecked the automatically related. Now i have two tables in the second one i insert the relations. In the parent i insert the "id"(keyword) of the first table , in the child i insert a numeric fields to have a relation one to many with the firs table but it doesn't work... when i create the relation the data are not related and when i change the value in the first table nothing change in the second table.... can someone can tell me if i make a mistake???

barrymavin
2013-09-11, 03:29
Good Morning i'm unchecked the automatically related. Now i have two tables in the second one i insert the relations. In the parent i insert the "id"(keyword) of the first table , in the child i insert a numeric fields to have a relation one to many with the firs table but it doesn't work... when i create the relation the data are not related and when i change the value in the first table nothing change in the second table.... can someone can tell me if i make a mistake???

When handling manual relationships between sections, you need to requery() in the parentdatachanged delegate of the child section. This is called as you navigate the data in the parent section. My previous post explained how to accomplish this.

djtropez
2013-09-11, 14:45
When handling manual relationships between sections, you need to requery() in the parentdatachanged delegate of the child section. This is called as you navigate the data in the parent section. My previous post explained how to accomplish this.


Please can you giveto me more details, where i have to insert "requery" and the code? is possible to have some photos to help me or a video tutorial?
thanks a lot,
Best regards Simone

Cox
2013-09-11, 16:53
Working through a similar scenario, created a database as an import from MySQL. Create a VT to my MySQL instance.
Create a grid based on the VT table.
Now I try to confirm that an update of MySQL table can be reflected in the lianja app based on the info here.

(running this at the console...numbering on left only indicates the order in which these are run)

1). ca=cursorAdaptor("vtdata") //vtdata is my vt alias name
2). ca.requery("select * from vtdata;") //this I believe is right SQL to use
//(see below actual SQL code when I built the VT marked by :0001)

3). mm = Lianja.getElementByID("secGRID").grid
4). mm.clear() //tried skipping this as well but no joy.
5). mm.refresh()

also tried the following in place of 3,4,5
Lianja.getElementByID("secGRID").refresh()


ref: 0001 SELECT A.ACTOR_ID, B.FILM_ID, C.TITLE FROM actor A, film_actor B, film C where A.ACTOR_ID = B.ACTOR_ID AND B.FILM_ID = C.FILM_ID;


At the console, I can run the sql "select * from vtdata" and this does show the data as a live representation from MySQL.
However the routine to update the grid only upates the heading only and no data.

The SQL at the command/console takes about "5462 records selected in 10ms".

P.S, looking at this with djtropez

barrymavin
2013-09-11, 20:49
Working through a similar scenario, created a database as an import from MySQL. Create a VT to my MySQL instance.
Create a grid based on the VT table.
Now I try to confirm that an update of MySQL table can be reflected in the lianja app based on the info here.

(running this at the console...numbering on left only indicates the order in which these are run)

1). ca=cursorAdaptor("vtdata") //vtdata is my vt alias name
2). ca.requery("select * from vtdata;") //this I believe is right SQL to use
//(see below actual SQL code when I built the VT marked by :0001)

3). mm = Lianja.getElementByID("secGRID").grid
4). mm.clear() //tried skipping this as well but no joy.
5). mm.refresh()

also tried the following in place of 3,4,5
Lianja.getElementByID("secGRID").refresh()


ref: 0001 SELECT A.ACTOR_ID, B.FILM_ID, C.TITLE FROM actor A, film_actor B, film C where A.ACTOR_ID = B.ACTOR_ID AND B.FILM_ID = C.FILM_ID;


At the console, I can run the sql "select * from vtdata" and this does show the data as a live representation from MySQL.
However the routine to update the grid only upates the heading only and no data.

The SQL at the command/console takes about "5462 records selected in 10ms".

P.S, looking at this with djtropez

In fact what you are doing is wrong. Let me clarify.

1. requery( "where_condition" ) e.g.


CREATE VIRTUALTABLE cox CONNSTR 'whatever' AS select * from orders where 1=0

When orders is used in a grid you will see no records as the "where 1=0" is specified.

requery() will requery the ODBC data source with a new where condition. e.g.


ca.requery("customer='smith'")

So... If you use that in a parentDataChanged delegate on the grid section like this. (assuming customers is the parent and orders coming from MySQL is the child):



proc page1_section1_parentDataChanged()
ca=cursorAdaptor("vtdata")
ca.requery("customerid='{customers.id}'") // note the use of { } to build the query before it is executed in MySQL
Lianja.getElementByID("secGRID").refresh()
endproc


2. If all you want to do refresh the grid with the exact same SQL query just do this.



ca.cursorRefresh()
Lianja.getElementByID("secGRID").refresh()


Using ca.requery("select * from vtdata;") is incorrect. Also you don't need to select * from vtdata, that is performing 2 SQL queries, one on the server and one on the client. You should only do that when you want to join disparate data sources e.g. Lianja table to a MySQL table.

Note:

You have two things at play here, the data source which is queried into the local cursor, and the grid which is the "view" on that data.

What Lianja does is provide a visual representation in the UI of what you think of in terms of "How is my data related together" and how do I visualize it in my mind e.g.

(one) customers --> (many) orders --> (many) orderDetails

I hope that helps to clarify how to use Virtual Tables.

djtropez
2013-09-12, 03:16
In fact what you are doing is wrong. Let me clarify.

1. requery( "where_condition" ) e.g.


CREATE VIRTUALTABLE cox CONNSTR 'whatever' AS select * from orders where 1=0

When orders is used in a grid you will see no records as the "where 1=0" is specified.

requery() will requery the ODBC data source with a new where condition. e.g.


ca.requery("customer='smith'")

So... If you use that in a parentDataChanged delegate on the grid section like this. (assuming customers is the parent and orders coming from MySQL is the child):



proc page1_section1_parentDataChanged()
ca=cursorAdaptor("vtdata")
ca.requery("customerid='{customers.id}'") // note the use of { } to build the query before it is executed in MySQL
Lianja.getElementByID("secGRID").refresh()
endproc


2. If all you want to do refresh the grid with the exact same SQL query just do this.



ca.cursorRefresh()
Lianja.getElementByID("secGRID").refresh()


Using ca.requery("select * from vtdata;") is incorrect. Also you don't need to select * from vtdata, that is performing 2 SQL queries, one on the server and one on the client. You should only do that when you want to join disparate data sources e.g. Lianja table to a MySQL table.

Note:

You have two things at play here, the data source which is queried into the local cursor, and the grid which is the "view" on that data.

What Lianja does is provide a visual representation in the UI of what you think of in terms of "How is my data related together" and how do I visualize it in my mind e.g.

(one) customers --> (many) orders --> (many) orderDetails

I hope that helps to clarify how to use Virtual Tables.

thank you i'm sorry but i'm an archeologist and i have not strogs competences so i need of very simple explications (now i think i can do this). Just a question this processus will be also in the stable version?
thank you so much!
Simone

barrymavin
2013-09-12, 04:46
thank you i'm sorry but i'm an archeologist and i have not strogs competences so i need of very simple explications (now i think i can do this). Just a question this processus will be also in the stable version?
thank you so much!
Simone

Yes this functionality will not change.

Cox
2013-09-12, 17:12
I am using RC8 and suggestions and pointers given assisted in understanding.
However, please see the following image, have two comments to make

1). Not sure if this is just a version issue, but my vt tables are not reported correctly when I am in the app/page view.
and in database/data view, where they are reported as only tables. Only one is indicated as vt
dir command however reports all expected resources ok.

2). When you open a section based on the tables, either standard table or vt. The table indicated in uppercase, this can be a bit confusing.

451

yvonne.milne
2013-09-13, 08:03
I can reproduce the hover display behaviour with a virtual table that has no records, but not ones with records. Was there anything different in the way these were created as opposed to vtdata? Do they still show as 'Tables' if you reopen the database or App?

Regards,
Yvonne

Cox
2013-09-13, 08:32
Been tying myself in knots with this, and what I have come up with is first, create app, connect to the database, and while the app and database are in 'connected' mode, go and add the vt's. Now the twist is that in the resultant vt's sql statement, there are around 5462 records, and on each occassion, creating a vt with same sql statement, the total number of records returned (shown in brackets) is different. I am working on a local machine for the database as well and on a fairly decent machine but can't understand this. Will try it all on a different machine once the new release file (promised for today) is available.