Hi,

I have to deal with a DBISAM database and want to write a code generator for DB classes in c#. For this case I connect to the database and have to list all the tabel names and all the columns (with datatype, primary keys,...).
Until now I've successfuly read out the tabele names with the .NET ODBC function OdbcConnection.GetSchema("Tables"). But I haven't found a way to read the schema informations of a table. Is there a way (maybe a SQL-statement) to read this information for a table, maybe something like this:

SELECT * FROM SysTableColumns WHERE TableName = 'Orders'

or something like that?

Greetings

Hannes

Dear Hannes,

DBISAM doesn't have system tables in the same way that some other SQL databases (such as Elevate's ElevateDB) do. There is no SQL (to the best of my knowledge) which can iterate tables / columns etc.

However there is an extremely rich programming environment with the TDBISAMDatabase, Session, Query etc., objects which you can access in Delphi (I am not sure about accessing them in C#). These objects carry methods and properties which give you access to all the values you need to build the kind of code-framework you are talking about.

DBISAM is a very good low-overhead engine, but it is designed with the expectation that most of the complex work is done in the application, not at the SQL / Engine side of things.

I hope this is helpful.

Adam

Having written my first reply I realise that it is possible to write complex external functions for DBISAM, which might well be able to do what you want on the database side of things rather than the programmatic side of things. I've never used these external functions so I am not sure of their capability ... Roy Lambert uses them a lot & will probably add to this thread fairly shortly with some useful advise.

For the time being have a look at:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=r&version=2007&topic=Customizing_Engine

The "Server Procedures" section, about 3/4s of the way through the article.

Adam

Adam


>Having written my first reply I realise that it is possible to write complex external functions for DBISAM, which might well be able to do what you want on the database side of things rather than the programmatic side of things. I've never used these external functions so I am not sure of their capability ... Roy Lambert uses them a lot & will probably add to this thread fairly shortly with some useful advise.

I would but I'm strictly Delphi, and I switched a long time ago to ElevateDB. My memory says that there's no way to read table structures using SQL in DBISAM (could be wrong though) and you need to use the table and fielddefs to get at them.

Roy Lambert [Team Elevate]

H.Bishof

You can create a custom funcion at engine level that deal with some of this
features but I think you can only get individual information like:

select fieldname(1) from tablex   // You will get the field name from the
the field number 1 of the table x

or
select fieldtype(3) from tablex    // You will get the field datatype from
field 3 of the table x

but

You can create functions to return strings with information separated by
comma or semi-colon or anything you want.
The logic of each function is depending only from you like:

select fieldnames() from tablex  // You will get something like
"code;name;zipcode"

Eduardo

On 21.08.2011 19:57, H. Bischof wrote:
> Hi,
>
> I have to deal with a DBISAM database and want to write a code generator for DB classes in c#. For this case I connect to the database and have to list all the tabel names and all the columns (with datatype, primary keys,...).
> Until now I've successfuly read out the tabele names with the .NET ODBC function OdbcConnection.GetSchema("Tables"). But I haven't found a way to read the schema informations of a table. Is there a way (maybe a SQL-statement) to read this information for a table, maybe something like this:
>
> SELECT * FROM SysTableColumns WHERE TableName = 'Orders'
>
> or something like that?
>
> Greetings
>
> Hannes
>


If you just "prepare" that "select * from ...", can you then find info
for the fields?  Something like Query1.Fields[i].Fieldname or
Fields[i].SQLtype, perhaps.  Haven't tried this in DBISAM, but (IIRC)
works with another database.

--
Aage J.

Aage


>If you just "prepare" that "select * from ...", can you then find info
>for the fields? Something like Query1.Fields[i].Fieldname or
>Fields[i].SQLtype, perhaps. Haven't tried this in DBISAM, but (IIRC)
>works with another database.

That's the same as reading the field defs for a table.

Roy Lambert [Team Elevate]

Hi,

thank you for your reply. I found a way to get the data type an the field name. The ODBC driver gives me the table wich contains these informations. But until now I haven't found a way to read the primary and foreign keys. Seems to me that it doesn't work as easy as I thought.

Greetings

Hannes

I don't use ODBC so I don't know its capabilities. Can you read FieldDefs and IndexDefs for the table?

Roy Lambert [Team Elevate]

Hannes,

<< Until now I've successfuly read out the tabele names with the .NET ODBC
function OdbcConnection.GetSchema("Tables"). But I haven't found a way to
read the schema informations of a table. Is there a way (maybe a
SQL-statement) to read this information for a table, maybe something like
this: >>

You can see all of the schema collections that you can reference for the
ODBC.NET data provider here:

http://msdn.microsoft.com/en-us/library/cc668760.aspx

--
Tim Young
Elevate Software
www.elevatesoft.com