[Mapbender-users] Problem using the Search Router (can't find table)

[Astrid Emde]

Hi,

you only referred one database, but you need one for Mapbender and one 
for your database with geodata.

The configuration should look like this

doctrine:
     dbal:
         default_connection: default
         connections:
             default:
                 driver:   %database_driver%
                 host:     %database_host%
                 port:     %database_port%
                 dbname:   %database_name%
                 path:     %database_path%
                 user:     %database_user%
                 password: %database_password%
                 charset:  UTF8
                 logging: %kernel.debug%
                 profiling: %kernel.debug%
             acadb:
                 driver:   %database2_driver%
                 host:     %database2_host%
                 port:     %database2_port%
                 dbname:   %database2_name%
                 path:     %database2_path%
                 user:     %database2_user%
                 password: %database2_password%
                 charset:  UTF8
                 logging: %kernel.debug%
                 profiling: %kernel.debug%

see 
https://doc.mapbender3.org/en/book/database.html#using-multiple-databases

Afterwards you can change the search router configuration and refer to 
acadb

class: Mapbender\CoreBundle\Component\SQLSearchEngine
class_options:
   connection: acadb
   relation: data.aires_prot_priv
   attributes:
     - objectid
     - code_proje
   geometry_attribute: geom
form:
   code_proje:
     type: text
     options:
       required: true
     compare: ilike
results:
   view: table
   count: true
   headers:
     code_proje: Name
   callback:
     event: click
     options:
       buffer: 10
       minScale: null
       maxScale: null

Astrid


Am 2017-07-03 19:22, schrieb Abrabacabra:
> Thank you Astrid.
> 
> Although I am still missing something and I can't spot it...
> 
> It can find the connection, but even if the credentials and
> permissions are correct, I still get the "No such table" error. In
> fact, even if I put incorrect information in parameters.yml (such as
> not wrong database driver, wrong adress, wrong port, wrong user, wrong
> password, etc), it raises the "No such table" error.
> 
> I included the links to the configuration files
>  CONFIG.YML:   https://paste.debian.net/hidden/317e0f4c/
> PARAMETERS.YML: https://paste.debian.net/974572/
> SEARCHROUTER CONFIGURATION: https://paste.debian.net/974573/
> 
> On 2017-07-03 11:46 AM, Astrid Emde wrote:
> 
>> Hello Patrice,
>> 
>> Am 2017-07-01 16:15, schrieb Patrice:
>> 
>>> I am trying to use the Search Router bundle in an application, but
>>> it
>>> fails.
>>> 
>>> At first, it could not find the database, so I have setup the
>>> database
>>> in config.yml and parameters.yml.
>>> 
>>> Now, it can't find the table to search in.
>>> 
>>> Here is the error in prod.log:
>>> 
>>> [2017-07-01 14:04:54] request.CRITICAL: Uncaught PHP Exception
>>> Doctrine\DBAL\DBALException: "An exception occurred while
>>> executing
>>> 'SELECT t.objectid, t.code_proje, ST_AsGeoJSON(geom) as geom FROM
>>> data.aires_prot_priv t WHERE LOWER(t.code_proje) LIKE LOWER(?)'
>>> with
>>> params ["%tabi%"]:  SQLSTATE[HY000]: General error: 1 no such
>>> table:
>>> data.aires_prot_priv" at
>>> 
>> 
> C:\ms4w\apps\mapbender3-3.0.5.3\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php
>>> 
>>> line 91 {"exception":"[object]
>>> (Doctrine\\DBAL\\DBALException(code:
>>> 0): An exception occurred while executing 'SELECT t.objectid,
>>> t.code_proje, ST_AsGeoJSON(geom) as geom FROM data.aires_prot_priv
>>> t
>>> WHERE LOWER(t.code_proje) LIKE LOWER(?)' with params
>>> [\"%tabi%\"]:\n\nSQLSTATE[HY000]: General error: 1 no such table:
>>> data.aires_prot_priv at
>>> 
>> 
> C:\\ms4w\\apps\\mapbender3-3.0.5.3\\vendor\\doctrine\\dbal\\lib\\Doctrine\\DBAL\\DBALException.php:91,
>>> 
>>> PDOException(code: HY000): SQLSTATE[HY000]: General error: 1 no
>>> such
>>> table: data.aires_prot_priv at
>>> 
>> 
> C:\\ms4w\\apps\\mapbender3-3.0.5.3\\vendor\\doctrine\\dbal\\lib\\Doctrine\\DBAL\\Connection.php:691)"}
>>> 
>>> []
>>> 
>>> The table "aires_prot_priv" does exist in the "data" schema and I
>>> have
>>> the necessary right to read from it (made the test with pgAdmin
>>> using
>>> the same credentials as specified in parameters.yml).
>>> 
>>> However, as my database and schema are already filled with the
>>> data I
>>> want to use, I didn't create the database and schema using
>>> "doctrine:database:create". Is this mandatory in order to use the
>>> search router?
>> 
>> No - this is not mandatory.
>> 
>>> What am I missing?
>> 
>> It looks like your database configuration is not right or your
>> configuration.
>> You should ceck this again in parameters.yml and config.yml.
>> 
>> 
> https://doc.mapbender3.org/en/book/database.html#using-multiple-databases
>> 
>> 
>> In the searchRouter configuration you have to use the name that you
>> defined for your second database
>> 
>> Hope that helps
>> 
>> Astrid
>> 
>>> _______________________________________________
>>> Mapbender_users mailing list
>>> Mapbender_users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/mapbender_users
> _______________________________________________
> Mapbender_users mailing list
> Mapbender_users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapbender_users

-- 
Mit freundlichen Grüßen

Astrid Emde
GIS-Consultant

**********************************
AGIT 2017
5.-7. Juli 2017 in Salzburg
Besuchen Sie uns an Stand Nr. 31
www.agit.at
**********************************

  Astrid Emde
  WhereGroup GmbH & Co.KG
  Eifelstraße 7
  53119 Bonn
  Germany

  Fon: +49(0)228 90 90 38 - 22
  Fax: +49(0)228 90 90 38 - 11

  astrid.emde at wheregroup.com
  www.wheregroup.com

  Folgen Sie der WhereGroup auf twitter: 
http://twitter.com/WhereGroup_com

Amtsgericht Bonn, HRA 6788
-------------------------------
Komplementärin:
WhereGroup Verwaltungs GmbH
vertreten durch:
Olaf Knopp, Peter Stamm
-------------------------------