StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » QueryInformation: SQL Generated for DB2 is incorrect

RE: QueryInformation: SQL Generated for DB2 is incorrect

StrataFrame Team — Fri, 31 Aug 2007 08:59:14 GMT

[quote]I know more about PHP/MySQL applications than anything else at the moment[/quote]

Mmm, the good old days. That's where I started my SQL exploits. Still to this day, I think LIMIT instead of TOP.

Don't worry about .NET, though, you'll get the hang of it quickly enough.

QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Fri, 31 Aug 2007 08:59:14 GMT

I'm hooking into DB2 and it returns a DB2 error when I start view the ASP page:

[codesnippet][DB2Exception (0x80004005): ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "[ID_SUPPLIER], [NM_SUPPLIER], [CD_COUNTRY], [IND_STATUS] FROM " was found following "SELECT DISTINCT ". Expected tokens may include: "". SQLSTATE=42601][/codesnippet]

In this case, I believe the SQL is being generated incorrectly because of the square brackets around each field name - DB2 doesn't accept these. Are there any options I need to set on the QueryInformation object to change how it generates SQL and for what database?

Thanks in advance!
Peter

RE: QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Thu, 30 Aug 2007 09:49:15 GMT

[quote][b]Ben Chase (08/30/2007)[/b][hr]Excellent. Glad it's working for you :)

As for the objects, we tried to keep the names the same as the corresponding ADO.NET provider objects (except for a few cases where the data source uses OLE DB.

SqlDataSourceItem -> System.Data.SqlClient.SqlCommand

OracleDataSourceItem -> System.Data.OracleClient.OracleCommand

Db2DataSourceItem -> IBM.Data.DB2.DB2Command (technically this one accepts a factory, so you can use whatever provider is optimized for your database type since DB2 has several different versions of their ADO.NET provider and we didn't want to limit you)

VfpDataSourceItem -> System.Data.OleDb.OleDbCommand

AccessDataSourceItem -> System.Data.OleDb.OleDbCommand[/quote]

That's good to know and I'll have to keep it in mind. I'd probably have realized that if I'd worked in .NET technologies prior to now (I know more about PHP/MySQL applications than anything else at the moment).

Thanks!

RE: QueryInformation: SQL Generated for DB2 is incorrect

StrataFrame Team — Thu, 30 Aug 2007 09:18:37 GMT

Excellent. Glad it's working for you :)

As for the objects, we tried to keep the names the same as the corresponding ADO.NET provider objects (except for a few cases where the data source uses OLE DB.

SqlDataSourceItem -> System.Data.SqlClient.SqlCommand

OracleDataSourceItem -> System.Data.OracleClient.OracleCommand

Db2DataSourceItem -> IBM.Data.DB2.DB2Command (technically this one accepts a factory, so you can use whatever provider is optimized for your database type since DB2 has several different versions of their ADO.NET provider and we didn't want to limit you)

VfpDataSourceItem -> System.Data.OleDb.OleDbCommand

AccessDataSourceItem -> System.Data.OleDb.OleDbCommand

RE: QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Wed, 29 Aug 2007 13:25:38 GMT

Well, I've got it sort of working. I created a static SqlHandler object that is initialized when the request is started and retrieves a connection whenever necessary. Originally, I had it returning a SqlDataSourceItem object, but changed that to Db2DataSourceItem. I had though the SqlDataSourceItem was a generic object rather than one specific to SQL Server.

I've now got it connected in and pulling data like it should.

Thanks for your help!

RE: QueryInformation: SQL Generated for DB2 is incorrect

StrataFrame Team — Wed, 29 Aug 2007 10:29:06 GMT

Aha, you're not going through any SF stuff to get there. You're using the System.Web.UI.WebControls.SqlDataSourceView class which is designed to work with SQL Server, so it's creating the query for SQL. DB2 might have a corresponding ASP.NET control. If it doesn't then you might need to fill a DataTable in code and use it to populate the grid (or whatever you're bound to).

RE: QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Wed, 29 Aug 2007 09:38:59 GMT

[quote][b]Ben Chase (08/29/2007)[/b][hr]You are correct both correct.The "identifier delimiter" used by the different databases for special names is like this:

SQL Server: [name]

Oracle: "name"

DB2: "name"

Access: [name]

So, knowing this, we took out all of the [] from the Db2DataSourceItem and replaced them with "". Could you post the stack trace for the error you're getting? It's odd that the Db2DataSourceItem is using [], so I need to track that one down.[/quote]

Here's the stack trace from my error page:

IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1, DB2CursorType A_2, Boolean A_3, Boolean A_4, Boolean A_5, Int32 A_6) +3973
IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1) +47
IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior) +93
IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior) +33
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2464
System.Web.UI.WebControls.Repeater.GetData() +50
System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232
System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53
System.Web.UI.WebControls.Repeater.DataBind() +72
System.Web.UI.WebControls.Repeater.EnsureDataBound() +55
System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

Thanks!

RE: QueryInformation: SQL Generated for DB2 is incorrect

StrataFrame Team — Wed, 29 Aug 2007 09:27:08 GMT

You are correct both correct. The "identifier delimiter" used by the different databases for special names is like this:

SQL Server: [name]

Oracle: "name"

DB2: "name"

Access: [name]

So, knowing this, we took out all of the [] from the Db2DataSourceItem and replaced them with "". Could you post the stack trace for the error you're getting? It's odd that the Db2DataSourceItem is using [], so I need to track that one down.

RE: QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Wed, 29 Aug 2007 07:16:14 GMT

[quote][b]Greg McGuffey (08/28/2007)[/b][hr]Brackets around SQL Server items is OK. This allows for non-compliant names to be used (such as names with spaces or that are key words).

Might be nice if there was a way to configure the data source to not use the brackets... :hehe:[/quote]

Yes, it's OK to use square brackets around fields and tables on SQL Server. I imagine it's OK on Oracle, too (I don't have enough experience to confirm that), but DB2 v9 and DB2 v8 don't allow it. It causes a SQL error to be thrown. I also know of at least one other DB platform (it's not supported by this architecture) that would throw an error.

RE: QueryInformation: SQL Generated for DB2 is incorrect

Greg McGuffey — Tue, 28 Aug 2007 17:25:01 GMT

Brackets around SQL Server items is OK. This allows for non-compliant names to be used (such as names with spaces or that are key words).

Might be nice if there was a way to configure the data source to not use the brackets... :hehe:

RE: QueryInformation: SQL Generated for DB2 is incorrect

PeterA — Tue, 28 Aug 2007 16:24:31 GMT

Some additional information. I was looking at the source code for the SqlDataSourceItem object and it looks like it will have this kind of trouble on the tables as well.

The following segment adds the fields to the query:

[codesnippet]
If QueryInfo.Fields.Count > 0 Then
For Each lcField In QueryInfo.Fields
loQuery.Append("[")
loQuery.Append(lcField)
loQuery.Append("], ")
Next

'-- Remove the last two characters ", "
loQuery.Length -= 2
Else
loQuery.Append("*")
End If
[/codesnippet]

It's adding brackets regardless of the database type. Something similar happens when specifying table names:

[codesnippet]
loQuery.Append(" FROM [")
loQuery.Append(QueryInfo.TableSchema)
loQuery.Append("].[")
loQuery.Append(QueryInfo.TableName)
loQuery.Append("] ")
[/codesnippet]

If the database is DB2 v8 (we've not upgraded to v9 yet, but the DB2 Express v9 on my development PC provides the same error), it can't use brackets around the table names either and the query will fail.

Thanks!

Peter