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.
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.
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...