Failed to query blob columns from Paradox database via ODBC or OLEDB

• j2bmw

  SSC Enthusiast

  Points: 158

  More actions

  October 15, 2010 at 2:32 pm

  #230529

  Hi there,

  I want to read blob columns from Paradox database using c#. Here is my code:

  DataSet dsUsers = new DataSet();

  string strQuery = "SELECT blobColumn FROM users";

  dsUsers = GetNames(strQuery, "Driver={Microsoft Paradox Driver (*.db )}; Default Dir=<directory>;DBQ=<directory>");

  private DataSet GetNames(String strQuery, String strConnect)

  {

  DataSet dsNames = new DataSet();

  OdbcConnection conOdbc = new OdbcConnection(strConnect);

  OdbcCommand cmdOdbc = new OdbcCommand(strQuery, conOdbc);

  OdbcDataAdapter odaOdbc = new OdbcDataAdapter(cmdOdbc);

  odaOdbc.Fill(dsNames);

  conOdbc.Open();

  conOdbc.Close();

  return dsNames;

  }

  Note that the Paradox driver version is 4.0.

  The code failed to query the blob column. The error is:

  Error code: -2146232009

  Error msg: ERROR [07002] [Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.

  Source: odbcjt32.dll

  Please note that I can query non-blob columns of the table with the same code. I tried placing the blob column after the other non-blob columns, I still got the same error.

  I also tried Microsoft OLE DB provider using the following code. There was also an error:

  "No value given for one or more required parameters."

  System.Data.OleDb.OleDbConnection ParConn;

  DataTable schemaTable;

  ParConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db;"+

  "Extended Properties=Paradox 4.x; Persist Security Info=False");

  System.Data.OleDb.OleDbCommand myCmd = new System.Data.OleDb.OleDbCommand();

  System.Data.OleDb.OleDbDataReader myReader;

  myCmd.CommandType = CommandType.Text;

  myCmd.Connection = ParConn;

  myCmd.CommandText = "SELECT blobColumn FROM users";

  ParConn.Open();

  myReader = myCmd.ExecuteReader(CommandBehavior.SequentialAccess);

  schemaTable = myReader.GetSchemaTable();

  ParConn.Close();

  I noticed the following statement at http://msdn.microsoft.com/en-us/library/aa215269%28SQL.80%29.aspx:

  When using the Microsoft OLE DB provider for ODBC with the SQL Server ODBC driver, all BLOB columns should be arranged after columns with other data types in a source rowset. You can use a SELECT statement to rearrange the BLOB columns to the end of the source rowset. The DTS Import/Export Wizard performs this operation automatically.

  Agasin I tried placing the blob column after the other non-blob columns, I still got the same error.

  I also tried "Import and Export Data" Wizard shipped with SQL Server Management studio. I couldn't import the table with blob columns.

  I think there mst be a way to import blob columns from Paradox database in C#. Any ideas?