Need help with inserting non-standard characters into SQL from CF
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','£','pound;')
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:353754
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 firew...@cc.uk.com 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','£','pound;')
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
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 r...@michaels.me.uk 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 firew...@cc.uk.com 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','£','pound;')
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
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 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 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
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 p...@sustainablegis.com 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
