1

I'm doing some php & SQL Server 2005 in a database with accents ( é , è , à ) in both tables names , columns names and fields . Unfortunately , I'm not the owner/creator of this database , but I agree that the owner must be slapped :) .

Im using ODBC driver to connect to the SQL Server odbc_connect($dsn,$user,$password).

My problem is that every fields with accents is not recognized . For example : despite having 7000 fields with the name "Réseau"

$query="Select * from dbo.Table where col1= 'Réseau'"

gives 0 results No rows found

Same thing for :

$query="Select * from [dbo].[Tablé]

giving an sql error : 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Object name 'dbo.Tablé' not valid., SQL state S0002 in SQLExecDirect in...

And , sadly , none of the tips i found over the net helped , like :

'Réseau' , Réseau, [Réseau] , COLLATE SQL_Latin1_General_Cp437_CI_AI/French_CI_AS ...

For more info , I'm using charset UTF-8 , my database collate is French_CI_AS . And all the querys above works perfectly on Access or Query Tools (Using ODBC) .

  • * EDITED to clarify more *
Strange bug
  • 11
  • 1
  • 3
  • Have you tried setting the collation to UTF8? – libjup Jun 06 '12 at 08:06
  • Change the database. I always walk when I see that stuff - serioulsly. People just not thinking further than their nose when making the basic infrastructure of a company. Nice, standard english names always. – TomTom Jun 06 '12 at 08:15
  • @TomTomThe database is not mine, and i just have access to views , that s why i can t change database – Strange bug Jun 06 '12 at 08:22
  • Your first query should work fine, and so should the second as long as you put the table name in square brackets: `[SpecialTableWithé]` Have you used SQL Profiler to check that the SQL being send to the server is indeed the SQL that you wrote? What is the data type of `col1` in your first query, and what is the database collation? Are you able to reproduce this in a small, standalone script? – Pondlife Jun 06 '12 at 08:23
  • @TomTom I sympathize with your point, but on the other hand the world is full of developers who do not speak English and see no reason not to use entity names in their own language if their tools allow it. A far worse situation in my experience is developers who misspell English words everywhere, making it impossible to search their code. – Pondlife Jun 06 '12 at 08:25
  • They can go and work somewhere else. I am NOT a native english speaker, and all projects I was invovled in in the last 15 years were english. I live in Poland (not polish, expat) and all the stuff we do here for ourselves is - in english. People learn or go to some other place. – TomTom Jun 06 '12 at 08:38
  • @Ponflife : txs for the answer , so my database collate is French_CI_AS . [SpecialTableWithé] is not working either ( forget to put [] in the example ) . col1 is a varchar , most fields are "standard" . I just can 't select those with accents in em – Strange bug Jun 06 '12 at 08:53
  • @Tomtom , i can t see you point . Im a french guy working for a french customer ? since I dont think that you will answer my question if I 've asked it in french , I wrote it in SOF language : English . anyway , txs for your "help" ... – Strange bug Jun 06 '12 at 08:53
  • From message "Object name 'SpecialTableWithé'" I conclude that query text is sent to driver as UTF8, but it interprets it as ANSI. Maybe these keywords help somehow (I've no experience with such scenario). – Arvo Jun 06 '12 at 08:53
  • Thanks for the details but you didn't say if you've used SQL Profiler to see the actual SQL being sent to the server. That might confirm @Arvo's suspicion that the source code encoding and/or driver is the real issue. – Pondlife Jun 06 '12 at 08:56
  • @Arvo Actually , my page have charset=iso-8859-1 , that's why accents ( coded as 2 bytes ) are shown as é ( 1 byte each ) . If i use charset=UTF-8 , my browser will display é correctly . on the other hand , when i use UTF-8 , an imported "é" from the database , it s displayed as ? , whereas in ISO-8859-1 , its displayed correctly . Thats why i ve chosen the iso charset . if i wana use é in a form , i type é – Strange bug Jun 06 '12 at 09:05
  • let me check if i have enough rights to launch the profiler , if i can 't , ill have to wait until tomorrow ;/ – Strange bug Jun 06 '12 at 09:08
  • Do you have rights to create views? If yes, then you could just create proxying views with changed (latinized) column and view names. – Arvo Jun 06 '12 at 10:36
  • I have the right to , but when i try it gives exactly the same error as above . – Strange bug Jun 11 '12 at 10:17

2 Answers2

0

try 

$q="Select * from DatabaseTable where col1= N'Réseau'"
techBeginner
  • 3,792
  • 11
  • 43
  • 59
0

Try setting your locale in your page, I found setting mine helped with all the special characters :

setlocale (LC_ALL, 'nl_BE');
PhDJ
  • 173
  • 1
  • 4
  • 15