Open a connection to a database file and read its contents

[kenwalter]

I am brand new to Progress (day 2) and I am trying to create a utility that will allow a user to click a button, choose a database file, and read its contents. I have created a window with a button in AppBuilder and my current goal is to generate a File Chooser Dialog box. This is easily accomplished in C#, but I am having issues with online documentation on how to do this in Progress.

After that task is accomplished, I need to open a connection to this DB file and read its contents to update another database.

Can somebody direct me to some documentation online or maybe set me on the right track of how to do this?

 

[GregTomkins]

This is way too big a question to answer in a forum post, but trying to be helpful ... documentation should be available in your AppBuilder under 'Help' someplace, but failing that, go here http://communities.progress.com/pcom/docs/DOC-16074, find your version, then (given you sound like an experienced developer) I would personally look at 'ABL Reference' first (this is a matter of opinion though).

It will be a long and (for a while) frustrating road though, P4GL is conceptually way way different from C#, I mean, by comparison (say) Java and C# are identical and (say) Java and Python are only different in minor syntactic ways. In a nutshell, P4GL is awesome when it comes to database record activities (imagine SQL buried inside Java and the compiler understanding every nuance of your SQL schema), or Ruby's ActiveRecord, or Hibernate automatically included with the compiler), but is kind of stuck back in early 2000 in most other ways (especially when it comes to tools).

Fortunately its awesomeness as regards databases makes up for everything else, IMHO.

 

[rzr]

for file chooser dialog-box - you can check the help pages for "SYSTEM-DIALOG GET-FILE statement"
connection to database - refer to help pages for CONNECT statement....these should hopefully get you started...
reading contents of db & updating to another db comes next.... and to be honest... you will need more hands-on exp in writing 4gl code / understanding various other key concepts... good luck !

 

[kenwalter]

Thanks, rzr. This is exactly what I did at the end of the day yesterday. I am trying to connect to an old version of a database so that I can copy its info into a new database. I have ensured that so far my code works swimmingly. During my method I connect to the database, do some stuff, then disconnect. This needs to be done at run-time since many of our computers do not have the license to compile code.

I am trying, now, to access the database using ambiguity.

DEFINE VAR cOtsName AS CHARACTER INITIAL "oTSDB". /* old DB logical name */
DEFINE VAR cNtsName AS CHARACTER INITIAL "nTSDB". /* new DB logical name - is already connected at run-time*/
CONNECT VALUE( oldTimeDB:SCREEN-VALUE) -1 -ld VALUE(cOtsName).  
  
/* I want to access tables and records from the old DB here so that they can be transfered to the new DB */

DISCONNECT VALUE(cOtsName).

I can't seem to access these databases dynamically. Is there a way to do this?

 

[tamhas]

Whenever you do a dynamic connect, you need to go one level deeper to use the DB. Run a procedure with the code to access the old DB.

 

[GregTomkins]

IIRC, you cannot have multiple VALUE clauses inside the one CONNECT ... try putting the whole thing (including all the options) inside a single string.

I think TamHas's comment will apply as soon as you get CONNECT to work and try to access the schema.

 

[kenwalter]

I think there may have been some misunderstanding about what I'm trying to do.

I have successfully connected to a DB at run-time. Using queries and handles, I was able to extract information (fields and values). This is already done.

My next goal is to add these records to a new database. This database has all the old tables and fields, plus more, so there should be no problem copying the info over.

Since this is all being done dynamically, I need to access the new database (which does not need a run-time connection, it is already connected), create a new record, and assign the fields appropriately.

 

[GregTomkins]

Oh, OK, I thought you meant that the CONNECT was spewing errors.

I think you need something like this. This syntax is from memory but hopefully you get the idea:

DEF VAR h_table AS C NO-UNDO INIT "database-name-goes-here.table-name-goes-here". /* Must prefix with the logical DB name */
DEF VAR h_bh AS HANDLE NO-UNDO.
CREATE BUFFER h_bh FOR TABLE h_table.
h_bh:BUFFER-CREATE().
h_bh:BUFFER-FIELD("field-name-goes-here"):BUFFER-VALUE("field-value-goes-here"). /* Repeat for however many fields you have */

Of course doing this field-by-field will become tiresome for a lot of fields and tables, so there is also a BUFFER-COPY to copy all fields en masse, as well as ways to access array elements.

If you still have trouble on Monday, I could throw together an example that actually works.

 

[tamhas]

So, you have both DBs connected to the session just fine?
I presume you have given them different logical names?
If you include the logical DB name in the table and field reference, can you access both database?
Or, what exactly is the problem?

 

[kenwalter]

Greg,

That sounds excellent. I do want to copy records across databases. If they are both connected to at run-time, how can I use buffer-copy() to achieve this?

Tamhas,

Both databases are connected. Each has a unique logical name. Unfortunately, I can't access them using explicit statements, i.e., db-name.table-name.field. I have to use queries. If the application is not compiled with references to a particular database, then it is not possible to access it through these explicit statements. I need a dynamic connection. Greg mentioned I could use buffer-copy(). I am copying records from DB1 to DB2, where DB2 has all of the tables and fields of DB1, but also has extra fields. Given that the DB schema are different, will this affect buffer-copy()?

I do not know how to approach this, here is where I am currently at:

DEF VAR oDB AS CHARACTER INITIAL "oldDatabase".
DEF VAR nDB AS CHARACTER INITIAL "newDatabase".
IF NOT CONNECTED(oDB) THEN CONNECT VALUE(/*from open file dialog*/) -1 -ld VALUE(oDB).


