Hi,
I have recently been playing around with PostgreSQL and SqlDB. I seem to have
quite a few problems. tiOPF's test suite normally gives database components a
good workout and currently SqlDB + PostgreSQL has about 40 tests failing out of
160 tests. SqlDB + Firebird does much better, but still not a 100% success
rate. I hope to resolve all of these.
Here are a few questions regarding the PostgreSQL support in SqlDB:
1..)
In the function TPQConnection.GetSchemaInfoSQL(..) there is the following SQL
statements.
* First off, this statement doesn't return a single row when I run it directly
in psql or pgAdmin III.
* Why are so many fields got the value 0. For example the system table
"pg_attribute" contains most of the information for the fields that return 0.
Fields like type, datatype, scale, length, etc...
============================================
stColumns : s := 'select '+
'a.attnum as recno, '+
''''' as catalog_name, '+
''''' as schema_name, '+
'c.relname as table_name, '+
'a.attname as column_name, '+
'0 as column_position, '+
'0 as column_type, '+
'0 as column_datatype, '+
''''' as column_typename, '+
'0 as column_subtype, '+
'0 as column_precision, '+
'0 as column_scale, '+
'a.atttypmod as column_length, '+
'not a.attnotnull as column_nullable '+
'from '+
' pg_class c, pg_attribute a '+
'WHERE '+
// This can lead to problems when case-sensitive
tablenames are used.
'(c.oid=a.attrelid) and (a.attnum>0) and (not
a.attisdropped) and (upper(c.relname)=''' + Uppercase(SchemaObjectName) + ''') ' +
'order by a.attname';
============================================
Instead of the above query, why not use the Information Schema views to pull
that information out in a much more friendly manner. Here is a quick example...
============================================
SELECT ordinal_position,
column_name,
data_type,
column_default,
is_nullable,
character_maximum_length,
numeric_precision
FROM information_schema.columns
WHERE table_name = 'test_table'
ORDER BY ordinal_position
============================================
2..)
Then in function TPQConnection.TranslateFldType(..) we have the following
lines...
Oid_text : Result := ftBlob;
Oid_Bytea : Result := ftBlob;
Shouldn't Oid_text return ftMemo instead of ftBlob?
3..)
In procedure TPQConnection.PrepareStatement(..) there is a const TypeStrings being setup. Many of those
entries show "unknown" when in fact they could probably have PostgreSQL types associated instead.
The 16th and 18th item (counting starts at 1) could most probably be "bytea" instead of
"unknown". There are a few others as well.
As I work through the rest of the code, I'll make notes and report more
findings here.
Regards,
- Graeme -
________________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/
_______________________________________________
fpc-devel maillist - fpc-devel@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-devel
