How to USE an Alaska-Software.* table in Xbase code

[rdonnay]

I am making some modification to my SqlQuery source to make it more robust when using PGDBE.

Xbase++ errors when trying to open their Alaska-Software.isam.order table in either ISAM mode or SQL SELECT mode.
My code below shows what I am trying to do.
Has anyone been successful doing this.
There is no problem using PgAdmin to view these tables.

Code: Select all

IF oConnection:type = 'ADS'
  cDbe := 'ADSDBE'
ELSEIF oConnection:type = 'ODBC'
  cDbe := 'ODBCDBE'
ELSEIF oConnection:type = 'PG'
  cDbe := 'PGDBE'
ENDIF

cFileName := Upper(cFileName)

IF cFileName = 'ALASKA-SOFTWARE.ISAM.ORDERS'
  cAlias := 'XBASEORDERS'
ELSEIF cFileName = 'ALASKA-SOFTWARE.ISAM.TABLES'
  cAlias := 'XBASETABLES'
ELSEIF cFileName = 'ALASKA-SOFTWARE.SYSTEM.CONNECTIONS'
  cAlias := 'XBASESYSTEM'
ELSE
  cAlias := Strtran(DC_Path(cFileName,.t.),'.DBF','')
ENDIF

cAlias := Strtran(cAlias,'-','')
IF Len(cAlias) > 10
  cAlias := Substr(cAlias,1,10)
ENDIF

dbeSetDefault(cDbe)

IF Empty(Alias())
  USE (cFileName) VIA (oSession) NEW ALIAS (cAlias)
ELSE
  lCloseWorkArea := .f.
ENDIF

sqlquery.jpg (72.27 KiB) Viewed 4194 times

[Tom]

You can't open those tables in PGDBE mode or with the PGDBE as the default DBE using SQL SELECT. You need to create a query and execute it (in a separate session!), like this:

Code: Select all

cQuery := "DELETE FROM  "+Chr(34)+"alaska-software.isam.tables"+Chr(34)+" WHERE table_name='"+cMyTableName+"'"
oSession:executeQuery(cQuery)
...

[rdonnay]

It's very interesting that a separate session is required for this.

I'll need to experiment.

NyCourts has 1200 users and it will be impossible to have each court manage the data dictionary by an administrator using PgAdmin.
This must always be done in the Xbase++ application.
They will need to add columns and also stored procedures.
Five of their CDX files have an index tag that uses a UDF expression, thus requiring a stored procedure as a replacement.

Much of their code was originally written in Clipper Summer 87 and upgraded using Xbase++ and eXpress++.
This conversion will likely be a hybrid of ISAM and SQL queries.
Actually, I like these kind of challenges and I hope they have the budget for this because I haven't even looked at their code yet, only their data.

[Tom]

We're still not finished with the PGDBE project (Alaska just delivered local filters, which will help us to get across the finish line), but we tried to be consistant and modified all service functions so they can be used from the app and/or our own service applications. Triggers or pgAdmin are not necessary, but we don't mix native SQL with ISAM or so on the database side. We don't use stuff like ALTER TABLE, but try to deal with DbCreate() and the other functions (which are a little slow, to be honest). We only created some SQL-based service functions for deleting tables/indexes, for tests on existing tables/indexes and some others. We don't want to "disturb" the Alaska-ISAM-system.

[rdonnay]

Do you know if we can create an ISAM index on the return value of a stored procedure?

This may be a deal killer if we cannot do it.

I'm expecting that we can use a stored procedure in the ORDER BY clause of a SELECT statement as an alternative.

[Tom]

I tried to work with stored procedures in order to get around the filter problem with the PGDBE (that was a suggestion: use stored procs for all the functions not supported in filters - now we have local filters, which work excellent). I never had the issue to use a stored proc in an ISAM index. Maybe you will find something if you look around in Alaska's "ILX"-forum:

https://ilx.alaska-software.com/index.p ... nd-sql.82/