Oracle JDBC : Getting strings "???" instead of the strings stored in the db

Hello !

I migrate an application from Weblogic 8 to 10 and Oracle 9i to 10g

I'm querying my database calling a stored procedure wich constructs arrays wich type is defined like that :

CREATE OR REPLACE
TYPE "XXXXX"."STRING_ARRAYTYP" IS TABLE OF CHAR(40) ;

i execute a CallableStatement and get the arrays via cs.getArray() stored in a java.sql.array.

In debug mode in the data field i get a byte[] : [-120, 1, -2, 0, 0, 2, 93, 1, 3, 0, -110, 8, 50, 48, 48, 56, 48, 51, 49, 51, 8, 50, 48, 48, 56, 48, 51, 49, 51, 8, 50, 48, 48, 56, 48, 51, 49, 51, 8, 50, 48, 48, 56, 48, 51, 49, 51, 8, 50, 48, 48, 56, 48, 51, 49, 51, 8, 50, 48, 48, 56, 48, 51, 49, 51 ...]

when i convert it to a string in debug mode i gives me : "[some special characters]200505252005052520050525200505252005052" which are my data.

but when i get the resultset and i call rs.getString(2), i get a "???" string (after having call rs.next() evidently)

The application worked before we migrate it.

Any idea ?
 
There're no special characters in my db that is only us-ascii.

Here's the code of my java app
public final static String QUERY = "call BASEINTER.PA_BASEINTER.PS_INIT_DATA_RF04(?,?,?,?,?,?,?,?,?,?,?,?)";

