By PeterA - 8/28/2007
I'm hooking into DB2 and it returns a DB2 error when I start view the ASP page:
[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]
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
|
By PeterA - 8/28/2007
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:
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
It's adding brackets regardless of the database type. Something similar happens when specifying table names:
loQuery.Append(" FROM [")
loQuery.Append(QueryInfo.TableSchema)
loQuery.Append("].[")
loQuery.Append(QueryInfo.TableName)
loQuery.Append("] ")
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
|
By Greg McGuffey - 8/28/2007
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... 
|
By PeterA - 8/29/2007
Greg McGuffey (08/28/2007)
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...
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.
|
By StrataFrame Team - 8/29/2007
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.
|
By PeterA - 8/29/2007
Ben Chase (08/29/2007)
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.
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!
|
By StrataFrame Team - 8/29/2007
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).
|
By PeterA - 8/29/2007
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!
|
By StrataFrame Team - 8/30/2007
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
|
By PeterA - 8/30/2007
Ben Chase (08/30/2007)
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
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!
|
By StrataFrame Team - 8/31/2007
I know more about PHP/MySQL applications than anything else at the moment 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.
|