java XML parsing: line 1, character 38, unable to switch the encoding

While working on a Java web application I was trying to import an XML file into a Microsoft SQL Server database table with an XML type column. Everything seemed to be OK except I was running into the following error:

java XML parsing: line 1, character 38, unable to switch the encoding

After much googling the consensus was that this error is raised because SQL Server stores XML in the XML type column using UTF-16 character encoding while the file I was trying to insert was UTF-8 encoded. So, I had to convert my input data from UTF-8 to UTF-16. My original code was doing this:

String str = new String(data);

The String class defaults to UTF-16 unless specified otherwise. However, to be sure I changed the above to:

String str = new String(data, "UTF-16");

Nope, didn’t work… Well, the documentation for the String constructor isn’t the greatest so maybe the encoding parameter is just used as a hint at what the input data is supposed to be. The behavior of this constructor when the given bytes are not valid in the given charset is unspecified. Not entirely clear… Just to be sure, I changed it to this:

String str = new String(data);
str = new String(str.getBytes("UTF-16"), "UTF-16");

Still no luck. Ok, how about using CharsetEncoder/Decoder

Charset utf8= Charset.forName("UTF-8");
Charset utf16= Charset.forName("UTF-16");
CharBuffer cb = utf8.decode(new ByteBuffer.wrap(data));
ByteBuffer outputBuffer = utf16.encode(cb);
String str = new String(outputBuffer.array(), "UTF-16");

Still no. Well, looking at the XML document itself I see the XML declaration is specifying the encoding attribute as encoding="UTF-8". I delete it and finally it works. So, even though I changed the input to the correct encoding, the fact that this attribute was specified in the XML document itself caused SQL Server to complain. Ok fine… However, I ended up removing all the encoding conversion code that I had been playing with and reverted back to the original code:

String str = new String(data);

Tried the import again but without the encoding attribute and it still works. SQL server will do the conversion for you if the encoding attribute isn’t specified. It won’t touch it however if it is. So, options now are, 1) either remove the encoding attribute altogether, or 2) make sure the file is UTF-16 encoded with the encoding attribute set accordingly.

java XML parsing: line 1, character 38, unable to switch the encoding

While working on a Java web application I was trying to import an XML file into a Microsoft SQL Server database table with an XML type column. Everything seemed to be OK except I was running into the following error:

java XML parsing: line 1, character 38, unable to switch the encoding

After much googling the consensus was that this error is raised because SQL Server stores XML in the XML type column using UTF-16 character encoding while the file I was trying to insert was UTF-8 encoded. So, I had to convert my input data from UTF-8 to UTF-16. My original code was doing this:

String str = new String(data);

The String class defaults to UTF-16 unless specified otherwise. However, to be sure I changed the above to:

String str = new String(data, "UTF-16");

Nope, didn’t work… Well, the documentation for the String constructor isn’t the greatest so maybe the encoding parameter is just used as a hint at what the input data is supposed to be. The behavior of this constructor when the given bytes are not valid in the given charset is unspecified. Not entirely clear… Just to be sure, I changed it to this:

String str = new String(data);
str = new String(str.getBytes("UTF-16"), "UTF-16");

Still no luck. Ok, how about using CharsetEncoder/Decoder

Charset utf8= Charset.forName("UTF-8");
Charset utf16= Charset.forName("UTF-16");
CharBuffer cb = utf8.decode(new ByteBuffer.wrap(data));
ByteBuffer outputBuffer = utf16.encode(cb);
String str = new String(outputBuffer.array(), "UTF-16");

Still no. Well, looking at the XML document itself I see the XML declaration is specifying the encoding attribute as encoding="UTF-8". I delete it and finally it works. So, even though I changed the input to the correct encoding, the fact that this attribute was specified in the XML document itself caused SQL Server to complain. Ok fine… However, I ended up removing all the encoding conversion code that I had been playing with and reverted back to the original code:

String str = new String(data);

