Deleting a table column (ADO, Catalog)

[bubak]

Hi please if anybody know, this code is not functionable and I don't know why. I want to delete a column from a table and add a new one:

_CatalogPtr m_pCatalog=NULL;
_ColumnPtr m_pColumn=NULL;
_TablePtr m_pTable=NULL;


(_COnnectionPtr m_pDstConCfg is initilaized OK, because data manipulation with _REcordPtr works fine)

m_pCatalog->PutActiveConnection(variant_t((IDispatch *)m_pDstConCfg));
m_pTable=m_pCatalog->Tables->GetItem(_T("My Table&quot);

m_pColumn=m_pTable->Columns->GetItem(_T("Column 1&quot);

Not functionable(throws some unspecified error):
m_pTable->Columns->Delete(_T("Column1&quot);
m_pTable->Columns->Append(_T("aaa&quot,adVarWChar,220);

 

[williamu]

I don't use ADO but I am familiar with it. So, if you are trying to delete then add a physical column from / to the table you need to use the Alter Table statement, with syntax pertinant to your DB.

This code would only delete a column from the Collection and NOT from the physical table. Also, I thought that Append and Delete were only applicable to the Parameters Collection?

HTH
William
Software Engineer
ICQ No. 56047340

 

[bubak]

When I do change data in RecordsetPtr and after it , I do update() on that recordset object, the data in physical table are beeing changed.
so why would something like that no funnction when deleting columns or other way altering table?

 

[williamu]

Altering the structure of a database is much more 'complicated' than a simple update. If you want to remove a column from the database the database has to ensure that it's safe to delete the column. The column you want to remove may be an index or even a primary key. There may also be be constraints on the column that will prevent the column from being deleted if it is referenced by another table.

If you are using SQL Server for example you need to execute a statement similar to :

alter table table_name drop column column_name ;

Or if you are using SQLOLEDB you can use the ITableDefinition:ropColumn function to do the same.

HTH

William
Software Engineer
ICQ No. 56047340

 

[bubak]

I know, how to do it from sql server query console (alter table...) but I want to do it via ADO, because it has to function also that way.

 

[bubak]

with that sql, sth like

_ConnectionPtr m_pDstConCfg;
....
m_pDstConCfg->Execute(_T("alter table tab1 drop column col1&quot);


functions. I just wanted to use sth like

_CatalogPtr pCatalog
....
pCatalog->PutRefActiveConnection(m_pDstConCfg);
pCatalog->Tables->Item[_T("tab1&quot]->Columns->Delete(_T("col1&quot);

 

[williamu]

Ah, now that I can't answer, since I don't use it. But one can assume that you need to get a pointer to the ITableDefinition interface which will provide you access to the Methods.

It should be noted however, that the dropcolumn requires exclusive access to the table, otherwise it will fail.

HTH
William
Software Engineer
ICQ No. 56047340