Solved

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

Posted on 2010-11-26
7
511 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ejb on wildfly 5 30
add projects t working set in maven 2 24
Glassfish admin console not working 1 34
Problem to Alipay 10 46
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

830 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