Re: Need help with inserting non-standard characters into SQL from CF
The short-circuit worked like a dream Paul, problem solved. Thank you very much for the assistance :) On 4 January 2013 11:18, Paul Hastings wrote: > > On 1/4/2013 6:08 PM, Edward Chanter wrote: > > > > Thanks Paul, the data isn't coming from a form it's being manually > entered > > you mean from a static cf page? and is that page UTF-8? where's the pound > symbol > coming from? > > if you're not using cfqueryparam, make sure to use unicode hinting > (N'text'). > > > Interestingly your reply to this thread shows on my system with my £ > > replaced with a �. That suggests fonts somewhere along the line... > > that's the way it came thru the mail servers. > > > if you want to short circuit this, use the unicode codepoints for those > symbols > > ie, N'#chr(163)#' for the pound sterling symbol. > N'#chr(8364)#' for the euro, etc. > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353760 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
On 1/4/2013 6:08 PM, Edward Chanter wrote: > > Thanks Paul, the data isn't coming from a form it's being manually entered you mean from a static cf page? and is that page UTF-8? where's the pound symbol coming from? if you're not using cfqueryparam, make sure to use unicode hinting (N'text'). > Interestingly your reply to this thread shows on my system with my £ > replaced with a �. That suggests fonts somewhere along the line... that's the way it came thru the mail servers. if you want to short circuit this, use the unicode codepoints for those symbols ie, N'#chr(163)#' for the pound sterling symbol. N'#chr(8364)#' for the euro, etc. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353759 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
Thanks Paul, the data isn't coming from a form it's being manually entered in the CFM and will only be run once hence the lack of cfqueryparams, I suspect that the problem is something to do with the fonts which was why I was thinking that the linux CF and windows DB might be the problem. Interestingly your reply to this thread shows on my system with my £ replaced with a �. That suggests fonts somewhere along the line... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353758 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
On 1/4/2013 5:34 PM, Edward Chanter wrote: > When I insert it as N'�' I still get question marks but these have black > diamond shaped boxes around them. either the data input isn't unicode in the first place (ie. from a form on a page that's not UTF-8 encoding) or its that the data isn't being displayed properly (ie you're not using the correct font, more likely). btw you should be using cfqueryparam & set the appropriate options in cfadmin for that DSN (ie under the advanced menu turn on the "Enable High ASCII characters and Unicode for data sources configured for non-Latin characters" option). ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353757 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
Thanks Russ, I was using the JTDS driver which doesn't have that setting so I changed it to the MS SQL driver and ticked the relevant box. It's still not working though. Given that I deleted and recreated the DSN should I restart the server? On 4 January 2013 10:41, Russ Michaels wrote: > > If memory serves there is a setting in your dsn you need to change to > enable utf8 > > Regards > Russ Michaels > www.michaels.me.uk > www.cfmldeveloper.com - Free CFML hosting for developers > www.cfsearch.com - CF search engine > On Jan 4, 2013 10:35 AM, "Edward Chanter" wrote: > > > > > First off, happy new year all! > > > > I'm sure the answer to this is really simple but I've been banging my > head > > against a wall for a few hours so thought I'd ask my friendly > neighbourhood > > gurus. > > > > I have a database table that needs to store currency symbols both as HTML > > chars (%pound;), text code (ie. GBP) and the symbol (£) > > > > The database table has the fields as UNICODE (nvarchar) and when I > manually > > paste the rows in via windows and SMSS I can put in currency symbols and > > every other strange character I could find including japanese chars and > > other stuff. They are all stored by SQL just fine and I can return the > data > > in a cfquery without any issues. > > > > However when I try and run an insert query via CF, for example: > > > > insert into currencies > > (title,code,symbol,htmlsymbol) > > values > > ('Pounds','GBP','£','£') > > > > The £ gets converted to ?? > > > > When I insert it as N'£' I still get question marks but these have black > > diamond shaped boxes around them. > > > > I thought it was the database collation or something but as I said I can > > type these characters into the table in my SMSS console and they are > saved > > without a problem. > > > > We're running a linux CF server and windows database server, could that > be > > the problem? If so is there anyway I can get it working? I'm really > > struggling to understand this problem so if anyone has encountered it > > before and knows a solution or can point me in the right direction for > some > > reference material then I would be most grateful. > > > > Thanks in advance ; > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353756 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
If memory serves there is a setting in your dsn you need to change to enable utf8 Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 4, 2013 10:35 AM, "Edward Chanter" wrote: > > First off, happy new year all! > > I'm sure the answer to this is really simple but I've been banging my head > against a wall for a few hours so thought I'd ask my friendly neighbourhood > gurus. > > I have a database table that needs to store currency symbols both as HTML > chars (%pound;), text code (ie. GBP) and the symbol (£) > > The database table has the fields as UNICODE (nvarchar) and when I manually > paste the rows in via windows and SMSS I can put in currency symbols and > every other strange character I could find including japanese chars and > other stuff. They are all stored by SQL just fine and I can return the data > in a cfquery without any issues. > > However when I try and run an insert query via CF, for example: > > insert into currencies > (title,code,symbol,htmlsymbol) > values > ('Pounds','GBP','£','£') > > The £ gets converted to ?? > > When I insert it as N'£' I still get question marks but these have black > diamond shaped boxes around them. > > I thought it was the database collation or something but as I said I can > type these characters into the table in my SMSS console and they are saved > without a problem. > > We're running a linux CF server and windows database server, could that be > the problem? If so is there anyway I can get it working? I'm really > struggling to understand this problem so if anyone has encountered it > before and knows a solution or can point me in the right direction for some > reference material then I would be most grateful. > > Thanks in advance ; > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353755 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm