Accessing a view instead of a table

Hi Charles,

I’m getting an error on that page. Looks like the table or view doesn’t exist. Can you fix that?

Can you provide your view definition?

Best regards,
Peter

I’m looking at the access problem. Here’s the view definition.

I had to change the quote character to get it past the forums formatter.

CREATE 
    ALGORITHM = TEMPTABLE 
    DEFINER = 'ogsonca'@'localhost' 
    SQL SECURITY INVOKER
VIEW 'ogsonca_ottawa_branch'.'nameindex_library' AS
    SELECT 
        'ogsonca_ottawa_branch'.'nameindex'.'SURNAME' AS 'SURNAME',
        'ogsonca_ottawa_branch'.'nameindex'.'NAME_DATE' AS 'NAME_DATE',
        'ogsonca_ottawa_branch'.'nameindex'.'PAGE' AS 'PAGE',
        'Library Info' AS 'Comment',
        'ogsonca_ottawa_branch'.'nameindex'.'LIBREF' AS 'LIBREF',
        'ogsonca_ottawa_branch'.'library'.'subcallid' AS 'subcallid',
        'ogsonca_ottawa_branch'.'library'.'callid' AS 'callid',
        'ogsonca_ottawa_branch'.'library'.'ID' AS 'ID',
        'ogsonca_ottawa_branch'.'library'.'Category' AS 'Category',
        'ogsonca_ottawa_branch'.'library'.'Classification' AS 'Classification',
        'ogsonca_ottawa_branch'.'library'.'Collection' AS 'Collection',
        'ogsonca_ottawa_branch'.'library'.'Description' AS 'Description',
        'ogsonca_ottawa_branch'.'library'.'keyword' AS 'keyword',
        'ogsonca_ottawa_branch'.'library'.'created' AS 'created',
        'ogsonca_ottawa_branch'.'library'.'Title' AS 'Title',
        'ogsonca_ottawa_branch'.'library'.'Publisher' AS 'Publisher',
        'ogsonca_ottawa_branch'.'library'.'Author' AS 'Author',
        'ogsonca_ottawa_branch'.'library'.'Abstract' AS 'Abstract',
        'ogsonca_ottawa_branch'.'library'.'ISBN' AS 'ISBN',
        'ogsonca_ottawa_branch'.'library'.'modified' AS 'modified',
        'ogsonca_ottawa_branch'.'library'.'Status' AS 'Status',
        'ogsonca_ottawa_branch'.'library'.'dewey' AS 'dewey',
        'ogsonca_ottawa_branch'.'library'.'Oldbook' AS 'Oldbook',
        'ogsonca_ottawa_branch'.'library'.'donated_by' AS 'donated_by'
    FROM
        ('ogsonca_ottawa_branch'.'nameindex'
        JOIN 'ogsonca_ottawa_branch'.'library' ON (('ogsonca_ottawa_branch'.'nameindex'.'LIBREF' = 'ogsonca_ottawa_branch'.'library'.'Classification')))

• This reply was modified 5 years, 3 months ago by charlesgodwin.
• This reply was modified 5 years, 3 months ago by charlesgodwin.

@peterschulznl What is your external IP address or country. We may have too tight blacklisting. We’ve been getting hit lately.

I’m connecting from the Netherlands Charles.

Try connecting now. sorry for the delay, I was away from my keyboard. I also emailed you the tables involved.

I have succeeded in using a view. I changed the ON syntax in the JOIN to simple WHERE syntax.

before
FROM (nameindex JOIN library ON ((nameindex.LIBREF = library.Classification)))

after
FROM (nameindex JOIN library) WHERE (nameindex.LIBREF = library.Classification)

Hi Charles,

Sorry for the delay as well! At day time I work at the university… ??

Thanks for the import files you mailed. On my server both views work. It doesn’t matter if I use the ON or WHERE syntax. I just had to change the “SQL SECURITY” definition. Could the SQL SECURITY INVOKER be the problem? If you change that to SQL SECURITY DEFINER the view works.

BTW, the WHERE syntax is way faster on my server!

Best regards,
Peter

I think I tried SQL SECURITY INVOKER both ways for yesterday but i’ll revisit it.

I hadn’t looked at performance. ??

Thanks for the update.

Your welcome Charles!

I’m closing this topic. You can of course always open a new one if you have any questions. I’ll be glad to help! ??

Best regards,
Peter