Test for a SQL Field name

[Rick Lipkin]

To All

Is there a way to test a SQL table to find out if there is a specific field name .. I need to be able to Alter a table and add a column if the column name I am looking for does not exist ?

I have thought about opening a recordset and with a try catch test if a value exists ??

Try
cTest := oRs:Fields("FieldLookingFor"):Value
Catch
// failed .. so add the column with the Alter table command
End try

The above is not very elegant .. Any suggestions ?

Thanks
Rick Lipkin

[Bayron]

Try this... Found with google
http://snippets.dzone.com/posts/show/4663

[Rick Lipkin]

Bayron

Code: Select all  Expand view

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
   ALTER TABLE TEST ADD TEST_DATE DATETIME
END
 

Interesting .. I was trying to keep from having to Select * to open an entire table .. this is an MS Access table. There is not a schema involved .. just curious how I would adapt this .. prob use a oCn connection rather than a recordset

Let me play with this ..

Thanks
Rick Lipkin

[Rick Lipkin]

To All

Thanks to Enrico and the xHarbour forum .. this seems like the best solution :

> FOR i = 0 TO oRs:Fields:Count - 1
> ? oRs:Fields( i ):Name
> NEXT

I add that the above technique works fine at least using Access, MSSQL and
MySQL (personally tested).

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic


Enrico

Thank you ... I have been thinking about this and it is in two
parts .. the Select statement and the field test .. Found this Sql
statement that will open the table which will always open a eof
recordset ...

Select * from table where 1 = 2

The above logic will open a recordset with no records .. then I can
use your logic to test for the Field names !!

Regards
Rick Lipkin

[reinaldocrespo]

Rick;

At least with Advantage you can do this:

Code: Select all  Expand view

Select * from system.columns where parent = 'tablename'
 

That will return a cursor with all field information from table "tablename".

That's exactly what I'm using to keep table structures updated.


Reinaldo.