View Issue Details

IDProjectCategoryView StatusLast Update
0010488mantisbtdb oraclepublic2014-12-08 00:34
ReportermsierszenAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.1.7 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0010488: Inserting strings > 4000 Bytes not working -> direct file upload and email
Description

The direct file upload using "INSERT ..." statement is not working with oracle as there is a 4000 Bytes size limit.

This problem occurs as well in email_queue.

Solution:
Use function UpdateBlob()
See patchfile for details.

Tagsoracle, patch

Relationships

related to 0010996 closeddregad Cant use Mantis with oracle9 - var binding fails 
related to 0013227 closeddregad Oracle DB support multiple issues 
related to 0007248 closedvboctor file upload to disk (via 'report issue') not possible 

Activities

msierszen

msierszen

2009-07-31 07:59

reporter  

mantis_fileupload_1.1.7-2.patch (4,046 bytes)
diff -rupN mantisbt-1.1.7/core/email_queue_api.php mantisbt-1.1.7_zce/core/email_queue_api.php
--- mantisbt-1.1.7/core/email_queue_api.php	2009-04-20 09:14:00.000000000 +0200
+++ mantisbt-1.1.7_zce/core/email_queue_api.php	2009-05-13 11:16:17.053875000 +0200
@@ -62,6 +62,7 @@
 
 	# --------------------
 	function email_queue_add( $p_email_data ) {
+		global $g_db;
 		$t_email_data = email_queue_prepare_db( $p_email_data );
 
 		# email cannot be blank
@@ -89,22 +90,44 @@
 		$c_body = $t_email_data->body;
 		$c_metadata = serialize( $t_email_data->metadata );
 
-		$query = "INSERT INTO $t_email_table
-				    ( email,
-				      subject,
-					  body,
-					  submitted,
-					  metadata)
-				  VALUES
-				    ( '$c_email',
-				      '$c_subject',
-				      '$c_body',
-					  " . db_now() . ",
-					  '$c_metadata'
-					)";
-		db_query( $query );
-
-		return db_insert_id( $t_email_table );
+		$t_db_type = config_get( 'db_type' );
+		if($t_db_type == 'oci8') {
+			$query = "INSERT INTO $t_email_table
+					    ( email,
+					      subject,
+						  body,
+						  submitted,
+						  metadata)
+					  VALUES
+					    ( '$c_email',
+					      '$c_subject',
+					      empty_clob(),
+						  " . db_now() . ",
+						  '$c_metadata'
+						)";
+			db_query( $query );
+
+			$id = db_insert_id($t_email_table);
+			$g_db->UpdateClob($t_email_table,'body', $c_body, "EMAIL_ID = $id");
+		} else {
+			$query = "INSERT INTO $t_email_table
+					    ( email,
+					      subject,
+						  body,
+						  submitted,
+						  metadata)
+					  VALUES
+					    ( '$c_email',
+					      '$c_subject',
+					      '$c_body',
+						  " . db_now() . ",
+						  '$c_metadata'
+						)";
+			db_query( $query );
+			$id = db_insert_id( $t_email_table );
+		}
+ 
+		return $id;
 	}
 
 	# --------------------
