Solved

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

Posted on 2010-11-26
7
504 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:cobol60
  • 4
  • 2
7 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 34218774
>>In debug mode in the data field i get a byte

Probably not valid data to make into a string
0
 
LVL 1

Author Comment

by:cobol60
ID: 34228464
the byte[] i get in debug mode is a raw field contained by java.sql.Array Object (implemented by the Oracle JDBC Driver).

0
 
LVL 1

Author Comment

by:cobol60
ID: 34228473
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:cobol60
ID: 34228583
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
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 34229394
You probably need to avoid calling getString so you can control encoding
0
 
LVL 1

Author Comment

by:cobol60
ID: 34230043
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
 
LVL 92

Expert Comment

by:objects
ID: 34234180
How did you fix the problem?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now