[Clob]Update d'un champ Clob
Bonjour tout le monde,
Je n'arrive désespérement pas à mettre à jour un Clob (initialement null) dans ma BD Oracle 10g.
J'utilise le dernier driver ojdbc14.jar que j'ai téléchargé hier, mais rien à faire.
Voici les différentes solutions que j'ai essayé.
Avec un StringReader:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| String sqlCmd = "UPDATE catalog SET modification_date = SYSDATE, status = 1, description = '"
+ this.getDescription() + "', search_text = '?' WHERE obj_id = " + this.getId();
logger.fine("CatalogItem.updateCatalog().sqlCmd: " + sqlCmd);
pst = conn.prepareStatement(sqlCmd);
// pst.executeUpdate(sqlCmd);
// Save data to store in the CLOB
StringBuffer myClobValue = new StringBuffer("Clob non vide");
// Retrieve all data from the attributes to populate the CLOB
DataTypeAttribute[] dataTypeAttributes = getDataType().getAttributes();
for (int i = 0; i < dataTypeAttributes.length; i++)
{
logger.fine(" -> attributes[i].getName(): "+attributes[i].getName());
// Append the value to the CLOB buffer if the att is global
// searchable and its value is not null.
if (attributes[i].isGlobalSearchable()
&& this.getAttributeValue(attributes[i].getName()) != null
&& !this.getAttributeValue(attributes[i].getName()).equals("null"))
{
myClobValue.append(this.getAttributeValue(attributes[i].getName()));
myClobValue.append(";");
}
}
logger.fine(" -> ClobValue: " + myClobValue.toString());
stringReader = new StringReader(myClobValue.toString());
pst.setCharacterStream(1,stringReader,myClobValue.toString().length());
pst.executeUpdate(sqlCmd); |
Avec un AsciiStream
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| String sqlCmd = "UPDATE catalog SET modification_date = SYSDATE, status = 1, description = '"
+ this.getDescription() + "', search_text = '?' WHERE obj_id = " + this.getId();
logger.fine("CatalogItem.updateCatalog().sqlCmd: " + sqlCmd);
pst = conn.prepareStatement(sqlCmd);
// pst.executeUpdate(sqlCmd);
// Save data to store in the CLOB
StringBuffer myClobValue = new StringBuffer("Clob non vide");
// Retrieve all data from the attributes to populate the CLOB
DataTypeAttribute[] dataTypeAttributes = getDataType().getAttributes();
for (int i = 0; i < dataTypeAttributes.length; i++)
{
logger.fine(" -> attributes[i].getName(): "+attributes[i].getName());
// Append the value to the CLOB buffer if the att is global
// searchable and its value is not null.
if (attributes[i].isGlobalSearchable()
&& this.getAttributeValue(attributes[i].getName()) != null
&& !this.getAttributeValue(attributes[i].getName()).equals("null"))
{
myClobValue.append(this.getAttributeValue(attributes[i].getName()));
myClobValue.append(";");
}
}
logger.fine(" -> ClobValue: " + myClobValue.toString());
InputStream is = new ByteArrayInputStream(myClobValue.toString().getBytes());
pst.setAsciiStream(1, is, myClobValue.toString().length());
pst.executeUpdate(sqlCmd); |
En instanciant d'abord mon clob en Empty Clob :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| String sqlCmd = "UPDATE catalog SET modification_date = SYSDATE, status = 1, description = '"
+ this.getDescription() + "' WHERE obj_id = " + this.getId();
logger.fine("CatalogItem.updateCatalog().sqlCmd: " + sqlCmd);
pst = conn.prepareStatement(sqlCmd);
pst.executeUpdate(sqlCmd);
//Ici les données sont mise à jour correctement !
// Save data to store in the CLOB
StringBuffer myClobValue = new StringBuffer("Clob non vide"); //Juste pour m'assurer que dans tous les cas je mets au moins quelque chose dans les clob
// Retrieve all data from the attributes to populate the CLOB
DataTypeAttribute[] dataTypeAttributes = getDataType().getAttributes();
for (int i = 0; i < dataTypeAttributes.length; i++)
{
logger.fine(" -> attributes[i].getName(): "+attributes[i].getName());
// Append the value to the CLOB buffer if the att is global
// searchable and its value is not null.
if (attributes[i].isGlobalSearchable()
&& this.getAttributeValue(attributes[i].getName()) != null
&& !this.getAttributeValue(attributes[i].getName()).equals("null"))
{
myClobValue.append(this.getAttributeValue(attributes[i].getName()));
myClobValue.append(";");
}
}
logger.fine(" -> ClobValue: " + myClobValue.toString());
// Create the CLOB in the database (or reset it)
stmt = conn.createStatement();
stmt.execute("UPDATE catalog SET search_text=empty_clob() WHERE obj_id = " + this.getId());
// Then retrieve its instance
String sqlQuery = "SELECT search_text FROM catalog WHERE obj_id = " + this.getId();
ResultSet clobRs = stmt.executeQuery(sqlQuery);
// Only one loop since the select filters on the obj_id.
while (clobRs.next())
{
// Get the lobs
CLOB textStringClob = ((OracleResultSet) clobRs).getCLOB("search_text");
// and populate it
DataBaseUtil.fillClob(textStringClob, myClobValue.toString());
}
clobRs.close(); |
Avec mon fillClob comme suit :
Code:
1
2
3
4
5
6
7
| public static void fillClob(CLOB clob, String buff) throws SQLException, IOException
{
Writer outstream = clob.getCharacterOutputStream();
outstream.write(buff, 0, buff.length());
outstream.close();
} |
Et rien ne marche !
Dans les 2 premiers cas, j'ai un clob dont la longueur vaut 1 et le contenu est '?' (sans les quotes. Qu'elles y soient ou pas dans la requête), et dans le dernier cas, j'ai un clob vide ! (length = 0).
