Joining tables for listgrid datasource

[azuniga]

I'm currently running the nightly build of smartgwtpower-2.5 with gwt 2.3. I've set up a datasource for a listgrid using a single Oracle database table and I want to know if it is possible to create a datasource using the visualBuilder that joins 2 or 3 tables and makes it as a single datasource for a list grid. In other words, to perform a query like select table1.columna, table2.columnb, table3.cloumnc from table1, table2, table3 where etc..... Is this possible? Also, I discovered an issue with my orcale datasource. I was getting an ORA-00904 error and it was b/c the query contained the schema name before the table name, i.e. select * from schema.table where ('1' = '1'). The way I fixed this is to remove schema from the .ds.xml file.

[Isomorphic]

Yes, it's easy to create a DataSource that does a join - see the QuickStart Guide, Server Framework chapter, and focus particularly on the sections on SQL and SQL Templating.

We've never had a report of the schema vs table name in the wrong order, if you can show us a test case that would let us reproduce the problem, that would be great.

[azuniga]

Thank you, that sounds like pleasant news. I'll go ahead and read that section.

[azuniga]

I added operation binding to my ds.xml file, however, the tableclause and the whereclause doesn't get inserted into the sql query performed. I'm setting the autofetch to true for the listgrid and setting initial criteria to it, just to filter by date. Here is what the ds.xml file looks like:

<DataSource ID="Prod" dataFormat="iscServer" serverType="sql" 
dataSourceVersion="1" dbName="Oracle" tableName="P">
    <fields>
        <field name="TIMESTAMP" type="datetime"/>
        <field name="VALUE" type="float" tableName="P_A"/>
        <field name="NAME" type="text" length="40" tableName="P_A_T"/>
    </fields>
   
    <operationBinding operationType="fetch">
    	<tableClause>P, P_A_T, P_A</tableClause>
    	<whereClause>P.P_ID == P_A.P_ID AND
    	P_A.P_TYPE_ID == 
    	P_A_T.P_TYPE_ID AND
    	($defaultWhereClause)
    	</whereClause>
    </operationBinding>
    <generatedBy>SC_SNAPSHOT-2011-09-15/PowerEdition Deployment 2011-09-15</generatedBy>
</DataSource>

Is there anything I need to do besides this? I need the default query to include the table & where clauses.

Also, about the schema thing, when I try to add a table in the visual builder, I get the ORA-00904 error and I can't see any data from the database. I have to manually go and remove the schema="" from the .ds.xml file.

=== 2011-09-20 21:19:24,275 [l0-3] DEBUG AppBase - [builtinApplication.P_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2011-09-20 21:19:24,275 [l0-3] DEBUG AppBase - [builtinApplication.P_fetch] No public zero-argument method named '_P_fetch' found, performing generic datasource operation
=== 2011-09-20 21:19:24,276 [l0-3] INFO SQLDataSource - [builtinApplication.P_fetch] Performing fetch operation with
criteria: {} values: {}
=== 2011-09-20 21:19:24,276 [l0-3] INFO SQLWhereClause - [builtinApplication.P_fetch] empty condition
=== 2011-09-20 21:19:24,276 [l0-3] INFO SQLDataSource - [builtinApplication.P_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2011-09-20 21:19:24,276 [l0-3] DEBUG SQLDataSource - [builtinApplication.P_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2011-09-20 21:19:24,277 [l0-3] DEBUG SQLDataSource - [builtinApplication.P_fetch] Eval'd row count query: SELECT COUNT(*) FROM P WHERE ('1'='1')

[Isomorphic]

Missing <operationBindings> container tag around <operationBinding> tags.

[azuniga]

Inserting the <operationBindings> tag worked. What can I do about duplicate name fields from different tables? For example:

 
<field name="Name" type="text" length="10" title="Plant Name" tableName="Plant"/>
<field name="Name" type="text" length="10" title="Color Name" tableName="Color"/>

I read in the quickStart that the later field gets saved, but is there any way to keep both of them?

[claudiobosticco]

well...simply use different names :-)

[Isomorphic]

Give the field a distinct name, then set DataSourceField.nativeName to "Name" so the generated SQL still uses "Name".

[azuniga]

Give the field a distinct name, then set DataSourceField.nativeName to "Name" so the generated SQL still uses "Name".

Thank you. This worked! Any way to mark this thread closed?

[Isomorphic]

We don't have a formal mechanism but some people like to prepend "Solved: " to the subject.