diff -rupN mantisbt-1.1.7/core/file_api.php mantisbt-1.1.7_zce/core/file_api.php
--- mantisbt-1.1.7/core/file_api.php	2009-04-20 09:14:00.000000000 +0200
+++ mantisbt-1.1.7_zce/core/file_api.php	2009-05-13 11:17:27.757000000 +0200
@@ -571,6 +571,7 @@ document.getElementById( span ).style.di
 
 	# --------------------
 	function file_add( $p_bug_id, $p_tmp_file, $p_file_name, $p_file_type='', $p_table = 'bug', $p_file_error = 0, $p_title = '', $p_desc = '' ) {
+		global $g_db;
 
 		if ( php_version_at_least( '4.2.0' ) ) {
 		    switch ( (int) $p_file_error ) {
@@ -678,11 +679,22 @@ document.getElementById( span ).style.di
 		$t_file_table	= config_get( 'mantis_' . $p_table . '_file_table' );
 		$c_id = ( 'bug' == $p_table ) ? $c_bug_id : $c_project_id;
 					
-		$query = "INSERT INTO $t_file_table
-						(" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content)
-					  VALUES
-						($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", $c_content)";
-		db_query( $query );
+		$t_db_type = config_get( 'db_type' );
+		if($t_db_type == 'oci8') {
+			$query = "INSERT INTO $t_file_table
+							(" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content)
+						  VALUES
+							($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", empty_blob())";
+			db_query( $query );
+			$id = db_insert_id($t_file_table);
+			$g_db->UpdateBlobFile($t_file_table,'content', $p_tmp_file, "ID = $id",'BLOB');
+		} else {
+			$query = "INSERT INTO $t_file_table
+							(" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content)
+						  VALUES
+							($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", $c_content)";
+			db_query( $query );
+		}
 
 		if ( 'bug' == $p_table ) {
 			# updated the last_updated date
msierszen

msierszen

2009-07-31 08:00

reporter   ~0022633

Use the second patch.
The first patch file had a bug. Please delete it.

ViperNet

ViperNet

2009-07-31 12:03

reporter   ~0022637

Last edited: 2009-08-08 09:05

View 2 revisions

wherever a CLOB field is inserted, or updated, take care to use the "UpdateClob()" function with the right "where" clause parameter.

for example in email_queue.php, the "where" clause parameter will look like:
"EMAIL_ID = $id"

in mantis_config_table there is a 'primary key' with 3 fields:
'config_id', 'project_id', 'user_id'
so in "config_api.php" inthe config_set() function you must add a new row below the "$result = db_query( $t_set_query );":
$g_db->UpdateClob($t_config_table,'VALUE', $c_value, "config_id = '$c_option' AND project_id = $c_project AND user_id = $c_user");

damien_b

damien_b

2009-08-11 09:26

reporter   ~0022714

Blob is not needed, here is what we do:

in email_queue_api
$query = "INSERT INTO $t_email_table
( email,
subject,
body,
submitted,
metadata)
VALUES
( '$c_email',
'$c_subject',
:body,
" . db_now() . ",
'$c_metadata'
)";
$params = array('body' => $c_body);
db_query( $query, -1, -1, $params );

db_query must be modified as well, in database_api.php:
function db_query( $p_query, $p_limit = -1, $p_offset = -1, $val = false ) {
global $g_queries_array, $g_db;

    $t_start = microtime_float();

    if ( ( $p_limit != -1 ) || ( $p_offset != -1 ) ) {
        $t_result = $g_db->SelectLimit( $p_query, $p_limit, $p_offset, $val );
    } else {
        $t_result = $g_db->Execute( $p_query, $val);
    }
vboctor

vboctor

2009-10-29 00:12

manager   ~0023468

I'm wondering if this is resolved in 1.2.x since we now use db_query_bound()

$query = "INSERT INTO $t_email_table
                ( email,
                  subject,
                  body,
                  submitted,
                  metadata)
              VALUES
                ( " . db_param() . ",
                  " . db_param() . ",
                  " . db_param() . ",
                  " . db_param() . ",
                  " . db_param() . "
                )";
db_query_bound( $query, Array( $c_email, $c_subject, $c_body, db_now(), $c_metadata ) );
damien_b

damien_b

2009-10-29 09:48

reporter   ~0023472

It's too much work for us to try to migrate to 1.2.x right now (we haven't even finished to cleanly extract our patches from 1.1.x), but reading the Param implementation in the oci8 ADOdb driver, that should indeed work.

watergad

watergad

2009-10-29 16:22

reporter   ~0023475

Whatever I do with 1.1.8 and 1.2.0 it doesn't work exactly because of var bindings ( 0010996 ).
Oracle 9i.

Issue History

Date Modified Username Field Change
2009-05-14 10:54 msierszen New Issue
2009-05-14 10:54 msierszen File Added: mantis_fileupload_1.1.7.patch
2009-05-15 13:06 jreese Tag Attached: oracle
2009-07-31 07:59 msierszen File Added: mantis_fileupload_1.1.7-2.patch
2009-07-31 08:00 msierszen Note Added: 0022633
2009-07-31 12:03 ViperNet Note Added: 0022637
2009-08-08 09:05 ViperNet Note Edited: 0022637 View Revisions
2009-08-11 09:26 damien_b Note Added: 0022714
2009-10-28 20:05 dhx Tag Attached: patch
2009-10-29 00:12 vboctor Note Added: 0023468
2009-10-29 00:12 vboctor Status new => feedback
2009-10-29 09:48 damien_b Note Added: 0023472
2009-10-29 16:22 watergad Note Added: 0023475
2010-08-04 16:19 atrol File Deleted: mantis_fileupload_1.1.7.patch
2011-09-09 12:31 dregad Relationship added related to 0013227
2012-12-19 19:45 dregad Relationship added related to 0007248
2012-12-20 03:56 dregad Relationship added related to 0010996
2013-10-17 12:52 dregad Target Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Status feedback => resolved
2013-10-17 12:52 dregad Fixed in Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Resolution open => fixed
2013-10-17 12:52 dregad Assigned To => dregad
2014-04-12 19:39 grangeway Issue cloned: 0017196
2014-12-08 00:34 vboctor Status resolved => closed