Announcement

**leeyuiwah** · 1 Apr 2013, 15:41

MySQL's concat() also did not appear to work in customSelectExpression

Hi,

I also just tried MySQL's concat() function and it did not appear to work either. (The values in the visual component were blank, which I think mean null value).

Code:

concat(t.realm_name, ' (REALM)') realmNameExpanded

Again, there were no complains about syntax errors, and other fields could be fetched. So the SQL statement seemed to have executed fine.

**Isomorphic** · 1 Apr 2013, 16:59

You can see the resulting SQL query, with your modifications caused by SQL templating, right in the log.

It executes the same way it would if you ran it in MySQL, so if it's not doing what you hoped, the next step is to analyze the actual SQL - SmartGWT is no longer part of the processing.

**leeyuiwah** · 1 Apr 2013, 18:41

Could run the query printed in the log, but in visual component the ifnull() failed

Dear Isomorphic,

Thanks for the quick reply.

When you say the log file, is it showing the content that I printed above (in #1)? (This log content I obtained from the "Console" tab of my eclipse)

In the log, I could see the MySQL SQL statement expanded correctly by SmartGWT, and if I ran it with a MySQL GUI (Workbench), the statement generated correct result for all fields -- including the field with the ifnull() function. Here let me the query:

Code:

SELECT 
                t.id, t.date, t.application, da.app_name, t.realm_name, t.num_subscribers, t.upBytes
                , ifnull( da.app_name, 'need decoding'
                        ) as appDecoded
            
             FROM 
                leecy_top_apps as t left outer join app_table as da on t.application = da.app_id
             WHERE 
                true or t.application >= (2<<24) 
            
             LIMIT 76, 76

However, in SmartGWT, the field with the ifnull() function become null. A similar test with the MySQL concat() function gave the same result (null value).

**Isomorphic** · 1 Apr 2013, 19:04

You've showed the customSelectExpression, but not the <field> definition and surrounding DataSource. If the field isn't named "appDecoded" for example, that would be a problem.

**leeyuiwah** · 1 Apr 2013, 19:24

my ds.xml

Here is my ds.xml, the three columns that involve MySQL ifnull() and concat() functions all display null values for all rows.

Code:

<DataSource
    ID="topAppsRealmsDaily"
	serverType="sql"
	tableName="leecy_top_apps"
>
    <fields>
        <field name="id"                  title="ID"
                 type="integer" primaryKey="true" required="true" customSelectExpression="t.id"/>
        <field name="date"                title="Date"
                 type="integer" customSelectExpression="t.date" />
        <field name="application"         title="App Id (Raw)"
                 type="integer" customSelectExpression="t.application" />
        <field name="app_name"            title="App Name "
                 type="text" customSelectExpression="da.app_name" />
        <field name="realm_name"          title="Realm"
                 type="text" customSelectExpression="t.realm_name"/>
        <field name="app_name_decoded1"   title="App Name (1)"
                 type="text" customSelectExpression="appDecoded" />
        <field name="app_name_decoded2"   title="App Name (2)"
                 type="text" customSelectExpression="ifnull( da.app_name, 'need decoding')" />
        <field name="realm_name_expanded" title="Realm (E)"
                 type="text" customSelectExpression="realmNameExpanded"/>
    </fields>

    <operationBindings>
        <operationBinding
          operationType="fetch"
        >
            <selectClause><![CDATA[
                t.id
                , t.date
                , t.application, da.app_name, t.realm_name
                , concat(t.realm_name, ' (REALM)') as realmNameExpanded
                , ifnull( da.app_name, 'need decoding') as appDecoded
            ]]>
            </selectClause>
            <tableClause>
                leecy_top_apps as t left outer join app_table as da on t.application = da.app_id
            </tableClause>
        </operationBinding>
    </operationBindings>
</DataSource>

One more note: I am not using a static ds.xml file as such. Instead, the above XML content is loaded dynamically using DataSource.fromXML()

**Isomorphic** · 1 Apr 2013, 22:34

This doesn't match the logs.

What you need to do is ensure that if you use as an "as" expression, it matches the field name.

**leeyuiwah** · 2 Apr 2013, 09:24

in XML field's name must match what is after AS

Thanks for your tip ! I got it working now.

I rewrote my field definition by making sure the the "name" matches what is after AS in the selectClause. And now I could see the column with MySQL function concat() (or ifnull() for that matter)

In my <selectClause>

Code:

concat(`t`.`realm_name`, ' (REALM)') as realmNameExpanded

In my field definition

Code:

<field name="realmNameExpanded" title="Realm (E)"
                 type="text" />

I think I got confused thinking that SmartGWT was trying to match 'customSelectExpression' in the field definition with what is after AS. Now I realize it is trying to match 'name' with what is after AS. So in the following, the 'customSelectExpression' is actually not used.

Code:

<field name="realm_name"          title="Realm"
                 type="text" customSelectExpression="t.realm_name"/>

Thanks for your help!

Announcement

SQL templating with MySQL's ifnull() function?

Comment

Comment

Comment

Comment

Comment

Comment

Comment