Andrew I
|
| Posted: 07/17/2002, 10:33 AM |
|
CCS 1.7 PHP MySQL
Project works fine on localhost, but uploading to server causes following error on running Login (and other pages)
The error is caused by the ` ` round the field names (remove them, and the SQL runs fine).
I've set all the properties I can find: Code Language=PHP4 with templates; Connection->Database=MySQL; PHP Database Library=MySQL.
Database error: Invalid SQL: SELECT `u_ID`, `u_LevelD` FROM `njp_users` WHERE `u_Login`='a' AND `u_password`='b'
MySQL Error: 1064 (You have an error in your SQL syntax near '`u_ID`, `u_LevelD` FROM `njp_users` WHERE `u_Login`='a' AND `u_password`='b'' at line 1)
Session halted.
Thanks
Andrew
|
|
|
 |
Ken
|
| Posted: 07/17/2002, 3:52 PM |
|
Sounds to me like you have two different versions of MySQL running. Or two different versions of PHP? I am not sure, but you may want to check settings on the servers. I know with SQL Server 2000 you have a setting for quoted identifers, is there a similar setting with MySQL?
Run this script to check your information.
<?php
echo phpinfo();
?>
|
|
|
|
Brent
|
| Posted: 07/17/2002, 8:01 PM |
|
You don't need the ` ` around field names unless they contain spaces. I would
strongly recommend replacing the spaces with "_" and removing the quotes.
From the MySQL manual:
6.1.2 Database, Table, Index, Column, and Alias Names
Database, table, index, column, and alias names all follow the same rules in MySQL.
Note that the rules changed starting with MySQL Version 3.23.6 when we introduced quoting
of identi ers (database, table, and column names) with ``'. `"' will also work to quote
identi ers if you run in ANSI mode. See Section 1.4.3 [ANSI mode], page 48.
|
|
|
|
Andrew I
|
| Posted: 07/18/2002, 1:28 AM |
|
Thanks for the suggestions.
Sorry, my question wasn't very clearly put. It's not me but CCS that is putting the ` round field & table names.
You've put me in the right direction, though. When I run the query as shown by the error message in local mySQL it runs, but not on the server MySQL. Server is version 3.22.23. Local is 3.23.37. The server is OK with all other quote marks (' ") but not the backquotes.
The server is not run by me, so changing PHP/MySQL settings is difficult. is there anything in CCS that can alter the quoting of names? (I don't have any spaces in names in any case).
I should add that Standard Code Charge (i.e. not studio) worked fine on the same server.
Any suggestions appreciated.
Rgds
Andrew
|
|
|
|
CodeCharge Support
|
| Posted: 07/18/2002, 4:30 AM |
|
Hello,
it is CCS feature, but not a bug. The quotes are added as field names delimiters. It was made in order to let users use e.g. reserve words as field names or use spaces.
In case you do not want quotes to be applied to the field names you should edit ..CodeChargeStudio\Components\Databases\MySQL.xml file. Open it, find lines:
fieldLeftDelim="`"
fieldRightDelim="`"
and remove delimiter symbol:
fieldLeftDelim=""
fieldRightDelim=""
|
|
|
|
Andrew I
|
| Posted: 07/18/2002, 9:01 AM |
|
Thanks - that solution worked fine.
My ISP is running MySQL version 3.22.23 - which dates from mid 1999.
Not only does it not allow ` ` round fields, but it also doesn't allow INNER JOINs with an ON clause. CCS Query builder creates INNER JOIN ... ON clauses, so this is requiring more manual editing of SQL clauses.
I'm asking the ISP about updating. Can anyone say what version they reasonably ought to be offering (if not the latest)? What version was CCS designed to work with?
Thanks
Andrew
|
|
|
|
|
