View Issue Details

This bug affects 1 person(s).
 8
IDProjectCategoryView StatusLast Update
05392Bug reportsSurvey editingpublic2011-08-12 16:06
ReporterPinkPenguin Assigned ToDenisChenu  
PriorityhighSeveritypartial_block 
Status closedResolutionfixed 
Product Version1.91+ 
Fixed in Version1.91+ 
Summary05392: ERROR: column "SID" of relation "lime_surveys" does not exist
Description

This error occurs with PostgreSQL v8.4 but, according to other reports, also seems to occur on MySQL though I have not verified this personally. It is the same for both the following versions of Lime Survey:
1) limesurvey191plus-build10670-20110810
2) limesurvey191plus-build10647-20110805

The error occurs because field names are quoted and in upper case in the INSERT INTO statement whereas in the database table names, field names ecc. are lower case.

Quoted names in SQL statements must match exactly the names in the database.

This happened on a fresh installation of Lime Survey where database creation and login worked perfectly.

Steps To Reproduce

1) Login and choose create a new survey.
2) Enter Title and any other required field.
3) Press "save" and the following error occurs:

INSERT INTO lime_surveys ( "SID", "OWNER_ID", "ADMIN", "ACTIVE", "STARTDATE", "EXPIRES", "ADMINEMAIL", "ANONYMIZED", "FAXTO", "FORMAT", "SAVETIMINGS", "TEMPLATE", "LANGUAGE", "DATESTAMP", "USECOOKIE", "ALLOWREGISTER", "ALLOWSAVE", "PRINTANSWERS", "AUTOREDIRECT", "SHOWXQUESTIONS", "SHOWGROUPINFO", "SHOWNOANSWER", "SHOWQNUMCODE", "SHOWWELCOME", "ALLOWPREV", "IPADDR", "REFURL", "DATECREATED", "LISTPUBLIC", "PUBLICSTATISTICS", "PUBLICGRAPHS", "HTMLEMAIL", "TOKENANSWERSPERSISTENCE", "ASSESSMENTS", "USECAPTCHA", "BOUNCE_EMAIL", "EMAILRESPONSETO", "EMAILNOTIFICATIONTO", "TOKENLENGTH", "SHOWPROGRESS", "ALLOWJUMPS", "NAVIGATIONDELAY", "NOKEYBOARD", "ALLOWEDITAFTERCOMPLETION" ) VALUES ( 12779, 1, 'Richard Golding', 'N', null, null, 'rgolding@datamanagement.it', 'N', '', 'G', 'N', 'sherpa', 'it', 'N', 'N', 'N', 'Y', 'N', 'N', 'Y', 'B', 'Y', 'X', 'Y', 'N', 'N', 'N', '2011-08-11', 'Y', 'N', 'N', 'Y', 'N', 'N', 'D', 'rgolding@datamanagement.it', '', '', 15, 'Y', 'N', 0, 'N', 'N' )
ERROR: column "SID" of relation "lime_surveys" does not exist LINE 1: INSERT INTO lime_surveys ( "SID", "OWNER_ID", "ADMIN", "ACTI...

TagsNo tags attached.
Bug heat8
Complete LimeSurvey version number (& build)10670
I will donate to the project if issue is resolvedNo
BrowserFirefox 5 & Chrome 13
Database type & versionPostgreSQL v8.4
Server OS (if known)Windows Vista
Webserver software & version (if known)Apache v2.2
PHP Versionv5.3.2

Relationships

related to 05367 closedDenisChenu Unable to import old survey with Array dual scale question 

Users monitoring this issue

worden

Activities

PinkPenguin

PinkPenguin

2011-08-11 11:45

reporter   ~16016

I have tried playing around with common_functions.php:

    case "postgres":
        return "\"".$id."\"";
        break;

But if I remove the quotes I just get blank screens. I have also tried strtolower() for the $id variable but it still all ends up upper case and produces the same error.

That's about as far as my limited knowledge of PHP goes at the moment...

PinkPenguin

PinkPenguin

2011-08-11 17:29

reporter   ~16020

Tried it with MySQL v5.1 - it works ok so the problem is probably limited to PostgreSQL.

c_schmitz

c_schmitz

2011-08-11 18:21

administrator   ~16025

As a workaround remove the line

if (!isset($ADODB_QUOTE_FIELDNAMES)){$ADODB_QUOTE_FIELDNAMES=true;}

from common.php

DenisChenu

DenisChenu

2011-08-11 19:10

developer   ~16032

Last edited: 2011-08-11 19:12

You can place $ADODB_QUOTE_FIELDNAMES=false; in config.php.

But i think here there are a problem of uppercase.
define('ADODB_ASSOC_CASE', 2); have to be good ...
And the array send to GetInsertSQL are in lowercase ...

PinkPenguin,
can you try with
$ADODB_QUOTE_FIELDNAMES=false;
and with
define('ADODB_ASSOC_CASE', 0);

please ?

PinkPenguin

PinkPenguin

2011-08-11 19:39

reporter   ~16033

Last edited: 2011-08-11 19:46

I put the following lines in config.php at the end of the file:

$ADODB_QUOTE_FIELDNAMES=false;
define('ADODB_ASSOC_CASE', 0);

That worked. I created a survey, did a few updates and inserted a question group and question just to be sure.

[edit]It also works with only $ADODB_QUOTE_FIELDNAMES=false; in the config.php file but it does not work with only define('ADODB_ASSOC_CASE', 0); - this latter maybe a question of where you put (just not in config.php).[/edit]

I suspect common.php is probably a better place for them but I'll leave that to you.

Many thanks.

DenisChenu

DenisChenu

2011-08-11 20:32

developer   ~16034

OK, i install postgres and see what we can do.

DenisChenu

DenisChenu

2011-08-12 13:09

developer   ~16040

Hello,

I don't know how to resolve,

I install LS with postgre 8.4

If i put
$ADODB_QUOTE_FIELDNAMES=false;; then i can create and activate a survey, but again i have the 05367 bug ..

With 05367 method:

ERREUR: erreur de syntaxe sur ou près de « # » LINE 1: ...te, lastpage, startlanguage, token, 72275X1X7SQ001#0, 72275X... ^:SELECT id, submitdate, lastpage, startlanguage, token, 72275X1X7SQ001#0, 72275X1X7SQ001#1, 72275X2X11#0, 72275X2X11#1, 72275X2X12#0, 72275X2X12#1, 72275X2X13#0, 72275X2X13#1, 72275X2X14#0, 72275X2X14#1 FROM lime_old_survey_72275_20110812130046

Somthing i don't understand : i read:
http://phplens.com/adodb/reference.constants.adodb_assoc_case.html
The 2 = use native-case field names.
The array are in lower-case, i don't understand why it was in upper-case for the function ...

Search for an idea ...

Workaround:
$ADODB_QUOTE_FIELDNAMES=false;
in config.php

PinkPenguin

PinkPenguin

2011-08-12 14:18

reporter   ~16041

It is probably not a good idea to use the # (hash) character in field names as in some SQL enfironments this causes teh rest of the statement to be treated as a comment. It was probably the reason you had to quote the field names in the first place.

Can the # (hash) be substituted for the _ (underscore) ?
This would be less ambiguous in the context. If this is so then $ADODB_QUOTE_FIELDNAMES=false; would probably work fine even for 05367.

DenisChenu

DenisChenu

2011-08-12 14:49

developer   ~16042

I know PinkPenguin ...

But there are some other part to verify.

c_schmitz

c_schmitz

2011-08-12 14:52

administrator   ~16043

Renaming fields is really a PITA. Lots of places would have to be changed. Maybe for a later version.

I guess the only way is to fix ADODB to use the right casing when calling getInsertSQL.

PinkPenguin

PinkPenguin

2011-08-12 15:00

reporter   ~16044

... I had a nasty feeling that the field name fix would be a problem ...;-)

