problem with odbc connectivity and mysql relation

[barrymavin]

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]

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]

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]

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]

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]

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]

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.

[yvonne.milne]

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]

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.