DEF VAR qTravelQuery AS CHARACTER.
DEF VAR tableName AS CHARACTER EXTENT.
EXTENT(tableName) = 6.
tableName[1] = "table1".
tableName[2] = "table2".
tableName[3] = "table3".
tableName[4] = "table4".
tableName[5] = "table5".
tableName[6] = "table6".
DEF VAR tableArrayIndex AS INT.
DO  tableArrayIndex = 1 TO EXTENT(tableName).
    DEF VAR hQueryData AS HANDLE.  /*Query*/
    DEF VAR hBufferData AS HANDLE. /*Data*/
    DEF VAR hNewBuffer AS HANDLE.  /*New DB buffer*/


    /* Every record of these 6 tables */
    qTravelQuery = "for each " + oDB + "." + tableName[tableArrayIndex].
    
    CREATE QUERY hQueryData.
    CREATE BUFFER hBufferData FOR TABLE oDB + "." + tableName[tableArrayIndex].
    CREATE BUFFER hNewBuffer FOR TABLE nDB + "." + tableName[tableArrayIndex].
        hQueryData:SET-BUFFERS(hBufferData).
        hQueryData:QUERY-PREPARE(qTravelQuery).
        hQueryData:QUERY-OPEN.
        REPEAT:
            hQueryData:GET-NEXT().
            IF QUERY-OFF-END THEN LEAVE.


            /* take records from oDB and insert into nDB */


        END.
END.

 

[RealHeavyDude]

From the online help

BUFFER-COPY( ) method
This method copies any common fields, determined by name, data type, and extent-matching, from the source buffer to the receiving buffer. If there are fields in one buffer that do not exist in the other, they are ignored. This method is used to accommodate temp-tables of joins.

Other than that you could manually loop through the fields in the source buffer - something like this ( coded in Firefox IDE, therefore not syntax checked ):

DO iField = 1 TO hSourceBuffer:NUM-FIELDS:
IF VALID-HANDLE ( hTargetBuffer:BUFFER-FIELD ( hSourceBuffer:BUFFER-FIELD ( iField ):NAME ) ) THEN DO:
ASSIGN hTargetBuffer:BUFFER-FIELD ( hSourceBuffer:BUFFER-FIELD ( iField ):NAME ) ):BUFFER-VALUE = hSourceBuffer:BUFFER-FIELD ( iField ):BUFFER-VALUE.
END.
END.

Heavy Regards, RealHeavyDude.

 

[rzr]

also... you should "clean-up" *stuff* that you "create".. maybe you are already doing it somewhere down in the logic

 

[kenwalter]

I have been working on other projects lately, but I have solved my issue.

When trying to copy items from an old database to a new one and the connection to the old database is made at run-time, the easiest way to achieve this is through separate queries, one for each DB.

DEF VAR tablename as CHAR INITIAL "table1,table2,table3" /* and so on */

        DEF VAR cTraverseOldTable AS CHARACTER.
        DEF VAR iTableArrayIndex AS INT.
        DO iTableArrayIndex = 1 TO NUM-ENTRIES(tablename,","):
            DEF VAR hQueryOldDatabase AS HANDLE.
            DEF VAR hOldDatabaseBufferData AS HANDLE.
            DEF VAR hNewDatabaseBuffer AS HANDLE.
            
            cTraverseOldTable = "for each " + otsName + "." + ENTRY(iTableArrayIndex,tablename,",").
            CREATE QUERY hQueryOldDatabase.
            CREATE BUFFER hOldDatabaseBufferData FOR TABLE otsName + "." + ENTRY(iTableArrayIndex,tablename,",").
            CREATE BUFFER hNewDatabaseBuffer FOR TABLE ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").
            
            hQueryOldDatabase:SET-BUFFERS(hOldDatabaseBufferData).
            hQueryOldDatabase:QUERY-PREPARE(cTraverseOldTable).
            hQueryOldDatabase:QUERY-OPEN.
            hNewDatabaseBuffer:DISABLE-LOAD-TRIGGERS(FALSE).
            
            COPY-BLOCK:
            REPEAT :
                hQueryOldDatabase:GET-NEXT().
                IF hQueryOldDatabase:QUERY-OFF-END THEN LEAVE.
                
                DEF VAR cTraverse AS CHARACTER.
                DEF VAR cTableIndex AS CHARACTER.
                cTraverse = "for each " + ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").
                DEF VAR hQuery AS HANDLE.
                DEF VAR hBufferData AS HANDLE.
                CREATE QUERY hQuery.
                CREATE BUFFER hBufferData FOR TABLE ntsName + "." + ENTRY(iTableArrayIndex,tablename,",").
                hQuery:SET-BUFFERS(hBufferData).
                hQuery:QUERY-PREPARE(cTraverse).
                hQuery:QUERY-OPEN.

and the block that copies data from old and creates records in new:

           CREATE-BLOCK:
                DO ON ERROR UNDO, THROW:
                    hNewDatabaseBuffer:BUFFER-CREATE() NO-ERROR.
                    hNewDatabaseBuffer:BUFFER-COPY(hOldDatabaseBufferData) NO-ERROR.
                    IF ERROR-STATUS:ERROR THEN DO:
                        LEAVE COPY-BLOCK.
                    END.
                    CATCH someError AS PROGRESS.Lang.ERROR:
                        MESSAGE someError
                            VIEW-AS ALERT-BOX INFO BUTTONS OK.
                    END CATCH.
                /* end CREATE-BLOCK DO */
                END.