DynamicForm save not returning record in response? (Pro/EE)

[pmac]

Hi,

I have a DynamicForm that I enter new values into and save. When I run in ClientOnly mode, it saves and returns the record in the DSResponse (via getData()). However, when I run in live mode against my SQL Server DB, the response status is "STATUS_SUCCESS", but the record set is zero-length. The record is correctly created in the DB.

Is this common, or am I missing something?

Thanks.

[Isomorphic]

Can you show the request and response in the RPC tab of the Developer Console, as well as the complete server-side logs for this request.

Also, you forgot to post all relevant versions (including, in this case, your SQL Server version and driver version).

[pmac]

I'm running the app in hosted mode in Eclipse (buildID=20090920-1017) on Mac OS X Snow Leopard. The DB is running in XP Home in a virtual machine on the Mac.

DB: SQL Server 2008 Express
Driver: jtds-1.2.5.jar
SmartGWT EE: v2.1

Sorry, when running in hosted mode, where are the "server logs" you're asking for. Is that the Eclipse console output?

Here's the other info you asked for:

Request:

{
    "actionURL":"http://localhost:8888/tracker/sc/IDACall", 
    "showPrompt":true, 
    "prompt":"Saving form...", 
    "transport":"xmlHttpRequest", 
    "promptStyle":"dialog", 
    "bypassCache":true, 
    "data":{
        "values":{
            "Name":"Test10", 
            "Description":"test item", 
            "ElementTypeID":1, 
            "BoolStartState":"1"
        }, 
        "operationConfig":{
            "dataSource":"Element", 
            "repo":null, 
            "operationType":"add"
        }, 
        "componentId":"isc_OID_50", 
        "appID":"builtinApplication", 
        "operation":"Element_add", 
        "oldValues":{
            "Name":"", 
            "Description":""
        }
    }
}

Response:

[
    {
        isDSResponse:true, 
        invalidateCache:false, 
        status:0, 
        data:[
        ]
    }
]

For what it's worth, I have a custom IDACall:

public class CustomIDACall extends IDACall {
	
	private static final long serialVersionUID = -1483088157905503631L;
	
	public DSResponse handleDSRequest(DSRequest dsRequest, RPCManager rpc, RequestContext context) throws java.lang.Exception {
		String dsDir = getServletContext().getInitParameter("datasource.directory");
 
 		// Get the absolute path on the filesystem for the "ds" relative dir.
		String absoluteDSDir = getServletContext().getRealPath(dsDir);
    	
    	DataSource ds = null;
	    try{
                // Load the data source description from the filesystem
	    	IDataSourceSource dsSource = new FileDataSourceSource(absoluteDSDir);
	    	ds = dsSource.load(dsName);
			dsRequest.setDataSource(ds);
	    }
	    catch (Exception e){
	    }
		
		return super.handleDSRequest(dsRequest, rpc, context);
	}
	
}

[Isomorphic]

Missing the most important thing (server-side logs).

[pmac]

Here's part 1 of 3 of the console output (see attached file).

Note that in part 3, there's an exception:
Attempt to execute RPC DMI Builtin 'getAvailableScriptEngines' DENIED by server.properties configuration

[pmac]

Part 2 of 3 of console output (see attached file).

[pmac]

Part 3 of 3 of console output (see attached file).

[Isomorphic]

Only needed the logs for the specific transaction where you're having trouble. Regardless, here's the problem:

