Re: Creating tables

From: Peter E.C Dashwood (dashwood_at_enternet.co.nz)
Date: 10/13/04

  • Next message: ppeggie: "Re: Huon / Endevor Configuration Management position in San Francisc" 
    Date: Thu, 14 Oct 2004 01:20:53 +1300

    "Dionisis Vrionis" <diovr@dksoft.gr> wrote in message
    news:ckg68k$1km9$1@ulysses.noc.ntua.gr...
    > I Have a database named test.mdb without tables.
    > How can i create a table in a database from netcobol.
    >
    What Richard wrote is true, however, there is more than one way to skin a
    cat, and tables can be created by more than just embedded SQL.

    With a little imagination, some knowledge if the DB object model, and an OO
    COBOL compiler (like NetCOBOL), you can certainly create tables "on the
    fly".

    Here's how....

     ENVIRONMENT DIVISION.
     REPOSITORY.
         CLASS COM-EXCEPTION AS "*COM-EXCEPTION"
         CLASS COM AS "*COM".
     DATA DIVISION.
     WORKING-STORAGE SECTION.
     01 DAO-connection pic x(8192) value "DAO.DBEngine.36". *> Make sure this
    is the COM ProgID of the

    *> the version of ACCESS or DAO you have on

    *> your system. Search the registry for DAO...

    *> (Data Access Objects)
     01 OBJ-DBEngine OBJECT REFERENCE COM.
     01 OBJ-DAOCONNECTION OBJECT REFERENCE COM.
     01 OBJ-Database OBJECT REFERENCE COM.
     01 NAME-DB PIC X(whatever) value "Name of the DB you want to access (can
    be ODBC DSN)".
    * [You might want to pass this through Linkage...]

     01 create-stuff pic x( whatever) value "CREATE TABLE tablename (field,
    field, ...) CONSTRAINTS etc".

    ======================== Examples of valid CREATE entry (valid for SQL
    Server and ACCESS 2003)=============
    You could set up "create-stuff" to contain the following...

    CREATE TABLE jobs
    (
       job_id smallint
          IDENTITY(1,1)
          PRIMARY KEY CLUSTERED,
       job_desc varchar(50) NOT NULL
          DEFAULT 'New Position - title not formalized yet',
       min_lvl tinyint NOT NULL
          CHECK (min_lvl >= 10),
       max_lvl tinyint NOT NULL
          CHECK (max_lvl <= 250)
    )

    It will create the table as described.

    If you want a temporary table try...

    CREATE TABLE #tempTable (tempField int... etc)

    Here's the offical story on temporary tables...

    Temporary Tables
    You can create local and global temporary tables. Local temporary tables are
    visible only in the current session; global temporary tables are visible to
    all sessions.

    Prefix local temporary table names with single number sign (#table_name),
    and prefix global temporary table names with a double number sign
    (##table_name).

    SQL statements reference the temporary table using the value specified for
    table_name in the CREATE TABLE statement:

    CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) (this would be one EXECUTE
    statement)
    INSERT INTO #MyTempTable VALUES (1) (This would be another EXECUTE
    statement)
    ============================================================================
    =================================

     01 Temp-text pic x(50).
     01 connection-flag pic x value '1'.
         88 no-connection value zero.
         88 connected value '1'.
     PROCEDURE DIVISION. [using databaseName, TableName perhaps...]
     DECLARATIVES.
     ERR SECTION.
         USE AFTER EXCEPTION OLE-EXCEPTION
         move spaces to DB-path
         set no-connection to TRUE
         .
     END DECLARATIVES.
     main section.
         set connected to TRUE
         perform a-section
         .
     return-to-caller.
         exit program.
    *==============================================================
     a-section section.
     a000.
    *
    * Establish COM Object references to the specified
    * ACCESS Database using DAO.
    *
         invoke COM "CREATE-OBJECT"
                    using DAO-connection
              returning OBJ-DBEngine
         end-invoke

         invoke OBJ-DBEngine "OpenDatabase"
                     using NAME-DB
              returning OBJ-Database
         end-invoke
    *
    * Check we got to the DB OK...
         if no-connection
             yada-yada-yada...

    * Now you can use the EXECUTE method of the Database to create your new
    table...

           invoke OBJ-Database "Execute"
                      using create-stuff
           end-invoke
    ....
    a999.
        exit.

    The above is not definitive, but it has been put together from currently
    working code. Hopefully there's enough here to get you going.

    Pete.

  • Next message: ppeggie: "Re: Huon / Endevor Configuration Management position in San Francisc"

    Relevant Pages

    • Object reference not set to an instance of an object.
      ... Object reference not set to an instance of an object. ... An unhandled exception occurred during the execution of the ... Dim myJobRequest As New skillsDB.jobRequest ... Session= "The skills database has been updated ...
      (microsoft.public.dotnet.general)
    • Re: "Dynamic" form -- how to?
      ... I want to populate ddGender with data from the database, ... Result: " System.NullReferenceException: Object reference not set to an ... I'm guessing that I need to loop through the ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Change ImageButton visible property in code dynamically
      ... In the ItemDataBound event handler check the viability of the database ... > Dim row As DataRow ... > Not suprisingly I am getting the error "Object reference not set to an ...
      (microsoft.public.dotnet.framework.aspnet)
    • OracleDataAdapter Help
      ... Hi, I had developed 2 solutions with Oracle 9i as my database, using ... I started getting object reference not set to an instance of an ... until I gave both the OracleDataAdapter.dll and the .exe console app ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Creating tables
      ... >How can i create a table in a database from netcobol. ... And now an example in ADO to create tables ... Uses the command and the connection object to create two tables. ...
      (comp.lang.cobol)