[Gambas-user] Gambas & PostgreSQL cannot create index. relation XXX does not exist.

[Randy]

> Just the last line tells me it wants a table to add the index.
>
> >From the past with msaccess and mysql i had a problem too
> The solution was write the table first and change the field type.
>
> For the last line I would say it try to create the index "glcoa_sort" ON glcoa
> and that the table glcoa does not exist. 
>
>
> suggest:
> a)
>
>     .Fields.Add("status", gb.String, 1)
>     .PrimaryKey = ["account"]
>     .Update              '<-- this should write the table before tha add indexes
>     .Indexes.Add("glcoa_account", "account", TRUE)
>     .Indexes.Add("glcoa_sort", "glsort", TRUE)
>     .Update
>    END WITH
>
> or
> b)
>
>     .Fields.Add("status", gb.String, 1)
>     .PrimaryKey = ["account"]
>     .Update
>    END WITH
>
> ' here the table must have been written by the .update and the END WIDTH
>
> ' maybe you must reopen the table again here
>
>    WITH hTable
>     .Indexes.Add("glcoa_account", "account", TRUE)
>     .Indexes.Add("glcoa_sort", "glsort", TRUE)
>     .Update              '<-- this should write the table before tha add indes
>    END WITH
>
>
> It looks stupid but it is the best idea I have ATM.
>
>
>
>
> Best regards,
>
> Ron_1st
>
>   
I made the change to source and update before and after index. Now I get 
error 'No field". I checked database and the table and index is created, 
but what is the error for? What is the number "23" before the "No field" 
error in the debug output?

Debug output is:

postgresql: 0x815a3b0: select substring(version(),12,5)
postgresql: 0x815a3b0: show client_encoding
postgresql: 0x815a3b0: select relname from pg_class where (relkind = 'r' 
or relkind = 'v') and (relname = 'glcoa') and (relnamespace not in 
(select oid from pg_namespace where nspname = 'information_schema'))
postgresql: 0x815a3b0: CREATE TABLE "glcoa" ( "company" VARCHAR(3), 
"account" VARCHAR(12) NOT NULL , "description" VARCHAR(32), "gltype" 
VARCHAR(1), "glsort" INT, "status" VARCHAR(1), PRIMARY KEY (account) )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"glcoa_pkey" for table "glcoa"
postgresql: 0x815a3b0: select pg_class.relname from pg_class, pg_index, 
pg_class pg_class2 where pg_class2.relname = 'glcoa' and 
(pg_class2.relnamespace not in (select oid from pg_namespace where 
nspname = 'information_schema')) and pg_index.indrelid = pg_
class2.oid and pg_index.indexrelid = pg_class.oid and pg_class.relname = 
'glcoa_sort'
postgresql: 0x815a3b0: CREATE UNIQUE INDEX "glcoa_sort" ON glcoa ( glsort )
GLTables.CreateGLTables.23: No field



Updated source code with two updates.

STATIC PUBLIC FUNCTION CreateGLTables()

  DIM hTable AS Table

  hTable = Global.$hConn.Tables.Add("glcoa")

  WITH hTable
    .Fields.Add("company", db.String, 3)
    .Fields.Add("account", db.String, 12)
    .Fields.Add("description", gb.String, 32)
    .Fields.Add("gltype", gb.String, 1)
    .Fields.Add("glsort", gb.Integer)
    .Fields.Add("status", gb.String, 1)
    .PrimaryKey = ["account"]
    .Update
    .Indexes.Add("glcoa_sort", "glsort", TRUE)
    .Update
   END WITH

CATCH
  DEBUG DConv(Error.Text)
  Message.Error("CreateGLTables Function: " & DConv(Error.Text))
END


Randy :-(

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiSpam:  SpamAssassin 3.2.3
AntiVirus: ClamAV 0.91.2/8992 - Sat Feb 14 10:43:07 2009
by Markus Madlener @ http://www.copfilter.org