Firebird question

[Zoran Sibinovic]

Hi, 

Working around I encountered a firebird base, and, normally went to try some fb tests in hbfbird\tests.

I the .fdb base I found a table (Table1) as a list with CODE ,NAME  fields, also another (Table2) that contains the CODE field putted in relation with the Table1.

 I was able to add a new text field in the second table - NAME,  with ALTER, but I don't know how to replace the new added column with the relational names, from the first table, by code as key. 

 Thanks for help 

 Zoran

[Zoran Sibinovic]

Hi,

I try to open an existing database, read and display the structure of a defined table. The problem is that i got only some garbage instead of the fields names and the other displayed numerical data have no sense.

I have to say also that the base is created with the UTF8 charset, but the data are filled, throug the app (not mine), using the win1251 charset.

#include "simpleio.ch"

PROCEDURE Main()
LOCAL oServer, i, x, aTables, aStruct

LOCAL cServer := "localhost:"
LOCAL cDatabase:= "ev.fdb"
LOCAL cUser := "SYSDBA"
LOCAL cPass := "masterkey"
LOCAL nPageSize := 1024
LOCAL cCharSet := "UTF8"
LOCAL nDialect := 1

oServer := TFBServer():New( cServer + cDatabase, cUser, cPass, nDialect )

FOR x := 1 TO 1
aTables := oServer:ListTables()
FOR i := 1 TO Len( aTables )
? aTables[ i ]
NEXT
NEXT

oServer:Query("SELECT id, godina, arh_id, opstina_id, maticno_podrucje_id FROM umrli")

aStruct := oServer:TableStruct( "umrli" )

FOR i := 1 TO Len( aStruct )
?
FOR x := 1 TO Len( aStruct[ i ] )
?? (aStruct[ i, x ])
NEXT
NEXT

wait
oServer:Destroy()
RETURN

[apolinar]

You can try dialect=3

regards
Apolinar

[Zoran Sibinovic]

Hi apolinar,

aready did.

When tried to open in Flamerobin only work with dialecty=3,

but through harbour code the opening can be done only with 1, otherwise the app stuck.

Zoran

[apolinar]

TFBServer is wraper (ch ) that do not work very well. Try tests.prg
in firebird contrib

apolinar

[Zoran Sibinovic]

Hi apolinar,

you was right, many thanks.

I used FBConnect(), and been able to connect and read the columns data.

The only problem to solve are the blob data. 

when use : 

qry := FBQuery( db, "SELECT * FROM umrli", nDialect )

x:=11 /field number

?FBGetData( qry, 11)

I get nil for empty blob data, that can be ok for handling them as nil.

but when is encountered a not empty field I get some like this 0x00B24DEC as result.

maybe is required some codepage change before/during executing  FBGetData().

I found the  FBGetBlob(), just cant find what is produce and what are the parameters for it.

Using Flamerobin, when register the database and when I choose charset="UTF8", the data is readable except the blob data, they look like some garbage.  If I use  charset="WIN1251", Flamerobin advertise me that i choose to use a different charset instead of the default set (UTF8) during the database creation, and ..., using this way all the data including the blobs are displayed correctly. Seems that the database is created with the UTF8 but the data in the blob is entered using the win1251 charset.

Anyway, its still on the way how to read the blob data, even as garbage.

Thanks again, very much, to help me to, at least, read the base.

If you can help me about how to read blobs, it will be very appreciate.

Zoran

[apolinar]

Unfortunately I have no experience with blob fields.
But if you find anything I am interested to see it.
If I find something about it I'll post it here.

regards
Apolinar

[Zoran Sibinovic]

Hi apolinar,  

I found a way, to extract the blob text from a firebird database, it's do the job but ... is not pretty nice. 

I'm involved in one project to extract data from a Firebird database and to generate some .txt lists based on the extracted data. 

I use Flamerobin to manage the database

What we have and what we don't know:

1. We have a Firebird database 

2. We don't know in witch version of Firebird is created 
3. We don't know what charset is used during the creation of the database 
4. We know that the input language is cyrillic 
5. We have some problematic blob fields that are displayed ok in grids, in some tables, and as garbage in others (observation: all blob fields are text blobs of subtype=1). 