Tried the import again but without the encoding attribute and it still works. SQL server will do the conversion for you if the encoding attribute isn’t specified. It won’t touch it however if it is. So, options now are, 1) either remove the encoding attribute altogether, or 2) make sure the file is UTF-16 encoded with the encoding attribute set accordingly.

T-SQL Incorrect syntax near 'ERROR_MESSAGE'

Working on Microsoft SQL Server 2005 I was trying to raise an error doing the following:

BEGIN CATCH
  ...
  RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH

Looks pretty straight forward to me. So why the Incorrect syntax near 'ERROR_MESSAGE' error? Poking around the net it seems parameters passed to RAISERROR must be a constant or a variable. You cannot pass a function return value directly as a parameter. Even though I’ve seen examples like this one that use the above syntax, they don’t actually work. The correct way to write the above is like this:

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  ...
  SELECT
    @ErrorMessage=ERROR_MESSAGE(),
    @ErrorSeverity=ERROR_SEVERITY(),
    @ErrorState=ERROR_STATE();
  RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Further reading:

T-SQL Incorrect syntax near 'ERROR_MESSAGE'

Working on Microsoft SQL Server 2005 I was trying to raise an error doing the following:

BEGIN CATCH
  ...
  RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH

Looks pretty straight forward to me. So why the Incorrect syntax near 'ERROR_MESSAGE' error? Poking around the net it seems parameters passed to RAISERROR must be a constant or a variable. You cannot pass a function return value directly as a parameter. Even though I’ve seen examples like this one that use the above syntax, they don’t actually work. The correct way to write the above is like this:

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  ...
  SELECT
    @ErrorMessage=ERROR_MESSAGE(),
    @ErrorSeverity=ERROR_SEVERITY(),
    @ErrorState=ERROR_STATE();
  RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Further reading:

Broken JDK 7 Installer

Ok, how broken is this? I recently rebuilt my development machine and ran into this goodie while reloading my software development tools. I needed the Java JDK so, like any normal person, I went to Oracle’s website and downloaded the shiny new JDK 7 and Java EE 6 bundle. After running the downloaded installer I am confronted by this gem:

Error: Could not find the required version of the Java(TM) 2 Runtime Environment in'(null)'.

Huh? So doing some googling I see one suggestion that the download might have been incomplete so I download it again. No good. I found a post on Stack Overflow from someone trying to uninstall Java EE 6. The “fix” for that problem is that the uninstaller is written in Java so it needs a Java VM to run. Seriously? Yeah, so I try it. I install the basic Java 6 Runtime and hey! All fixed! Um, Oracle… You know that the JDK comes with its own runtime right? Now I have 2 runtimes installed… Your bundle installer couldn’t install its required runtime before launching the JDK installer? Even if you couldn’t be bothered to do something like that, how about a more descriptive message instead of your buggy “couldn’t find it in ‘(null)'” crap? I mean, I’m expecting the runtime to be included in the bundle so this tells me nothing about needing it to be already installed. It just looks like I got a corrupted download or just crap software.

Update 10/20/12

Ok, so it’s been a year since this original post and Oracle still hasn’t fixed anything. Not only that, I discovered a new issue, probably what one commenter below had run into but I didn’t see it at the time. I got a shiny new laptop the other day. 64-bit with Windows 7 Professional. So, setting up my tools I initially tried to install the JDK deliberately without first installing the runtime just to see if this issue has actually been fixed. It hasn’t. What I did discover is just as WTF worthy as the above problem. Since I got a hot new 64-bit computer, let’s install the 64-bit JDK. Ok cool. I run the installer and it didn’t work as I expected. So, I download the Java 7 runtime. Still doesn’t work. Ok, I used the Java 6 runtime before so I try that. Nope. WTF? Ok, I’m trying the 64-bit runtimes, surely they should work. I download the 32-bit Java 7 runtime. And it works! Seriously? WTF? So, you can’t install the 64-bit JDK with the 64-bit Java runtime? The 64-bit JDK installer only works with the 32-bit runtime. Not only is this not documented anywhere on Oracle’s website the error message presented is still the same worthless uninformative message.

]]>

