none
ADO.NET OLEDB SQL syntax RRS feed

  • Question

  •  

    I am using the ADO.NET OLEDB class in an application that can load data from any OLEDb supported datasource using a DataReader and then Bulk upload that data to either SQLServer, Oracle or DB2.

     

    I would like to add support for trimming whitespace from the source data using the SQL that queries the source data.(because I pass the DataReader to the BulkLoad class I can't modify the data in code).

     

    Is there any definitive specification for OLEDB sql syntax?  It appears that both the excel provider and text file provider support TRIM in the select statement but I'd like to know if all providers should support this and if not is there any way to query the provider to find out it's SQL capabilities.

     

    Also what other SQL functions are available across OLEDB providers.  So far I haven't tracked down any documents covering this area of OLEDB.

     

     

    Many thanks

     

    Adrian Heath

    Tuesday, July 22, 2008 3:23 PM

Answers

  • I see two options here:

    1) Use a stored procedure or Access QueryDef to handle the trim operation. This way you can use the SQL language function to perform this operation that is specific to the SQL language you are using.

    2) Use a DataSet instead of a DataReader and perform the trim operation on the DataSet before performing the bulk copy to SQL Server, Oracle or DB2. This will likely be slower than the first suggestion but would be completely database agnostic.

    There is no common trim function that would apply to all SQL variations.
    Tuesday, July 22, 2008 4:03 PM

All replies

  • I see two options here:

    1) Use a stored procedure or Access QueryDef to handle the trim operation. This way you can use the SQL language function to perform this operation that is specific to the SQL language you are using.

    2) Use a DataSet instead of a DataReader and perform the trim operation on the DataSet before performing the bulk copy to SQL Server, Oracle or DB2. This will likely be slower than the first suggestion but would be completely database agnostic.

    There is no common trim function that would apply to all SQL variations.
    Tuesday, July 22, 2008 4:03 PM
  • Is there any definitive specification for OLEDB sql syntax?

    I'd be interested in that too.

    I'm using a jet.mdb database, and every so often I find a query that works fine in Access, but doesn't work through ADO.NET OleDB.

    Thursday, September 4, 2008 2:12 PM
  • It would be nice if it worked that way but it doesn't because of the several layers of software that interoperate with one another. For example Microsoft Access is a completely different environment with its own set of supported functionality. It implements the Jet database engine, DAO and built-in functionality, such as the ability to call code level user defined functions from SQL statements.

     

    When using Jet OLEDB, via ADO or ADO.NET, the rules change. There are even differences between ADO and ADO.NET with respect to functionality, despite the fact that they use the same Jet OLEDB provider. The following article may help a bit, which documents some of the differences between DAO (Access) and ADO behavior.

     

    INFO: Issues Migrating from DAO/Jet to ADO/Jet

     

    Fundamental Microsoft Jet SQL for Access 2000

    Thursday, September 4, 2008 3:20 PM
  • When using Jet OLEDB, via ADO or ADO.NET, the rules change.

    I understand that. It would just be nice to know what the rules are.

     

    There are even differences between  ADO and ADO.NET

    That's useful to know, although at the moment I'm only using ADO.NET (VS2008)

    I'm currently using a jet database, but in the future I'm probably going to need to use SQL Server and/or MySQL, so it would also be useful to know the syntax rules for these too. As much as possible I'm avoiding stored queries and procedures and trying to write SQL that is as generic as possible, to minimise any future work needed to support other databases. This is very difficult though, if you don't know what the language differences are until you start getting syntax error messages.

    It would also be useful to know what the rules are for queries that can be used with the TableAdapter Configuration wizard. I spent most of yesterday trying to figure out what was causing an error I was getting. Only eventually to discover that I could just ignore the error, and it actually worked fine when I ran the application.

    See  news:w9CdndP4Up66YSLVnZ2dnUVZ8j2dnZ2d@posted.plusnet

     

    Thanks for the references. I'll check those out.

     

    Cheers,

    Phil.

    Friday, September 5, 2008 8:50 AM
  • The bottom line here is that while a database vendor can conform to an API or a subset of an API, such as ODBC, OLEDB or .NET, the chances that their SQL language will be compatible with other database vendors is fairly remote. In this instance you pretty much have to consult the SQL language implementation documentation of the vendor in order to determine the differences.

     

    The implementation of core ANSI compliant SQL is about as close as you will come with respect to compatibility amongst different database systems, but as I said, every database vendor has their own set of proprietary extensions.
    Friday, September 5, 2008 12:28 PM