Answered by:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
Question
-
I have a forum written in Perl and I am attempting to get it working with MSSQL Server 2008 using the ODBC SQL Server Driver. So far so good, however I occasionally get this error: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018). The error is generated when I try to insert to a table. Here's the Perl code that creates the problem:
$time = time; # time is a datatype and can't be passed as an insert parameter
I have a separate Perl module to interface to the database. Here is the constructor and relevant methods:
$getlist = SQL->new('activelist');
$getlist->where('[time]|<|OR','[user]|=');
$getlist->delete($time-(60*$activeuserslog),$buser);
if($buser ne '') {
$getlist = SQL->new('activelist');
$getlist->into('[time]','guest','[user]','[view]','boardid','messageid');
$getlist->insert($time ,$guest,$buser,$URL{'v'},$URL{'b'},$URL{'m'});
}
sub new { my($class,$table,$varval,$debug) = @_; my $self = { _table => $table, _where => undef, _columns => '*', _order => undef, _group => undef, _limit => 1, _into => undef, _set => undef, _debug => $debug || undef }; $self->{_varval} = $varval eq '' ? int(rand(time)) : $varval; @{"_$self->{_varval}"} = (); bless($self,$class); return($self); } sub into { my($self) = $_[0]; my(@into) = @_; my($sql); shift @into; foreach(@into) { $sql .= "$_,"; } $sql =~ s/,\Z//g; $self->{_into} = $sql; } sub insert { my($self) = $_[0]; my(@query) = @_; my($questions,$sql); shift @query; foreach(@query) { $questions .= "?,"; } $questions =~ s/,\Z//g; $sql = "INSERT INTO [$main::forumprefix$self->{_table}] ($self->{_into}) VALUES ($questions)"; error($sql) if($self->{_debug}); $sth = $dbh->prepare($sql); eval { $data = $sth->execute(@query) or error($sth->errstr."\n\n$sql\n\n$count\n\n"); }; if($@) { error($@); } $sth->finish; ++$stats->{_inserts}; # $stats->{_stats} .= "<i>INSERT</i> ($self->{_into}) <strong>$self->{_table}</strong></strong><br />"; #return($dbh->last_insert_id('','','','','')); return($sth->{mssql_insertid}); }This is the output that the ODBC driver tells me when I try to run the code:[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018) INSERT INTO [eblah_activelist] ([time],guest,[user],[view],boardid,messageid) VALUES (?,?,?,?,?,?)
The values are ? because that's how the query builder makes them.
Let me know if you need any more information. Thanks much.Wednesday, March 17, 2010 4:53 AM
Answers
-
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
INSERT INTO [eblah_activelist] ([time],guest,[user],[view],boardid,messageid) VALUES (?,?,?,?,?,?)
Invalid character value for cast specification comes from the driver when:
1.) It detects a string -> non-string conversion (e.g. string -> time or string -> boardid or string->messageid)
2.) It detects that a character is out of the domain for the conversion (e.g. converting 'bob' to int)
In this case, I would look at time, boardid, and messageid unless user/guest/view are also non-string values on the server. If those values are not integers, that would be the problem. You can either change the server type to a string type (if you want to allow strings) or you can tune up the application logic to ensure that you don't get strings here (if you don't want to allow strings).
Hope that helps,
John
This post is provided 'as is' and confers no express or implied warranties or rights.- Marked as answer by John C GordonMicrosoft employee Wednesday, March 17, 2010 6:35 PM
Wednesday, March 17, 2010 6:35 PM
All replies
-
what is the exact select stament before you execute. i think you are holding the value in a string.
Please mark the post as answered to help others to choose the best.
chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)Wednesday, March 17, 2010 5:52 AM -
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)
INSERT INTO [eblah_activelist] ([time],guest,[user],[view],boardid,messageid) VALUES (?,?,?,?,?,?)
Invalid character value for cast specification comes from the driver when:
1.) It detects a string -> non-string conversion (e.g. string -> time or string -> boardid or string->messageid)
2.) It detects that a character is out of the domain for the conversion (e.g. converting 'bob' to int)
In this case, I would look at time, boardid, and messageid unless user/guest/view are also non-string values on the server. If those values are not integers, that would be the problem. You can either change the server type to a string type (if you want to allow strings) or you can tune up the application logic to ensure that you don't get strings here (if you don't want to allow strings).
Hope that helps,
John
This post is provided 'as is' and confers no express or implied warranties or rights.- Marked as answer by John C GordonMicrosoft employee Wednesday, March 17, 2010 6:35 PM
Wednesday, March 17, 2010 6:35 PM -
Thanks for the tips. If forgot to list the data types for each column. Here is the table creation script with each column defined. Could the error be arising from that 'time' is a column? I know that time is a data type. I may try renaming the column to timestamp and see if that fixes the problem. The function 'time' returns the current time in seconds in Perl as an integer. I know that it's most likely from trying to assign a string to an integer field. I'll play with it some more and let you know what I find. EDIT: Well, renaming the column didn't fix the problem. I'll have to track down where a string is being passed to an int.
CREATE TABLE [IGADB].[eblah_activelist]( [user] [varchar](25) NOT NULL, [guest] [int] NOT NULL, [time] [int] NOT NULL, [view] [varchar](20) NULL, [boardid] [int] NULL, [messageid] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [IGADB].[eblah_activelist] ADD DEFAULT ('0') FOR [guest] GO ALTER TABLE [IGADB].[eblah_activelist] ADD DEFAULT (NULL) FOR [view] GO ALTER TABLE [IGADB].[eblah_activelist] ADD DEFAULT (NULL) FOR [boardid] GO ALTER TABLE [IGADB].[eblah_activelist] ADD DEFAULT (NULL) FOR [messageid] GO- Edited by pcmantinker Wednesday, March 17, 2010 9:52 PM Renaming the column didn't fix
Wednesday, March 17, 2010 9:40 PM -
With the help of one of my friends, I was able to figure out the issue. It is related to the insertid not being returned. The parameters expect a messageid which isn't being supplied since an insertid isn't being returned when a message is posted. I will need to return the last identity value from the posts table and pass it to the activelist table when a member is inserted into the list.
I found that SELECT IDENT_CURRENT(‘tablename’) will return the last identity for a specified table. I will need to figure out how to return an int so that I can pass it to the INSERT statement.
Friday, March 19, 2010 2:54 AM -
It looks like you have solved the problem of connecting to SQL Server from Perl. I am also trying to connect from Perl to SQL Server but need to use "Windows NT authentication" instead of "SQL Server ID/Password authentication". Do you know of anyone who is successfully connecting to SQL Server from Perl using Windows NT authentication?
The bottom message in the post linked below contains more details of my environment and the steps I've taken so far.
Pass User Id and Password to SQL Server DSN Using Windows AuthenticationSunday, January 8, 2012 12:46 AM