final CallableStatement statement = dbcon.prepareCall(QUERY);
try {
statement.setString(1,big_site);
statement.setInt(2,nu_tran);

statement.registerOutParameter(3,Types.INTEGER);
statement.registerOutParameter(4,Types.ARRAY, "DATE_ARRAYTYP");
statement.registerOutParameter(5,Types.ARRAY, "DATE_ARRAYTYP");
statement.registerOutParameter(6,Types.ARRAY, "NOMASS_ARRAYTYP");
statement.registerOutParameter(7,Types.ARRAY, "SITUAADMIN_ARRAYTYP");
statement.registerOutParameter(8,Types.ARRAY, "CODEGCN_ARRAYTYP");
statement.registerOutParameter(9,Types.ARRAY, "NUCAMP_ARRAYTYP");
statement.registerOutParameter(10,Types.ARRAY, "INTEGER_ARRAYTYP");
statement.registerOutParameter(11,Types.ARRAY, "IRRASS_ARRAYTYP");
statement.registerOutParameter(12,Types.ARRAY, "SITUAADMIN_ARRAYTYP");



statement.execute();



nbAss = statement.getInt(3);



arrayOutDateDeb = statement.getArray(4);
outDateDeb = remplirTableauString(arrayOutDateDeb);


arrayOutDateFin = statement.getArray(5);
outDateFin = remplirTableauString(arrayOutDateFin);


arrayOutNomAss = statement.getArray(6);
outNomAss = remplirTableauString(arrayOutNomAss);


arrayOutSituCour = statement.getArray(7);
outSituCour = remplirTableauString(arrayOutSituCour);


arrayOutCodeGCN = statement.getArray(8);
outCodeGCN = remplirTableauString(arrayOutCodeGCN);


arrayOutDernCamp = statement.getArray(9);
outDernCamp = remplirTableauBigDecimal(arrayOutDernCamp);


arrayOutCampDiff = statement.getArray(10);
isCampDiff = remplirTableauBoolean(arrayOutCampDiff);


arrayOutIrraFDC = statement.getArray(11);
outIrraFDC = remplirTableauBigDecimal(arrayOutIrraFDC);


arrayOutSituAdmin = statement.getArray(12);
outSituAdmin = remplirTableauString(arrayOutSituAdmin);

Open in new window


The remplirTableauString method :
private String[] remplirTableauString(Array ArraySQL){ 
String[] ArrayOut = null;
try{
ResultSet rs = ArraySQL.getResultSet();


ArrayList al = new ArrayList();


while(rs.next())
{
if(rs.getString(2) != null)
al.add(rs.getString(2).trim());
}


ArrayOut = new String[al.size()];
al.toArray(ArrayOut);


}catch(SQLException sql){}
catch(NullPointerException e){}


return ArrayOut;
}

Open in new window


The stored procedure :

  PROCEDURE ps_init_data_rf04 (
      in_big_site         IN       ass.ass_campagne.big_site%TYPE,
      in_nu_tran          IN       ass.ass_campagne.nu_tran%TYPE,
      ou_nb_ass           OUT      INTEGER,
      ou_date_debut_res   OUT      date_arraytyp,
      ou_date_fin_res     OUT      date_arraytyp,
      ou_nom_ass          OUT      nomass_arraytyp,
      ou_situa_admin      OUT      situaadmin_arraytyp,
      ou_code_gcn         OUT      codegcn_arraytyp,
      ou_nu_camp          OUT      nucamp_arraytyp,
      ou_diff_camp        OUT      integer_arraytyp,
      ou_irr_ass_fdc      OUT      irrass_arraytyp,
      ou_situa_prec       OUT      situaadmin_arraytyp
   )
   IS
      l_id_assemblage        ass.ass_situation.id_ass%TYPE;
      l_id_situation         ass.ass_situation.id_situa%TYPE;
      l_id_ass               idass_arraytyp;
      l_id_carac             idcarac_arraytyp;
      l_id_situa             idsitua_arraytyp;
      l_situa_admin          situaadmin_arraytyp;
      l_code_gcn             codegcn_arraytyp;
      l_date_debut_res       date_arraytyp;
      l_nom_ass              nomass_arraytyp;
      l_irr_ass_fdc          irrass_arraytyp;
      l_locali_prec          ass.ass_situation.locali_ass%TYPE;
      l_situa_prec           ass.ass_situation.situa_admin%TYPE;
      l_nu_camp_situa_actu   ass.ass_cycle.nu_camp%TYPE;
      l_diff_camp            INTEGER;
      l_nb_ass               INTEGER;
      i                      INTEGER;
      j                      INTEGER;
--
   BEGIN
--
        -- Initialisation des tableaux
      ou_date_debut_res := date_arraytyp (NULL);
      ou_date_fin_res := date_arraytyp (NULL);
      ou_nom_ass := nomass_arraytyp (NULL);
      ou_situa_admin := situaadmin_arraytyp (NULL);
      ou_code_gcn := codegcn_arraytyp (NULL);
      ou_nu_camp := nucamp_arraytyp (NULL);
      ou_irr_ass_fdc := irrass_arraytyp (NULL);
      ou_situa_prec := situaadmin_arraytyp (NULL);
      ou_diff_camp := integer_arraytyp (NULL);
      l_id_ass := idass_arraytyp (NULL);
      l_id_carac := idcarac_arraytyp (NULL);
      l_id_situa := idsitua_arraytyp (NULL);
      l_situa_admin := situaadmin_arraytyp (NULL);
      l_code_gcn := codegcn_arraytyp (NULL);
      l_date_debut_res := date_arraytyp (NULL);
      l_nom_ass := nomass_arraytyp (NULL);
      l_irr_ass_fdc := irrass_arraytyp (NULL);
--
          -- Initialisation des compteurs --
      i := 1;         -- compteur sur les elements renvoyes par le curseur --
      j := 1;
       -- compteur sur les elements a retourner au sous-programme appelant --
      l_nb_ass := 0;
      ou_nb_ass := 0;

--
      OPEN cu_maj_restriction (in_big_site, in_nu_tran);

      LOOP
         -- extend des tableaux
         l_id_ass.EXTEND;
         l_id_carac.EXTEND;
         l_id_situa.EXTEND;
         l_situa_admin.EXTEND;
         l_code_gcn.EXTEND;
         l_date_debut_res.EXTEND;
         l_nom_ass.EXTEND;
         l_irr_ass_fdc.EXTEND;

         -- Lecture des elements associes a la campagne courante --
         FETCH cu_maj_restriction
          INTO l_id_ass ( i), l_id_carac ( i), l_id_situa ( i),
               l_situa_admin ( i), l_code_gcn ( i), l_date_debut_res ( i),
               l_nom_ass ( i), l_irr_ass_fdc ( i);

         EXIT WHEN cu_maj_restriction%NOTFOUND;
         i := i + 1;
      END LOOP;

      CLOSE cu_maj_restriction;

--
          -- Nombre d'assemblages retenus par le curseur --
      l_nb_ass := i - 1;

--
      FOR i IN 1 .. l_nb_ass
      LOOP
         -- extend des tableaux
         ou_code_gcn.EXTEND;
         ou_date_debut_res.EXTEND;
         ou_date_fin_res.EXTEND;
         ou_nom_ass.EXTEND;
         ou_situa_admin.EXTEND;
         ou_nu_camp.EXTEND;
         ou_diff_camp.EXTEND;
         ou_irr_ass_fdc.EXTEND;
         ou_situa_prec.EXTEND;

         -- Lecture numéro de campagne situation actuelle --
         IF l_situa_admin (i) IN
               ('EN ATTENTE REPARATION', 'EN RESERVE POUR GESTION FUTURE',
                'A DISPOSITION DE LA DAC')
         THEN
            l_nu_camp_situa_actu := fs_lire_derniere_camp (l_id_ass (i));
         END IF;

         -- Creation de nouvelles restrictions : determination de la cause --
         -- et de la nature de la restriction pour chaque assemblage --
         IF l_situa_admin (i) IN
                  ('EN ATTENTE REPARATION', 'EN RESERVE POUR GESTION FUTURE')
         THEN
            ou_code_gcn (j) := '    ';
            ou_date_debut_res (j) := l_date_debut_res (i);
            ou_date_fin_res (j) := '     ';
            ou_nom_ass (j) := l_nom_ass (i);
            ou_situa_admin (j) := l_situa_admin (i);
            ou_code_gcn (j) := l_code_gcn (i);
            ou_nu_camp (j) := l_nu_camp_situa_actu;
            ou_diff_camp (j) := 0;
            ou_irr_ass_fdc (j) := l_irr_ass_fdc (i);
            ou_situa_prec (j) := ' ';
            j := j + 1;
--
--
               -- Levee de restrictions existantes --
         ELSIF l_situa_admin (i) = 'A DISPOSITION DE LA DAC'
         THEN
            -- Lecture situation precedente et recup numéro de campagne --
            l_id_assemblage := l_id_ass (i);
            l_id_situation := l_id_situa (i);
            ps_lire_situa_prec (l_id_assemblage,
                                l_id_situation,
                                l_locali_prec,
                                l_situa_prec
                               );
            ps_diff_camp (l_id_assemblage, l_id_situation, l_diff_camp);

--
                    -- Selection des assemblages 'a disposition de la dac'dont situation --
                    -- precedente etait 'en attente  reparation', --
                    -- ou 'en reserve pour gestion future' --
            IF     (l_locali_prec = 'BK')
               AND (   l_situa_prec = 'EN ATTENTE REPARATION'
                    OR (l_situa_prec = 'EN RESERVE POUR GESTION FUTURE')
                   )
            THEN
               ou_code_gcn (j) := '    ';
               ou_date_debut_res (j) := l_date_debut_res (i);
               ou_date_fin_res (j) := TO_CHAR (SYSDATE, 'YYYYMMDD');
               ou_nom_ass (j) := l_nom_ass (i);
               ou_situa_admin (j) := l_situa_admin (i);
               ou_code_gcn (j) := l_code_gcn (i);
               ou_nu_camp (j) := l_nu_camp_situa_actu;
               ou_diff_camp (j) := l_diff_camp;
               ou_irr_ass_fdc (j) := l_irr_ass_fdc (i);
               ou_situa_prec (j) := l_situa_prec;
               j := j + 1;
            END IF;
--
         END IF;
--
      END LOOP;

--
          -- Nombre d'assemblages utiles --
      ou_nb_ass := j - 1;
--
   END ps_init_data_rf04;

Open in new window


i'm getting a String[] of length=27 and containing 27 strings equals to "???"

Any idea on what's happenning ?

I upgraded the oracle jdbc driver to the last 10 version ... it did'nt solve the problem.

Thank's
LVL 1
cobol60Asked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
You probably need to avoid calling getString so you can control encoding
0
 
CEHJCommented:
>>In debug mode in the data field i get a byte

Probably not valid data to make into a string
0
 
cobol60Author Commented:
the byte[] i get in debug mode is a raw field contained by java.sql.Array Object (implemented by the Oracle JDBC Driver).

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
cobol60Author Commented:
in the data field, the byte before the real string data : "20050525" is equal to 8, ans that's the length of this String.
0
 
cobol60Author Commented:
if i do a getArray on the java.sql.Array, this method returns an Object[]. In debug mode i can see that these objects are of type String.

Another precision, i also get arrays of BigDecimal[] and Boolean[], and that feature works good
0
 
cobol60Author Commented:
it should be encoding. I get know arrays of NCHAR and it works normally.

The strange thing is that on standard queries (not returning arrays or not calling stored proc, i don't really know) the getString returns the right string.
0
 
objectsCommented:
How did you fix the problem?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.