Tips with calling a stored procedure

[b.kazybaev]

smartgwtpower-3.1p_20.12.2012

We have a stored procedure sp_FillPropertObj1 which returns a table.
Our fillPropertObj.ds.xml:

 <DataSource ID="fillPropertObj" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="SQLServer" schema="dbo">    
    <operationBindings>    	 
        <operationBinding operationType="fetch">  
            <customSQL>
            	call sp_FillPropertObj1(205024)
            </customSQL>  
        </operationBinding>  
    </operationBindings>        
</DataSource>

A snippet of java code:

final ListGrid listGridInner = new ListGrid();
listGridInner.setWidth("50%");
listGridInner.setHeight(230);
listGridInner.setAlternateRecordStyles(true);
listGridInner.setDataSource(DataSource.get("fillPropertObj"));
listGridInner.setAutoFetchData(true);

Did I miss something?

[Isomorphic]

What's going wrong?

Make sure you always post a server log if you're asking about a server-side issue.

[b.kazybaev]

How .ds.xml should looks like if you just call stored procedure which returns a table?

I have attached all logs below.

[b.kazybaev]

just added "fillPropertObj" in

<script src="register/sc/DataSourceLoader?dataSource=fillPropertObj"></script>

fillPropertObj.ds.xml looks like:

<DataSource ID="fillPropertObj" dataFormat="iscServer" serverType="sql"
dataSourceVersion="1" dbName="SQLServer" schema="dbo">
    <operationBindings>
	<operationBinding operationId="customFetch"
      	    operationType="fetch">
	        <customSQL>
		    call sp_FillPropertObj1(205024)				
		</customSQL>
	</operationBinding>
    </operationBindings>
</DataSource>

Java code:

final ListGrid listGridInner = new ListGrid();
listGridInner.setWidth("50%");
listGridInner.setHeight(230);
listGridInner.setAlternateRecordStyles(true);
listGridInner.setDataSource(DataSource.get("fillPropertObj"));
listGridInner.setAutoFetchData(true);

Now log look like this:

[Isomorphic]

We can't read that error message, presumably you can.

What we're doing is creating and executing a JDBC Statement with your stored procedure call. We expect a ResultSet to come back for a fetch.

[b.kazybaev]

Hi,
The issue is still actual.
The snippet of java code:

