Sequences and Editor Extension

medusa101

Hi,

Has anyone interfaced with tables that use sequences for a column. For example - a table that I have in postgres has the column studentID as a sequence. How we use this column?

Also has anyone implemented controls like drop downs inside the Editor or Datatables?

Regards
Adi

tangerine

AFAIK a Postgres "Sequence" is equivalent to SQL's auto-increment field. You would use it like any other field for ordering and searching - unless your particular application decrees otherwise - but you wouldn't enable editing on a Sequence field.

Others will know more than me about Postgres, though.

allan

This is the demo postgres SQL for DataTables (note that there has been a fix added since the 1.10.2 release...) which uses serial to create a column with a sequence attached to it.

Basically you shouldn't need to do anything if the database is setup to use the sequence for a column and it is triggering correctly.

Allan

medusa101

Hi Allan,

Thanks for the answer. However, does the PHP database library assume the column to be named 'id'. Because my column name is StudentID and it is trying to create a query like 'select id as "id" when it should be doing it as 'select studentid as "id"/"studentid". The version of Editor I have running is 1.3.2

medusa101

The error I get is as follows for my own files.

DataTables warning: table id=example - SQLSTATE[42703]: Undefined column: 7 ERROR: column "id" does not exist
LINE 1: SELECT id as "id", studentname as "studentname", dob as "do...

I think there is a problem with automatic detection and use of sequence based fields.

To verify I did this I modified the code for inline-editing example:-

I copied the simple inline editing to inline-editing2. Changes created a new table called datatables_demo2 from datatables_demo. I added another sequence for datatables_demo2. I then changed the name of the first column which is a SERIAL type from id to studentid. I made no changes to any of the code except for changing the table name in the staff.php.

When I try to load this page I get the following error for the example too.

DataTables warning: table id=example - SQLSTATE[42703]: Undefined column: 7 ERROR: column "id" does not exist
LINE 1: SELECT id as "id", first_name as "first_name", last_name as...

allan

However, does the PHP database library assume the column to be named 'id'.

Use the third parameter of the Editor constructor to tell the class to use a different primary key table. See:

• https://editor.datatables.net/manual/php/getting-started#Basic-initialisation
• https://editor.datatables.net/docs/Editor-1.3.2/php/class-DataTables.Editor.html#___construct

Allan