Re: Inserting Blobs-Images Blobs

[Carlos Proal]

Dear Dave and Paolo:

  Im attaching part of my currently code used to update different values 
(including images, in fact every file); im using an special class containing 
couples column-value (SQLArguments), Dave im using a different kind to 
contruct the preparedStatement so check it out. The example also works with 
inserts, changing update... to insert into ...; also im not using the ? 
inside the where clause but i dont think this is the problem with you.

     If you have any questions please feel free to email me :)

P.D. Dont forget to create tables like:
       create table figure(image blob);
     The size is not a problem im using animated gifs around 500kb
     the blob size is about 65555(2^16 -1 )


>From: "Calistra Research Labs" <dave@stripped>
>Reply-To: dave@stripped
>To: "'java@stripped'" <java@stripped>
>Subject: Inserting Blobs
>Date: Tue, 8 Feb 2000 11:00:49 +0800
>
>
>I am using the following code to try to insert images of over 70K- but the 
>code dies on the
>update command. It dies either with the commented code (setBytes) or the 
>uncommented code
>(setBinaryStream)
>
>	pStmt = new org.gjt.mm.mysql.PreparedStatement( Conn, "update pictures set 
>image_data=? where id=?","");
>
>		picFile = new File("d:\\catalog\\" + image_filename);
>		picFileLength = picFile.length();
>		label_1.setText( "transfer file to DB "+picFileLength);
>		picFileStream = new FileInputStream( picFile );
>		/*
>		picArray = new byte[  (int)picFileLength ];
>		result = picFileStream.read( picArray, 0, (int)picFileLength );
>		picFileStream.close();
>		pStmt.setBytes(1,picArray);
>		*/
>		pStmt.setBinaryStream(1,picFileStream,(int)picFileLength);
>		pStmt.setInt(2,id);
>		pStmt.executeUpdate();
>
>Debug log connecting to localhost:7777
>java.lang.ArrayIndexOutOfBoundsException
>         at org.gjt.mm.mysql.Buffer.writeBytesNoNull(Buffer.java:352)
>         at 
>org.gjt.mm.mysql.PreparedStatement.executeUpdate(PreparedStatement.java:291)
>         at MainForm.MainForm_objectCreated(MainForm.java:341)
>         at MainForm.create(MainForm.java:196)
>         at PortfolioMySQL.createAppletForm(PortfolioMySQL.java:24)
>         at PortfolioMySQL.init(PortfolioMySQL.java:19)
>         at sun.applet.AppletPanel.run(AppletPanel.java:286)
>         at java.lang.Thread.run(Thread.java:466)
>Dave Appleton		Calistra Research Labs Pte Ltd
>Pager : 9-327-9-739	111 North Bridge Rd #21-01
>Fax : [phone]		Peninsular Plaza
>                         Singapore 179098
>Machine Vision Systems  Republic of Singapore
>Interactive Voice Response Systems Development
>IT Consultants & Custom Software Development
>mySQL Database Consultants / Developers
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail java-thread702@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail java-unsubscribe@stripped instead.
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

        previously......
        Connection connection = 
DriverManager.getConnection(completeURL.toString(),
		                                         databaseUsername,
		                                         databasePassword
		                                        );

	public int updateValues(String tableName,
	                         Vector arguments,
	                         String whereCondition) throws SQLException
	{
		//gets the number of arguments in the vector
		int vectorSize = arguments.size();
		//creates the SQL query in the following way:
		//   update TABLENAME set ARGUMENTNAME = ?, ... where WHERECONDITION
		//each ? represents a column which is going to be built next
		StringBuffer query = new StringBuffer("update ");
		query.append(tableName);
		query.append(" set ");

		//for each argument in the vector add ARGUMENTNAME = ?
		for(int i=0;i<vectorSize;i++)
		{
			query.append((String)((SQLArguments) arguments.elementAt(i)).getName());
			query.append(" = ? ");


			if(i < vectorSize - 1)
				query.append(",");
		}

		query.append(" where ");
		query.append(whereCondition);
		query.append(";");


		PreparedStatement ps = connection.prepareStatement(query.toString());

		for(int i=0;i<vectorSize;i++)
		{
			//gets the i elements in the vector
			SQLArguments sqlArgs = (SQLArguments) arguments.elementAt(i);

			//what type is the argument i?
			switch (sqlArgs.getArgumentValueType())
			{
				case SQLArguments.STRING:
				   ps.setString(i + 1, (String) sqlArgs.getValue());
					break;
	 			case SQLArguments.BYTE:
	 				ps.setByte(i + 1, ((Byte) sqlArgs.getValue()).byteValue());
	 				break;
	 			case SQLArguments.INTEGER:
	 				ps.setInt(i + 1, ((Integer) sqlArgs.getValue()).intValue());
	 				break;
	 			case SQLArguments.LONG:
	 				ps.setLong(i + 1, ((Long) sqlArgs.getValue()).longValue());
	 				break;
	 			case SQLArguments.BOOLEAN:
	 				ps.setBoolean(i + 1, ((Boolean) sqlArgs.getValue()).booleanValue());
	 				break;
	 			case SQLArguments.FLOAT:
	 				ps.setFloat(i + 1, ((Float) sqlArgs.getValue()).floatValue());
	 				break;
	 			case SQLArguments.DOUBLE:
	 				ps.setDouble(i + 1, ((Double) sqlArgs.getValue()).doubleValue());
	 				break;
	 			case SQLArguments.DATE:
	 				ps.setDate(i + 1, (java.sql.Date) sqlArgs.getValue());
	 				break;
	 			case SQLArguments.SHORT:
	 				ps.setShort(i + 1, ((Short) sqlArgs.getValue()).shortValue());
	 				break;
	 			case SQLArguments.TIME:
	 				ps.setTime(i + 1, (Time) sqlArgs.getValue());
	 				break;
	 			case SQLArguments.TIME_STAMP:
	 				ps.setTimestamp(i + 1, (Timestamp) sqlArgs.getValue());
	 				break;
	 			case SQLArguments.OBJECT:
	 				ps.setObject(i + 1, (Object) sqlArgs.getValue());
	 				break;
	 			case SQLArguments.FILE:
	 				FileInputStream inputStream = null;
	 				int fileLength = Integer.MIN_VALUE;

	 				try
	 				{
	 					inputStream = new FileInputStream((File) sqlArgs.getValue());
	 					fileLength = (int)((File) sqlArgs.getValue()).length();
	 					ps.setBinaryStream(i + 1, inputStream, fileLength);
	 				}
	 				catch(FileNotFoundException fnfe)
	 				{}

	 			default:;
	 		}
		}

		return (ps.executeUpdate());
	}