[2.0 b4] Error: db function instr

Started by Kakao, October 04, 2008, 04:42:18 PM

Previous topic - Next topic

Kakao

2.0b4 Centos 5.1 Postgresql 8.3.3

After installing and selecting a new language i get this error when i click on a board link:

ERROR: function instr(text, unknown) does not exist at character 361
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248


The code is this:

        // Nosey, nosey - who's viewing this topic?
        if (!empty($settings['display_who_viewing']))
        {
                $context['view_members'] = array();
                $context['view_members_list'] = array();
                $context['view_num_hidden'] = 0;

                $request = $smcFunc['db_query']('', '
                        SELECT
                                lo.id_member, lo.log_time, mem.real_name, mem.member_name, mem.show_online,
                                mg.online_color, mg.id_group, mg.group_name
                        FROM {db_prefix}log_online AS lo
                                LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = lo.id_member)
                                LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = {int:reg_member_group\
} THEN mem.id_post_group ELSE mem.id_group END)
                        WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',
                        array(
                                'reg_member_group' => 0,
                                'in_url_string' => 's:5:"board";i:' . $board . ';',
                                'session' => $user_info['is_guest'] ? 'ip' . $user_info['ip'] : session_id(),
                        )
                );


Selecting the default english language does not get rid of the error.

Kakao

As i saw this:
if (!empty($settings['display_who_viewing']))

I disabled "Show who is viewing the board index and posts" in the Theme Settings and obviously the error is gone since that chunk of code is not executed.


SleePy

Thanks, Bug #2657: instr does not exist in postgresql

I think there is two bugs here. I am not really sure on that though.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:GitHub
Join us on IRC Libera.chat/#smf

Oldiesmann

I'm not positive that the "unknown" type is a bug, but anyway, run this in phpPgAdmin and let me know if it fixes the issue:

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS integer AS '
  SELECT POSITION($2 IN $1)
' IMMUTABLE STRICT LANGUAGE 'sql';


(Source: MySQL Compatibility Functions package at http://pgfoundry.org/projects/mysqlcompat/)
Michael Eshom
Christian Metal Fans

Kakao

After creating the function i got this error:

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248


Then i changed from:
WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',

To:
WHERE INSTR(lo.url, {string:in_url_string}) > 0 OR lo.session = {string:session}',

And it worked. Then when viewing the post:

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/Display.php
Linha: 497


The same fix as above worked.

Oldiesmann

Thanks for the feedback. With some minor tweaking, we can make the function return a boolean value :)

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS boolean AS '
  SELECT (POSITION($2 IN $1) != 0)
' IMMUTABLE STRICT LANGUAGE 'sql';


That's the version I've committed as the fix :)

! PostgreSQL doesn't have instr() (install_2-0_postgresql.sql, upgrade_2-0_postgresql.sql) [Bug 2657]

Revision 7598
Michael Eshom
Christian Metal Fans

Kakao

In my proposed fix i changed the where clause and not the function because i though it would be possible that the function was used somewhere else with the original meaning, that is, return the substring start position (int) in a string.

Thanks for the fast fix.

SleePy

It should be returning as the position. That is the way the MySQL manual says it should. So we need to follow that incase there is another use for instr such as returning the actual position in the string.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:GitHub
Join us on IRC Libera.chat/#smf

Advertisement: