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

[Patrice]

I finally solved my problem.

The database configuration was correct after all... All I had to do was 
clearing the cache in prod environment.

*php app/console cache:clear --env=prod*


On 2017-07-03 16:21, Astrid Emde wrote:
> 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
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapbender_users/attachments/20170721/aaad4029/attachment.html>