How to create a temporary dbf file by copying the structure of a POSTGRESQL table

[Diego Euri Almanzar]

In a session with POSTGRESQL via PGDBE, it has been impossible for me to obtain a structure of any table, to create temporary files that I need in reports.

I have done the following test:

SELECT("PGTABLE")
// COPY STRUCTURE TO (DBFSTRUC)
DBCOPYEXTSTRUCT(DBFSTRUC)
USE (DBFSTRUC) VIA FOXCDX

DC_DBEDIT()

DBFCREATEFROM(NEWTEMP,,DBFSTRUC, "FOXCDX")

It never gets to the DC_DBEDIT() line, it gives me an error.

If there is no command that can copy the structure of a table, I will have to have the table in a folder, and use it via foxdbe to copy it. I wanted to gradually eliminate the use of set default to.

Please can someone help me with the following:

1. Is there any way to put the name of the server in the default set, so as not to have to do the Map Network Drive, in the client computers or terminals?

For example
Set Default To \\server\c\data

2. What command or function allows me to make a copy of a table structure while using the POSTGRESQL driver?


Best regards

[Tom]

Hi, Diego.

Almost every database command knows the VIA clause. This allows you to use a DBE for operations that is not the default DBE:

Code: Select all

USE mytable // uses the default DBE
COPY TO mytable2 VIA DBFNTX // creates a physical DBF at the path set by your app

When using functions like DbCreate(), DbExport() and others, the DBE is a parameter - take a look at the docs. You can also use a session object instead of the DBE name, if you want.

[Diego Euri Almanzar]

Hello, Tom

Great. Thank you so much. The subject of the temporary file, already works correctly for me.

I notice that these temporary files, I also have to open them with the same VIA that they were created. Therefore, I will have to give a map network driver to the server where they are hosted.

Is there any way to put the name of the server in the default set, so as not to have to do the Map Network Drive, in the client computers or terminals, by putting the name of the server in a set default to, or in another statement ?

Best regards

[Tom]

Hi, Diego.

You may use the PG for temporary tables aswell. It's just a little harder to delete them - you have to use a SQL statement for that (DROP TABLE ...). Besides, it's a good idea to get rid of temporary tables. Use arrays or arrays of data objects instead. This is ways faster - and you don't have to deal with files.

I'm not sure if I understand your question. If you have a network path set with SET PATH or SET DEFAULT, this will work even if your database is on a PG server. The location/URL of your server is a part of the connection string you use for the DAC session, so you don't need that elsewhere.

So if you set Set(_SET_DEFAULT,"\\myServer\data\") and you use DbCreate() or DbExport() (COPY TO), your files will be created there. You need to mention the path only if you create tagless single index files (DBFNTX), since OrdCreate() doesn't reflect SET DEFAULT.

[Diego Euri Almanzar]

Hello, Tom

As I get more experience with POSTGRESQL, and as I learn to use your excellent suggestions, including DROP TABLE, I must have a shared folder on the server, as I always have.

For this, I have always used Set Default to.

Then I'll use your excellent array recommendation. In the meantime, I'll use temporary files. And for this I will have to make the map network to each terminal or client.

However, if your recommendation of the Set(_SET_DEFAULT,"\\myServer\data\") It works for me, it will be a success, a great thing for me, because it would avoid me giving the physical map network drive to each computer, or client.

Finally, this sentence Set(_SET_DEFAULT,"\\myServer\data\") that you recommend is what I was looking for a long time.

Excellent thanks.

Best regards.

[slobodan1949]

Problem:
-----------
Situations in which DBF files are used as temporary files
for downloading data from SQL tables and various processing and printing of that data

Task:
--------
sql table CUSTOMER with customer data is copied
in the temporary dbf file CUSTOMER.DBF in which the data about
to the customer and used to print the invoice:

An example of one of the solutions:
--------------------------------------------
PUBLIC tSQL := "CUSTOMER"
PUBLIC tDBF := "D:\DBF\CUSTOMER.DBF" or "\\SERVER\DBF\CUSTOMER.DBF"

cSQL := "SELECT * FROM "+tSQL+";"
oStmt := DacSqlStatement():fromChar(cSQL)
cAlias := oStmt:build():query(,"CUSTOMER")
CUSTOMER := cAlias // this is mandatory !
SELECT "CUSTOMER" // this is mandatory !
GO TOP
// copy structure and all records from sql table tSQL to tDBF file in CUSTOMER.DBF
COPY ALL TO (tDBF) VIA "DBFNTX"
// and this works:
// COPY TO (tDBF) FOR RECNO()=1 VIA "DBFNTX" // i ovo radi
CLOSE CUSTOMER

// if sql table tSQL is empty it will be created
// empty dbf file tDBF with identical structure as sql table tSQL.

// if the sql table tSQL is not empty, all rows from it will be overwritten
// to dbf file tDBF.
// If you need an empty tDBF file simply empty it with:

USE (tDBF) NEW EXCLUSIVE ALIAS "TMP" VIA "DBFNTX"
DELETE ALL; PACK
* or ZAP
SELECT "TMP";USE

For all of this to work, the application must be started with the following content in DBESYS()
PROCEDURE dbesys()
IF ! DbeLoad( "DBFDBE", .T.) // load engine for DBF files
msgbox( "Database Engine DBFDBE not loaded" , "STOP" )
QUIT
ENDIF
IF ! DbeLoad( "NTXDBE", .T.) // load engine for DBF files
msgbox( "Database Engine NTXDBE not loaded" , "STOP" )
QUIT
ENDIF
DbeBuild( "DBFNTX", "DBFDBE", "NTXDBE" ) // DBE=DBFNTX
DbeSetDefault("DBFNTX")

DbeLoad("PGDBE")
DbeSetDefault("PGDBE")
RETURN