=== 2010-04-21 11:42:07,301 [l0-4] INFO SQLDriver - [builtinApplication.Element_add] Executing SQL update on 'SQLServer': INSERT INTO Element (Name, Description, ElementTypeID, BoolStartState) VALUES ('Test10', 'test item', 1, '1')
=== 2010-04-21 11:42:07,316 [l0-4] DEBUG SQLDataSource - [builtinApplication.Element_add] add operation affected 1 rows
=== 2010-04-21 11:42:07,318 [l0-4] INFO SQLDriver - [builtinApplication.Element_add] Executing SQL query on 'SQLServer': SELECT @@IDENTITY
=== 2010-04-21 11:42:07,319 [l0-4] INFO SQLDataSource - [builtinApplication.Element_add] primaryKeys: {ElementID=62}
=== 2010-04-21 11:42:07,319 [l0-4] INFO SQLDriver - [builtinApplication.Element_add] Executing SQL query on 'SQLServer': SELECT Element.Name, Element.SelectOptions, Element.Description, Element.MaxLength, Element.ElementID, Element.ElementTypeID, Element.MinIntValue, Element.MaxIntValue, Element.MaxFloatValue, Element.BoolStartState, Element.MinFloatValue FROM Element WHERE (Element.ElementID=62)
=== 2010-04-21 11:42:07,321 [l0-4] WARN SQLDataSource - [builtinApplication.Element_add] SQLServer getLastRow(): empty result set fetching last row

We're guessing your primary key (ElementID) is declared as a sequence field. The log shows the SQL subsystem correctly finding out the generated ID of the new record (62). The subsequent select for that record fails with a very straightforward where clause ("WHERE (Element.ElementID=62)").

1. Any idea why that is - something unusual about this table or column?

2. Can you verify that the auto-generated ID is in fact 62?

3. Can you check whether the SQL query that should select the newly inserted record works if run by hand?

[pmac]

Ok, found the problem. I had installed the "AutoAudit" script on the database. It sets up triggers so that any changes to the schema or data is automatically recorded to the DB. Once I removed that, SmartGWT works just fine. I've written to the author of the script to see whether this a known side-effect.

On a related note, I'm interested in maintaining an audit trail of data changes to the DB. If can't use the AutoAudit script, where would you recommend I setup such a function? Should I adapt my CustomIDACall servlet to intercept and log changes between the old and new values? Other suggestions? (Should I start a separate thread on this?)

[Sorry I didn't reply sooner, I didn't receive an email indicating that you'd already replied. Thanks very much for the help!]

[pmac]

I got a reply back from the AutoAudit script's author. He was wondering what you're using to get the last inserted ID? (and hoping it's not @@IDENTITY). Can you provide any insight into this? The audit log indicates the query is a simple INSERT INTO FooTable (...) VALUES (...). I imagine you're using some construct after the INSERT to get the last inserted ID.

Thanks.

[pmac]

Oh, I see in the text above, that you are selecting @@IDENTITY.

Do you adapt your queries based on the specific DB, or is @@IDENTITY selected for every database? Reason being that for SQL Server, the SCOPE_IDENTITY() might be more appropriate (not affected by triggers).

Thoughts? Thanks.

[Isomorphic]

Yes, different approaches for different DBs. It's an area where DBs are extremely inconsistent.

In what version did SCOPE_IDENTITY() become available and does it appear to you to be functionality equivalent, including with respect to transactions?

[pmac]

Reference URL for scope_identity():
- http://msdn.microsoft.com/en-us/library/ms190315.aspx

Looks like it's also available back in SQL Server 2000:
- http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx

This guy compares the three typical options:
- http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

[Isomorphic]

Thanks, that makes sense, we'll switch the strategy.

[pmac]

For anyone else who's following along. The AutoAudit author wrote back to suggest a couple of changes to his script to work with this situation:

1. Remove the identity column from the Audit table (leaving @@IDENTITY to work again because no other identity is created), or
2. Change the definition of the Audit table to have a primary key column defined as:

AuditID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
CONSTRAINT pkAudit PRIMARY KEY,

The NEWSEQUENTIALID column achieves a unique identity-like column but also avoids affecting what @@IDENTITY relies on.

I opted for #2, above, and it works well with SmartGWT. It still sounds like the IDENTITY_SCOPE() call might be a better solution as well (improvement for the SmartGWT server code). The IDENTITY_SCOPE() is also the script author's recommendation, and he's also the author of the "SQL Server Bible" (pretty great reference book, IMHO).