treeGrid.addLeafClickHandler(new LeafClickHandler() {
					public void onLeafClick(LeafClickEvent event) {
						final ListGrid listGridInner = new ListGrid();
						listGridInner.setWidth("50%");
						listGridInner.setHeight(230);
						listGridInner.setAlternateRecordStyles(true);
						listGridInner.setDataSource(DataSource.get("fillPropertObj"));
						String obj_base = event.getLeaf().getAttribute("obj_base");
						Criteria criteria2 = new Criteria();
						criteria2.addCriteria("obj_base", obj_base);
						listGridInner.fetchData(criteria2);

The .ds.xml and server-side log in attachment.

Actually, the stored procedure fillPropertObj1 returns a table. But Server-Side log shows error: "Incorrect syntax near the construction".
Am I missed something?

[Isomorphic]

The server log doesn't show the SQL generated or several other messages that are normally logged and are very important for troubleshooting - it looks like you have turned off some of the default logging, and in order to help, we need to see the full log with the default log settings we ship with the product.

[bbruyn]

Not that I know better than Isomorphic, I clearly do not. That said...

You have no fields defined in your datasource. I guess I don't know what I'd expect to happen, but it probably isn't helping. :-)

If your procedure actually "returns a table" (as in select * from someTable?) you could probably get away with tableName and autoDeriveSchema. Otherwise define the fields you're expecting to be returned.

[Cal]

Not sure if it makes a difference but I use SQLServer and my calls look like:

<customSQL>EXEC storedProcedureName value1, value2, value3</customSQL>

[b.kazybaev]

The server log doesn't show the SQL generated or several other messages that are normally logged and are very important for troubleshooting - it looks like you have turned off some of the default logging, and in order to help, we need to see the full log with the default log settings we ship with the product.

In fact, the server log shows SQL generated message!

=== 2013-01-18 19:57:38,261 [l0-4] INFO  SQLDriver - [builtinApplication.fillPropertObj_fetch] Execute of select: call sp_FillPropertObj1(213272) on db: SQLServer threw exception: com.microsoft.sqlserver.jdbc.SQLServerException: Неправильный синтаксис около конструкции "213272". - assuming stale connection and retrying query.

I have translated the statement: "Неправильный синтаксис около конструкции" from Russian to English "Incorrect syntax near the construction".
I didn't turn off default logging, but because your system allows attach only very little size .txt file I cutted unnecessary log. I can't believe that simple man solved my issue (Cal), when Isomorphic engineers can't do it. Since we bought the support, we require that you have been more responsible in future!

@Cal, thank you very much! When I changed

<customSQL>
    call sp_FillPropertObj1($criteria.obj_base)
</customSQL>

to

<customSQL>				
    EXEC sp_FillPropertObj1 $criteria.obj_base 						
</customSQL>

,
and added

<fields>
    <field name="gritem_name" title="Атрибут"></field>
    <field name="value" title="Значение"></field>
</fields>

it started to work.

[b.kazybaev]

Now there is another question:

How to call another stored procedure when a user edited a ListGridField and pushed "Enter" key?

What we have done:

ListGridField gritem_name = new ListGridField("gritem_name");
ListGridField value = new ListGridField("value");
listGridInner.setFields(gritem_name, value);
listGridInner.setDataSource(DataSource.get("fillPropertObj"));
String obj_base = event.getLeaf().getAttribute("obj_base");
Criteria criteria2 = new Criteria();
criteria2.addCriteria("obj_base", obj_base);
listGridInner.fetchData(criteria2);
value.setCanEdit(true);
listGridInner.setEditEvent(ListGridEditEvent.CLICK);
//after user edited and pushed the "Enter" key some stored procedure should be called.

[Isomorphic]

Take a look at the Grid Editing overview - there are various ways to control when the grid saves, or to trigger manual saves. On saving, the grid will automatically submit "update" and "add" requests to its DataSource.

[b.kazybaev]

A snippet code

final ListGrid listGridInner = new ListGrid();
listGridInner.setWidth("50%");
listGridInner.setHeight(340);
listGridInner.setAlternateRecordStyles(true);						
ListGridField gritem_name = new ListGridField("gritem_name");
final ListGridField value = new ListGridField("value");
listGridInner.setFields(gritem_name, value);
listGridInner.setDataSource(datasource);
String obj_base = event.getLeaf().getAttribute("obj_base");
Criteria criteria2 = new Criteria();
criteria2.addCriteria("obj_base", obj_base);
listGridInner.fetchData(criteria2);						
listGridInner.addRecordClickHandler(new RecordClickHandler() {
public void onRecordClick(RecordClickEvent event) {
String str = event.getRecord().getAttribute("typpropert");								
if (!str.equals("4") && !"5".equals(str)) {
value.setCanEdit(true);									
listGridInner.setEditEvent(ListGridEditEvent.CLICK);									
value.addCellSavedHandler(new CellSavedHandler() {
public void onCellSaved(CellSavedEvent event) {
String new_value = value.getValueField();
Criteria criteria3 = new Criteria();
criteria3.addCriteria("new_value", new_value);		
}
});									
} else {
value.setCanEdit(false);
}
}
});

We have multiple operationBindings in one Datasource. (See an attachment)
How to call particular operation using operationId?
API and Quick Start Guide doesn't help.

[Isomorphic]

Is something wrong with listGrid.setFetchOperation()? That would be the normal way to do this.

[b.kazybaev]

You mean do like this? :

listGridInner.addRecordClickHandler(new RecordClickHandler() {
	public void onRecordClick(RecordClickEvent event) {
		final String str = event.getRecord().getAttribute("typpropert");								
		if (!str.equals("4") && !"5".equals(str)) {
			value.setCanEdit(true);									
			listGridInner.setEditEvent(ListGridEditEvent.CLICK);									
			value.addCellSavedHandler(new CellSavedHandler() {
				public void onCellSaved(CellSavedEvent event) {
					String new_value = value.getValueField();
					Criteria criteria3 = new Criteria();
					criteria3.addCriteria("new_value", new_value);											
					if (str.equals("1")) {
						listGridInner.setUpdateOperation("ObjUpdAttrib");
					} else if (str.equals("2")) {
						listGridInner.setFetchOperation("ObjListFactor");
						listGridInner.setUpdateOperation("ObjUpdFactor");
					} else if (str.equals("3")) {												
						listGridInner.setUpdateOperation("ObjUpdMeter");
					}											
				}
			});									
		} else {
			value.setCanEdit(false);
		}
	}
});