How to save files to blob in PG

[unixkd]

Hi all

I am working on document management system and need to save any kind of files to bytea of PG using SQL insert and update.

Thanks

Joe

[skiman]

Hi,

This is the answer of Chatgpt.

In **Xbase++**, saving a file into a PostgreSQL BLOB field can be done using the `bytea` type and parameterized SQL with the PostgreSQL client library (e.g. via ODBC or the `PGDBE` from Alaska Software).

Below is a structured outline for how to save files to a `bytea` column in PostgreSQL using Xbase++.

---

## Step-by-Step: Save File to `bytea` in PostgreSQL (Xbase++)

### 1. **PostgreSQL Table Definition**

First, create a suitable table in PostgreSQL:

```sql
CREATE TABLE files (
id SERIAL PRIMARY KEY,
filename TEXT,
content BYTEA
);
```

---

### 2. **Read File into a Binary String**

In Xbase++, use `FRead()` to load the file into a buffer:

```xbase
FUNCTION LoadFileToBuffer(cFilename)
LOCAL nHandle := FOpen(cFilename, FO_READ + FO_SHARED)
LOCAL nSize := FSeek(nHandle, 0, FS_END)
LOCAL cData

FSeek(nHandle, 0, FS_SET)
cData := Space(nSize)
FRead(nHandle, @cData, nSize)
FClose(nHandle)

RETURN cData
```

---

### 3. **Insert into PostgreSQL using parameterized query**

If you are using the **PGDBE** (PostgreSQL DatabaseEngine for Xbase++), use a prepared SQL statement:

```xbase
PROCEDURE SaveFileToPostgres(cFilename)

LOCAL oSession := DacSession():new("PGDBE") // Adjust if you use ODBC
LOCAL oConn := oSession:open("host=localhost dbname=mydb user=postgres password=secret")
LOCAL cFileContent := LoadFileToBuffer(cFilename)
LOCAL cFileName := JustFName(cFilename)
LOCAL oStmt := oConn:statement()

oStmt:prepare("INSERT INTO files (filename, content) VALUES (?, ?)")
oStmt:setParameter(1, cFileName, DBTYPE_CHARACTER)
oStmt:setParameter(2, cFileContent, DBTYPE_BINARY)
oStmt:execute()

? "File inserted: " + cFileName

oStmt:close()
oConn:close()

RETURN
```

---

### 4. **Retrieving the file**

To read and save a file back to disk:

```xbase
FUNCTION RetrieveFileFromPostgres(nId, cTargetDir)

LOCAL oSession := DacSession():new("PGDBE")
LOCAL oConn := oSession:open("host=localhost dbname=mydb user=postgres password=secret")
LOCAL oStmt := oConn:statement()
LOCAL aRow, cFileContent, cFileName, cTargetFile

oStmt:prepare("SELECT filename, content FROM files WHERE id = ?")
oStmt:setParameter(1, nId, DBTYPE_INTEGER)
oStmt:execute()
aRow := oStmt:fetch()

IF aRow != NIL
cFileName := aRow[1]
cFileContent := aRow[2]
cTargetFile := cTargetDir + "\" + cFileName

LOCAL nHandle := FCreate(cTargetFile)
FWrite(nHandle, cFileContent)
FClose(nHandle)

? "File written: " + cTargetFile
ELSE
? "No file found for ID " + LTrim(Str(nId))
ENDIF

oStmt:close()
oConn:close()

RETURN
```

---

## Notes

* Ensure the `PGDBE` is enabled in `DBE=...` in your application’s `config.dbf`.
* Use proper escaping or parameter binding to avoid issues with binary data in SQL.
* For larger files, consider chunking or streaming if needed.

---

[unixkd]

Hi Chris

Thanks so much for your response. I appreciate.

I used odbc with sqlexpress. I used the xbase tools 3 functions namely

1. Filestr()
2. Strfile()

Work fine. These functions can read any type of file and write it back to file. I didn't realise it initially.

Joe