What we found

1. We found that the charset that is used during the creation of the database is UTF8
2. We found that when use the UTF8 charset as default for the database, blob fields, in some tables, are displayed ok in grid, but if use the WIN1251 charset the things become the opposite. The ok displayed blob fields become as garbage and the early problematic fields become now readable. Seems that is there a collision between the defined chares and the input charset. So if we want to export them we have to change the default charset in the database registration info depending of the problematic tables. 

Exporting tables 

1. Exporting as .csv files
We presume that we have choose the right charset for reading all the fields in the table, blob also. 
All the fields are nicely exported except the blob fields. 
A blob field can contains more lines of text, so what's happened after exporting. 
We get a .csv file witch can be edited in notepad line by line.We have to notice that the record lines are with tabs or with some other char and nicely delimited, but somewhere we can find some line with some text that doesn't seems to be records, just a line of pure text with no delimiters. They are the second, the third, ... blob line from the above last record we seen, all because blob fields can contain more different lines of text. Very impractical from this point of view. 

2. Exporting as .html files 

All it's ok and all the line in a blob field are grouped in one cell so we can see what's happened, just how to read them?. No harbour example 

What we can do to get the data in a managable state? 

I have tried to import the .html file in excel and through my app to read the the .xsl file and put the data in a .dbf database. All is ok for the normal fields except that I have to manage sometimes two or more records of blob lines data.

I have tried also to work with the .csv files and found a way to put the blob directly in a temporary .dbf using APPEND FROM temp.csv SDF and than make some manipulation to get the hole blob lines in one record.

At the end, I used your suggestion to directly get the normal data from the firebird database, with FBConnect() and FBGetData(), and when is the time to get the blob data for that record, it is taken from the .csv>.dbf database that must have the same number of records as the firebird table that we work with. 

Not nice, but works and is faster rather than reading and converting .xsl files.

The database for the .csv imput is created as

DBCREATE("priv1",{ { "a1","C",254,0 }, { "a2","C",254,0 }, { "a3","C",254,0 }, { "a4","C",254,0 }, { "a5","C",254,0 }, { "a6","C",254,0 } })

or more fields depending of the blob lenght that we can expect. 

We than fill the .dbf with

APPEND FROM temp.csv SDF

so the blob line is automatically divided trough the fileds of the dbf. 

What we can do with the second line and so on?

well ... when exporting from the firebird database I exported only the ID and the blob field with ";" as field delimiter. The blobs second,... line/s of the blob text in the .csv have no ID number and no ";' so that lines can be later considered not as new record line but a line that belongs to the above record line in .csv. 

Also another thing, it's very hard or impossible to change the charset of a blob column or even directly edit and change the text in it, at list with Flamerobin.

That's all for now.

There are examples on the net of how reading blobs but not applicable to harbour. 

Maybe the FBGetBlob() does all the thing, but haven't found any documentation of how it work, yet. 

Hope that is useful to someone 

Zoran

[Zoran Sibinovic]

Hi,

I notice another thing...

what you get if you retrieve a data field with the FBGetData( qry, x) function that is defined as VARCHAR in the firebird database?

If the field have some data you got the data, but if the field is empty (NULL), then the retrieved data is not an empty string, so you cannot use EMPTY(), ALLTRIM() or other functions to determine the state, trim or else with it.

I have noticed this after filling a .dbf database with this kind of data in a column and looking into it, the empty fields are displayed as empty, the others have data.  

When, during the processes of my app i had to ask if the field is empty, I expected that in some case they were, but the function always return .f.,  and when I had to trim this data concatenated with other fields the result is always string with spaces between. I, then, made a .txt file and filled it with this data to see what is happened. In the OEM view i saw spaces where is no data but when I switch to another view (ex. ANSI) the spaces becomes some undefined characters.

I found that asking

IF ISALPHA(ALLTRIM(field1)) .OR. ISDIGIT( ALLTRIM(field1))

you can get if the field have some really data (.t.) or is "empty" (.f.), so, then decide the manner of successive use of this field.

Maybe not a complete way of handle it but hope useful

Zoran