requiredStatus on Qt 4.3 and PostgreSQL 8.3.3

[Auryn]

Some fields in one PostgreSQL table are configured with "NOT NULL".
Inside my Qt 4.3 application I try to detect this constraint, but I am trying with

if(my_model.requiredStatus == QSqlField::Required)
// Warn user that this field must be filled
But it allways returns -1 (QSqlField::Unknown).
If this is not the method to follow, how can PostgreSQL tell to Qt when a field has some restriction (check, not null...)?

[jacek]

How do you create that my_model object?

[Auryn]

Here is a sample code showing how I am creating the model:

QSqlRelationalTableModel my_model;

my_model.setEditStrategy(QSqlTableModel::OnManualS ubmit);
my_model.setTable("my_postgresql_table");

my_model.setHeaderData(0, Qt::Horizontal, t("my_field_0"));

my_model.setHeaderData(1, Qt::Horizontal, t("my_field_1"));
my_model.setRelation(1, QSqlRelation("foreign_table", "id", "caption");

my_model.select();

QSqlRelationalTableModel my_table;
my_table.setModel(my_model);
my_table.setItemDelegate(new QSqlRelationalDelegate(my_table));


The signal dataChanged is connected to one slot that calls:

my_model->submitAll();
This works, but if one constraint (as NOT NULL) doesn't match, the line doesn't get inserted, and doesn't warn the user.

Here is the code where I connect to my PostgreSQL database (before creating the model):

my_conection = new QSqlDatabase(QSqlDatabase::addDatabase("QPSQL"));
my_conection->setHostName("my_host");
my_conection->setDatabaseName("my_postgresql_database");
my_conection->setUserName("my_username");
my_conection->setPassword("my_password");

Thank you

[jacek]

But requiredStatus() is a method of QSqlField class, not QSqlRelationalTableModel. You can get one through QSqlRecord class.

[Auryn]

Sorry, what I wanted to say at the beginning was this:



if(my_model.record().field(0).requiredStatus() == QSqlField::Required)
// Warn user that this field must be filled


It allways returns -1, so the field never is considered as required (unless it is required for PostgreSQL: ALTER TABLE my_table ALTER COLUMN first_field SET NOT NULL).

In this way, I think my_model.record().field(0) is a QSqlField.

[jacek]

What does my_model.record().count() return?

[Auryn]

The sentence:

my_model.record().count()
returns four (the correct number of columns in the table).

[jacek]

So it seems that everything is OK on your side and the problem is with the driver. Since you have table and field names (or at least you can get them from Qt), you can write your own method that will check that. You have to query the information_schema.columns.

Something like this should do (if you don't use schemas):

SELECT ( is_nullable = 'NO' AND column_default is NULL ) AS required
FROM information_schema.columns
WHERE table_name = :table_name AND column_name = :column_name;

[Auryn]

Thank you, this method works to check "NOT NULL" constraint:


bool My_class::Is_field_required(const QString &my_table, const QString &my_field) const
{
QSqlQuery my_query("SELECT(is_nullable='NO' AND column_default IS NULL) AS required FROM information_schema.COLUMNS WHERE table_name='"
+ my_table + "' AND column_name='" + my_field + "';");
if (!my_query.exec())
{
return(false);
}
else
{
consulta.first();
return(my_query.value(0).toBool());
}
}

But, what about other constraints (CHECK)?

I supose that I must search inside information_schema too.

SQL table example:

CREATE TABLE my_products (
my_product integer,
my_product_name text,
my_price numeric CHECK (my_price > 0)
);

[jacek]

QSqlQuery my_query("SELECT(is_nullable='NO' AND column_default IS NULL) AS required FROM information_schema.COLUMNS WHERE table_name='"
+ my_table + "' AND column_name='" + my_field + "';");
Better use QSqlQuery::prepare() and QSqlQuery::bindValue().



I supose that I must search inside information_schema too.
Yes, see information_schema.check_constraints.