PICTURE SAVING IN ORACLE WITH PLSQLCOMMAND

[remi1@hotmail.com]

Hello :

I was saving picture of my customers in the database (oracle database 10g) (client 11g 32 bits)
CREATE TABLE PREDIO_PHOTO
(
ANOPRO VARCHAR2(4 BYTE),
CODIPRE VARCHAR2(12 BYTE),
TIPFOT VARCHAR2(1 BYTE),
CODZON VARCHAR2(3 BYTE),
CODSEC VARCHAR2(2 BYTE),
CODMZA VARCHAR2(2 BYTE),
PHOTO LONG
);

When i use the insert stament from Gupta Sql windows Version 3.0 PTF4 build13612 everythig goes ok.
So i decide to create a store procedure to do the same

CREATE OR REPLACE PACKAGE RENTAS.PACK_PREDIO AS
PROCEDURE Pack_Predio_Photo(
PARCODPRE IN VARCHAR2,
PARIMGPHO IN LONG,
PARCODERR IN OUT NUMBER,
PARMSGERR IN OUT VARCHAR2
);
END PACK_PREDIO;
/
CREATE OR REPLACE PACKAGE BODY RENTAS.PACK_PREDIO AS
PROCEDURE Pack_Predio_Photo(
PARCODPRE IN VARCHAR2,
PARIMGPHO IN LONG,
PARCODERR IN OUT NUMBER,
PARMSGERR IN OUT VARCHAR2 ) IS
HORA DATE;
BEGIN
INSERT INTO PREDIO_PHOTO (ANOPRO, CODIPRE, TIPFOT, CODZON, CODSEC, CODMZA, PHOTO)
VALUES ('2011','123456789012','F','123','12','12',PARIMGPHO);

COMMIT;
parcoderr := 0;
EXCEPTION
WHEN OTHERS THEN
PARCODERR := SQLCODE;
PARMSGERR := SQLERRM;
END;
END PACK_PREDIO;
/
From my program invoke that store procedure
!!CB!! 174
Call SqlPLSQLCommand( hSql, "RENTAS.PACK_PREDIO.Pack_Predio_Photo(sArchivo, lsPhoto, nCodErr, sMsgErr)" )
If nCodErr != 0
Call SalMessageBox (sMsgErr,'Error en el Procedure', MB_Ok)

And Gupta Sql windows colapse and close without any error. I hope you can help me to solve this because it's gonna be a masive upload.
The picture is 2 MB size.

thanks in advance
Remigio

[ferchou2]

Hola,
Revisa como tenes setado el parámetro longbuffer y uselob en la sección "oragtwy" de tu SQL.INI:


[oragtwy]
remotedbname=base,@TNSNAME
nodefparse=yes
longbuffer=10000000
fetchrow=10
uselob=1




Para manipular fotos con Oracle, yo uso el siguiente esquema:

La columna ARCHIVO de la tabla pruebas es CLOB.

Pushbutton: pbLeer
On SAM_Click
Set nBuffers = VisFileGetSize( 'C:\\FOTOS\\FER.JPG')
Call SalFileOpen( fhFile, 'C:\\FOTOS\\fer.JPG', OF_Binary | OF_Read )
Call SalFileRead( fhFile, lsData, nBuffers )
Call SalPicSetImage( pic1, lsData, PIC_ImageTypeJPEG )
Call SalFileClose( fhFile )

Pushbutton: pbGrabar
On SAM_Click
Call SqlPrepare( hsqlOracle, "Insert Into sysadm.prueba ( ARCHIVO) values ( :lsData )" )
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute( hsqlOracle)
Call SqlCommit( hsqlOracle )
Call SqlPrepare( hsqlOracle, "update sysadm.prueba set path= 'fer.jpg' " )
Call SqlExecute( hsqlOracle)
Call SqlCommit( hsqlOracle )

Pushbutton: pbSelect
On SAM_Click
Call SqlPrepareAndExecute( hsqlOracle, "SELECT ARCHIVO INTO :lsData from sysadm.prueba " )
Call SqlFetchNext( hsqlOracle, nFetch )
Call SqlCommit( hsqlOracle )
Call SalPicSetImage( pic1, lsData, PIC_ImageTypeJPEG )