How can I get schema information about a UniData/UniVerse database?

Question

I have some (limited) experience with UniData and UniVerse databases and now wanting to work with them via the .NET framework. It appears that the way to work with them now is via Rocket Software's U2 Toolkit for .NET.

Since I will be accessing databases whose schemas I won't know a priori, I need to know how to query the databases not just for data (that will come later), but for information about the databases themselves, such as the names of existing tables and their schemas.

It looks like maybe Rocket (or maybe it was IBM who owned the U2 technology previously) has some of that functionaity built into some of their utilities, but I really need to be able to do this programatically.

Any ideas?

Answer 1

You can access U2 Database (UniData or UniVerse) using U2 Toolkit for .NET the following ways:

1. SQL Access (UCI Server)
2. Native Access (UO Server)

SQL Access

For SQL Access, you need to normalize U2 Account (getting schema). For this, you can use the following tools:

1. HS.ADMIN (for UniVerse Database) (http://www.rocketsoftware.com/u2/products/u2-clients-and-db-tools/u2-resources/universe-11.1-clients/copy_of_uvodbc-v11r1.pdf/view)
2. VSG (for UniData Database)
3. MDM ( for UniVerse Database and UniData Database)

You can use U2 Toolkit for .NET’s U2 Database Provider for .NET (ADO.NET Provider) for SQL access

Native Access

For Native Access, you do not need to do anything.

You can use U2 Toolkit for .NET’s UO API for Native Access.

Sample Code and MSDN Style Help

There are tons of sample code on SQL Access and Native Access when you install the product.

The best thing for you is to install U2 Toolkit for .NET V 1.2.1 and start developing some code. You can run almost all samples as it uses sample database ( ‘HS.SALES’ UniVerse Account and ‘demo’ UniData Account)

You can also read U2 Toolkit for .NET V 1.2.1’s MSDN Style Help for information such as Architecture, Account Accessible/Getting Schema, Sample Code etc.

I have tested U2Connection Class’s GetSchema() with UniData’s demo account. It works for me. See screen shot below.

private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
            conn_str.UserID = "user";
            conn_str.Password = "pass";
            conn_str.Server = "localhost";
            conn_str.Database = "demo";
            conn_str.ServerType = "UNIDATA";
            conn_str.Pooling = false;
            string s = conn_str.ToString();
            U2Connection con = new U2Connection();
            con.ConnectionString = s;
            con.Open();
            this.textBox2.AppendText("Connected......."+Environment.NewLine);
            this.textBox2.AppendText("CALLING .......   DataTable dt = con.GetSchema(\"Tables\");"+Environment.NewLine);
            DataTable dt = con.GetSchema("Tables");
            this.dataGridView1.DataSource = dt.DefaultView;
            con.Close();
                        }
        catch (Exception e2)
        {

            this.textBox2.AppendText(e2.Message);
        }
    }

It looks like your ‘demo’ account is not normalized. Can you run “sql> select * from SQLTables;” from TCL Command. Do you see the following? If not, then you can do one of the following:

1. Run VSG Tool (Read VSG Manual)
2. Run MDM Tool (Read MDM Manual)

3. Run Command line from TCL Command:

    o    Convert.sql STUDENT (Read Unidata  Manual for convert.sql command)
    o    Grant privilege
    o    MIGRATE.SQL
   

Answer 2

U2 Toolkit for .NET v1.3.0 supports "ForeignKeys" and "ForeignKeysColumns" in GetSchema() API. See below Fig1, Fig2, Fig3 and Fig4.