So, if I have understood correctly, it's a hunt for wherever the field names get capitalized... probably in ADODB.

Is there a good way of tracing PHP while its executing ?

DenisChenu

DenisChenu

2011-08-12 15:56

developer   ~16045

Last edited: 2011-08-12 15:57

Remove getInsertSQL,

Put an pure sql string and an Execute on importoldresponse

Test the patch in postgre AND mysql, see what it done.

( and remove the $ADODB_QUOTE_FIELDNAMES=true; )

PS: jquery problem too with # in fieldname ;)
:)

DenisChenu

DenisChenu

2011-08-12 16:06

developer   ~16046

Fixed in 10704

Use of own function in importoldresponses.php .
Maybe put a global function in common_function.php is a good idea to insert and update a table with array of keys=>values

Issue History

Date Modified Username Field Change
2011-08-11 10:54 PinkPenguin New Issue
2011-08-11 11:45 PinkPenguin Note Added: 16016
2011-08-11 17:29 PinkPenguin Note Added: 16020
2011-08-11 18:19 c_schmitz Relationship added related to 05367
2011-08-11 18:19 c_schmitz Assigned To => DenisChenu
2011-08-11 18:19 c_schmitz Status new => assigned
2011-08-11 18:21 c_schmitz Note Added: 16025
2011-08-11 19:10 DenisChenu Note Added: 16032
2011-08-11 19:12 DenisChenu Note Edited: 16032
2011-08-11 19:39 PinkPenguin Note Added: 16033
2011-08-11 19:46 PinkPenguin Note Edited: 16033
2011-08-11 20:32 DenisChenu Note Added: 16034
2011-08-12 08:24 worden Issue Monitored: worden
2011-08-12 13:09 DenisChenu Note Added: 16040
2011-08-12 14:18 PinkPenguin Note Added: 16041
2011-08-12 14:49 DenisChenu Note Added: 16042
2011-08-12 14:52 c_schmitz Note Added: 16043
2011-08-12 15:00 PinkPenguin Note Added: 16044
2011-08-12 15:56 DenisChenu Note Added: 16045
2011-08-12 15:56 DenisChenu Note Edited: 16045
2011-08-12 15:57 DenisChenu Note Edited: 16045
2011-08-12 16:06 DenisChenu Note Added: 16046
2011-08-12 16:06 DenisChenu Status assigned => closed
2011-08-12 16:06 DenisChenu Resolution open => fixed
2011-08-12 16:06 DenisChenu Fixed in Version => 1.91+
2019-11-01 17:25 c_schmitz Category Survey design => Survey editing
2021-08-02 19:13 guest Bug heat 6 => 8