Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's not about actual code... so.... and it's database stuff... I hope this is the right spot and I hope I can get an answer. (if not, tell me where I should ask; plz, thnx).

I am working with PHP 5.2.4-2 and Sql Server 2005. To query, I must use a PHP function called mssql_query() and it apparently has an issue with some of the datatypes being returned. The solution to this error is to cast those things to compatible datatypes (varchar(max) or text). PHP treats everything as a string so not retaining something as a true integer or datetime or something is not an issue for me.

PHP error:

Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)

I am working with many stored procedures that I did not write myself and it would be a hassle to go through all of them to check and cast everything necessary. Since I can't even query in the PHP without getting an error (so I can't even get column names) and I am not working with an actual query (select col_name_1, col_name_2 from tbl) this must all be done in the database.

I was thinking a stored procedure to dynamically put together a query that casts each column from the original sp. This isn't syntactically correct. Pseudo-sql.

so... if exec sp_test_1 returns fields id, field1, field2, field3.....

a wrapper SP... will have to... build the query and I will have to painfully get all the column names and everything:

select cast(id as varchar(max)) as id, 
    cast(field1 as varchar(max)  as field1, 
    cast(field2 as varchar(max)  as field2..
from (exec sp_test_1) 

or something like that. I haven't really thought out the details because I'm not sure if this is the best route. I built something like this a year or so ago... I bet I can bring it back into my active memory in my head if I try hard enough.

Is this the route to go? Or will that ultimately kill performance and make every query I do through the PHP take absolutely forever?

I ran this by my friend who is strictly a front-end guy and he simply said, "uhm... that can't be the best way to do that..." but he rarely works in the database.

I can build this if needed. I am scared of performance issues. I can go through all 30+ extremely messy stored procedures that I didn't write and check for the bad datatypes and then include the cast in the SP, but these will be updated eventually by people who won't understand the importance of my changes (even if told directly and repeatedly) and that would just be an on-going battle.

If this is right idea (wrapper sp that dynamically builds a query to cast all returned columns as good data types)...?

I might start working on some quick n dirty sql do to this while I'm waiting for answers just to test out how I would put the wrapper together. If I come up with anything truly functional I'll post it as I go...

Edit 1: actually, I'm having a hard time even figuring out what the column names for the resulting table of an SP. (does that sentence make sense? I can't just join sysobjects and syscolumns for an SP, i guess)

Edit 2: Nevermind. As soon as I typed that the next google search came up with answers...

share|improve this question
    
I'm not familiar with PHP internals, but from the error message, is it possible you could use a newer version of the DB library that supports this functionality? –  Jon Seigel Feb 1 '13 at 18:28
    
not an option... well, it is but... it will be substantially easier to handle it in the scope of this one project. –  gloomy.penguin Feb 1 '13 at 18:37
    
also, I'm not great with PHP but I am usually excellent in the database... so I generally default to always let the db be responsible for outputting exactly what I need to whatever is building the front end. –  gloomy.penguin Feb 1 '13 at 18:43
    
odd, i've never seen that error in my setup. Couldn't tell you the config of my set up though, it's been as is for about 3 years now. –  DForck42 Feb 1 '13 at 19:31
    
@DForck42 if you search for the error text a lot comes up... My PHP version is up there and the error is pretty descriptive. This is specific to SQL Server (that I'm aware of). Dunno... I think I might give this one up and just go through and manually cast everything and then complain later when people don't follow requirements for new stuff or modifications. –  gloomy.penguin Feb 1 '13 at 22:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.