title: Mapping hibernate datatypes to output from stored procedure of mixed types
body: <p>Have a problem mapping datatypes from stored procedure output to a java datatype</p>
  
  <p>Using a SQLServer,
  I am given the following 2 tables initially - can't change their definitions.</p>
  
  <pre><code>CREATE TABLE [dbo].[tblDetail](
      ...
  [dCdeType] [dbo].[Tcde] NOT NULL,
      [dCdeState] [dbo].[Tvch] NOT NULL,
      ...
  )
  
  CREATE TABLE [dbo].[tblAP](
      ...
  [apChState] [char](2) NOT NULL, 
  [apChTaxType] [char](2) NOT NULL,
      ...
  )
  </code></pre>
  
  <p>I have created a 3rd table - whose definition I can change if I have to.</p>
  
  <pre><code>CREATE TABLE [dbo].[tblTempAP](
      ...
  [tpChState] [char](2) NOT NULL, 
  [tpChTaxType] [char](2) NOT NULL,
      ...
  )
  </code></pre>
  
  <p>I have a stored procedure "sp_MyProcedure", that is called multiple times from Java application using Hibernate (version 3.1.3).<br>
  Stored procedure makes a select from tblDetails and inserts records into tblTempAP.  And then it does the same from tblAP</p>
  
  <pre><code>select &lt;list of fiields&gt; into #tempD from tblDetails
  ...some logic...
  INSERT INTO [dbo].[tblTempAP]
         (...
         ,[tpChType]
         ,[tpChState]
          ...
    )
  select &lt;list of fields&gt; from #TempD
  ............some logic .....
  
  select &lt;list of fiields&gt; into #tempA from tblAP
  ...
      INSERT INTO [dbo].[tblTempAP]
         (...
         ,[tpChType]
         ,[tpChState]
          ...
    )
  select &lt;list of fields&gt; from #TempA
  </code></pre>
  
  <p>There can be "Updates" as well along "some logic" to the table tblTempAP based on some criterias.  But the 2 fields I am questioning - datatypes are the same.
  "sp_MyProcedure" us called in a loop from Java with different parameters every time, so it is guaranteed that data inserted into tblTempAP is different every time.</p>
  
  <p>At the end of the stored procedure, i do </p>
  
  <pre><code>select &lt;some fileds, including the 2 mentioned&gt; 
  from tblTempAP
  where &lt;some criterias&gt;
  </code></pre>
  
  <p>Output is taken in Java and processed, accumulated based on business logic.</p>
  
  <p><strong>Java code</strong></p>
  
  <pre><code>String queryString = "{ call sp_MyProcedure(?) }";
  Query q;
      List resultList;
      try {
         q = session.createSQLQuery(queryString);
         q.setInteger(0, port);
                 resultList = q.list();
      } 
      catch (HibernateException hex) {
          throw new TaxPartnersSystemException("My Exception");
      }
  </code></pre>
  
  <p><strong>Here is the problem, I need help with !!!</strong>
  At the end of the application run, I have populated tblTempAP, and it looks fine when looked at in SQL server management studio.
  However, the data in resultList on the java side has a problem - I see it with debugger, inspecting elements - The state that came has ONLY one character - so whatever in the tblTempAP is "NY" would be "N", "CA" - would be "C".
  Same for type field.  Those which were single character were correct.  However, something like "03" - would be "0", "34" would come out as "3".  </p>
  
  <p>I think it's a mapping inside hibernate API</p>
  
  <p>Please, help !!  Where is problem? And how to figure it out? Remember, I can't change the 2 initial tables - it's a given.</p>
  
  <p>I do not use Cast or Convert in stored procedure.  If I should, please tell me where ? when selecting from first 2 tables?  or when insert/updating the 3rd ?</p>
  
  <p>Thanks in advance</p>