Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jelf
Starting Member
5 Posts |
 Posted - 2011-12-17 : 14:25:59
|
| I'm writing PHP code to read a database someone else designed and which resides on MSSQL server 2005.Problem: There is a Description column which is type varchar. When my code tries to select the Description column I get a generic server error message.I can select other column types just fine.This has to be super simple but I'm gong blind looking for the answer.Here are some things I tried which did not work:$SQL = 'SELECT top 1000 Lat,Lng,Title,Address,CAST(Description as nvarchar) FROM Locations WHERE Title != ""';$SQL = 'SELECT top 1000 Lat,Lng,Title,Address,CONVERT(Description(100),textcoloumn) FROM Locations WHERE Title != ""';I'm competent with PHP but this is my first foray into SQL land. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-17 : 15:34:22
|
| What, exactly, is the error?Can you try and run those queries from a Management Studio query window and see what, if any, error is returned?Couple things...CAST(Description as nvarchar) - Why convert to nvarchar, especially nvarchar without a length (defaults to 30). Might also need an alias, not sure how PHP handles columns without names. To alias you'd say CAST(Description AS NVARCHAR(50)) AS UnicodeDescription (the alias can be anything that's a legal identifier)CONVERT(Description(100),textcoloumn) - That's syntatically wrong. That should be CONVERT (NVARCHAR(50), Description) AS UnicodeDescriptionHave you tried a straightforward select with no conversions?SELECT top 1000 Lat,Lng,Title,Address, Description FROM Locations WHERE Title != ""If so, what happens?p.s. You have a TOP without an Order By. Are you aware that means any 1000 rows, not guaranteed to be the same 1000 on repeated executions?--Gail ShawSQL Server MVP |
 |
|
|
Jelf
Starting Member
5 Posts |
Posted - 2011-12-18 : 00:17:04
|
| Many thanks for the info Gail.I'll study it in the morning.As you can tell, I have a lot to learn.Joseph |
|
|
|
Jelf
Starting Member
5 Posts |
Posted - 2011-12-18 : 23:24:41
|
| I'll confess that I do not know anything about Management Studio. I thought, perhaps naively, that it would be fairly straight forward to learn how to get all records from a MSSQL table into my PHP script. A long time ago I was a mainframe database programmer (IDMS) so I am well acquainted with database concepts.The error is an all purpose one. No matter what goes wrong the only error I see is:"500 - Internal server error.There is a problem with the resource you are looking for, and it cannot be displayed."There are less than 1000 records in the table. So if I do:SELECT top 1000 Lat,Lng,Title,Address FROM Locationsthen I get those columns for all the records.However, if I add the Description column like so:SELECT top 1000 Lat,Lng,Title,Address,Description FROM Locationsthen I get the unhelpful error message noted above.Using Gail's advice I tried this SELECT which executed OK:$SQL = 'SELECT top 1000 Lat,Lng,Title,Address,CAST(Description AS NVARCHAR(50)) AS UnicodeDescription FROM Locations';I know the above SELECT worked since I wrote a script that ended after this command.But when I extended my script and tried to load the alias into a PHP array, I got the generic error message:$SQL = 'SELECT top 1000 Lat,Lng,Title,Address,CAST(Description AS NVARCHAR(50)) AS UnicodeDescription FROM Locations';$result = mssql_query($SQL);while($fet_tbl = MSSQL_Fetch_Assoc($result)) { $lat[] = $fet_tbl[Lat]; $lng[] = $fet_tbl[Lng]; $title[] = $fet_tbl[Title]; $address[] = $fet_tbl[Address]; $description[] = $fet_tbl[UnicodeDescription]; <== causes error message}Let's bubble up please. What is the recommended way to SELECT all the content of a varchar column?I suspect that whoever designed the database was not very knowledgeable since the Locations table I am reading has 2 fields named ‘Lat’, 2 named ‘Lng’, and 3 named ‘Title’. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-19 : 05:16:50
|
| SELECT varcharcolumn FROM tablenameNothing fancier than that. No casts, no converts, nothing.Did you try what I asked and run them in Management Studio? If so, did you get an error (SQL error, not a HTTP 500)? We need to know if this is a SQL error or a PHP error. From what you've said, it sounds more like a PHP error not a SQL one.--Gail ShawSQL Server MVP |
|
|
|
Jelf
Starting Member
5 Posts |
Posted - 2011-12-19 : 13:13:04
|
| Gail,Thanks again for generously sharing your knowledge.I was trying to do this as a favor for a neighbor and just told him I am bailing out on the project. Obviously there is more to learn than I have time to absorb.Case closed.Joseph |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-19 : 14:30:46
|
| Lol. Probably a good move... :-)--Gail ShawSQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Jelf
Starting Member
5 Posts |
Posted - 2011-12-20 : 10:28:29
|
Surprise! Old guys can code.The project on which I bailed was to read a MSSQL database with information, including latitude and longitude, on wineries in Washington State, USA. There are 600+. The output was to be a delimited text file format that I designed.That text file (as well as some other formats) can be read by Gmap4, which is an enhanced Google map viewer I wrote. As soon as I finish updating the docs then I will promote the latest beta into production. Gmap4 will then have a geolocate feature.After my neighbor finds someone to produce that delimited text file then you will be able to go cruising in wine country with Gmap4 running in your phone's browser. Trigger the 'Findme' feature and you will see your location on the map plus symbols for the nearby wineries. Click a winery symbol for more info.The link below only shows a small part of the wine data. The 'Findme' feature will show up under the 'Menu' as soon as I update the production code (maybe later today).http://www.mappingsupport.com/p/gmap4.php?t=m&label=on&q=http://www.terroir360.com/gmap4_test/list_01.txtOf course if you happen to be in South Africa this particular wine data is not terribly useful to you. On the other hand, Gmap4 (just like Google maps) can display coordinate based information for anywhere on the planet.Finally, if you are curious to know more about Gmap4 you can follow the link in the lower left corner of the map.  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Stumped trying to select a varchar field