PL/SQL: ORA-00984: column not allowed here

I ran into an unexpected error the other day using TOAD. I was trying to add a new column to a table which shouldn’t have been too difficult. It seems that while using that “add column” form that TOAD provides, you still need to put single-quotes around the default value (‘N’) in the default field.

ALTER TABLE users ADD (is_locked CHAR(1 BYTE) DEFAULT N); TOAD was giving me this! Grrr… Useful resources if you like:

]]>

PL/SQL: ORA-00984: column not allowed here

I ran into an unexpected error the other day using TOAD. I was trying to add a new column to a table which shouldn’t have been too difficult. It seems that while using that “add column” form that TOAD provides, you still need to put single-quotes around the default value (‘N’) in the default field.

ALTER TABLE users ADD (is_locked CHAR(1 BYTE) DEFAULT N); TOAD was giving me this! Grrr… Useful resources if you like:

]]>

MySQL on Ubuntu

I was installing MySQL on a Ubuntu VM to do some testing. Being lazy, I was following the directions posted on the How-To Geek blog which was a breeze. Until I actually tried to create my database.

$ mysqladmin create <databasename>
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Oops…

$ sudo !!
sudo mysqladmin create <databasename>
[sudo] password for testuser:
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Hmmm… oh yeah, password…

$ sudo mysqladmin -u root -p create <databasename>
Enter password:
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Ummm…. ok…. So after messing around for a while and Googling a lot, it seems the setup script that is run by Aptitude to create the installation doesn’t actually set the root password correctly. I found I had to do the following:

$ sudo stop mysqld
mysql stop/waiting
$ sudo mysqld_safe --skip-grant-tables &
110613 17:36:18 mysqld_safe Logging to syslog.
110613 17:36:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> update mysql.user set password=PASSWORD('password') where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 3  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
$ sudo mysqladmin -u root -p shutdown
Enter password:
110613 21:21:49 mysqld_safe mysqld from pid file /var/lib/mysql/Test-ubuntu.pid ended
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ sudo start mysql
mysql start/running, process 22598
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 34
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> exit
Bye
$ mysqladmin -u root -p create test
Enter password:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 36
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql>

Woo Hoo! Working now! What a PITA!]]>

MySQL on Ubuntu

I was installing MySQL on a Ubuntu VM to do some testing. Being lazy, I was following the directions posted on the How-To Geek blog which was a breeze. Until I actually tried to create my database.

$ mysqladmin create <databasename>
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Oops…

$ sudo !!
sudo mysqladmin create <databasename>
[sudo] password for testuser:
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Hmmm… oh yeah, password…

$ sudo mysqladmin -u root -p create <databasename>
Enter password:
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Ummm…. ok…. So after messing around for a while and Googling a lot, it seems the setup script that is run by Aptitude to create the installation doesn’t actually set the root password correctly. I found I had to do the following:

$ sudo stop mysqld
mysql stop/waiting
$ sudo mysqld_safe --skip-grant-tables &
110613 17:36:18 mysqld_safe Logging to syslog.
110613 17:36:19 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> update mysql.user set password=PASSWORD('password') where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 3  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
$ sudo mysqladmin -u root -p shutdown
Enter password:
110613 21:21:49 mysqld_safe mysqld from pid file /var/lib/mysql/Test-ubuntu.pid ended
[1]+  Done                    sudo mysqld_safe --skip-grant-tables
$ sudo start mysql
mysql start/running, process 22598
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 34
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> exit
Bye
$ mysqladmin -u root -p create test
Enter password:
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 36
Server version: 5.1.41-3ubuntu12.10 (Ubuntu)
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql>

Woo Hoo! Working now! What a PITA!]]>