If I use the types 204 or 205 I get a "type invalid" error when assigning
the value to the parameter. (I get similar errors trying other types).
The 201 (adLongVarChar) type works if the size is no greater than 32,152
bytes.
Thanks,
Bo Durban
Is it possibly that the loParameter.Type below says long vartype? Have
you tried the Binary types(adLongVarBinary 204 and adVarBinary 205)? Have
you tried sending through just a tiny portion of the binary file to make
sure you have data-types and conversions correct, and it's not simply a
size limitation somewhere?
--
Derek
> Thanks for the suggestion Derek. I hadn't tried that yet.
> Unfortunately it
> returned the same error from the OLEDB provider:
>
> OLE IDispatch exception code 0 from OraOLEDB: ORA-01460:
> unimplemented or
> unreasonable conversion requested..
>
> Thanks,
> Bo Durban
>
>
>
>
> Derek Said:
>
> Does Oracle have an OLEDB/ADO provider you can use
> directly with ADO
> instead of passing everything through ODBC?
>
> --
> Derek
>
> > Anyone have experience passing ~2MB sized data to an Oracle SP using
> > SQLEEXEC( ) or ADO? I need to pass entire files to an Oracle
> > SP as an input
> > parameter of type BLOB or CLOB but cannot get it to work.
> >
> > Thanks,
> > Bo Durban
> >
> >
> > ** VFP Example:
> > nSQL = SQLSTRINGCONNECT( cOracleConnect )
> > cData = FILETOSTR("Document.pdf") && 2MB file
> > nResult = SQLEXEC(nSQL,"{call insert_file_blob(?cData)}") &&
> > C'005 error
> >
> > cData = FILETOSTR("Document.pdf") && 2MB file
> > cData = STRCONV(cData, 13) && Convert to base64
> > nResult = SQLEXEC(nSQL,"{call insert_file_clob(?cData)}") &&
> > C'005 error
> >
> >
> > ** ADO Example:
> > cData = FILETOSTR("Document.pdf") && 2MB file
> > cData = STRCONV(cData, 13) && Convert to base64
> >
> > loConnection = CREATEOBJECT( "ADODB.Connection" )
> > loConnection.CursorLocation = 2 && server-side cursor
> > loConnection.Open( cOracleConnect )
> >
> > loCommand = CREATEOBJECT( "ADODB.Command" )
> > loCommand.CommandType = 4 && by SP name
> > loCommand.CommandText = "insert_file_clob"
> > loCommand.ActiveConnection = loConnection
> >
> > loParameter = loCommand.CreateParameter()
> > loParameter.Name = "p_lob" && name we assign
> > loParameter.Type = 201 && long varchar string type
> > loParameter.Direction = 1 && input parameter
> > loParameter.Size = LEN(cData) && maximum length
> > loParameter.Value = cData && initialize the parameter
> > loCommand.Parameters.Append( loParameter )
> >
> > loCommand.Execute() && Error:
> > && OLE IDispatch exception code 0 from Microsoft OLE
> DB Provider
> > && for ODBC Drivers: [Oracle][ODBC][Ora]ORA-01460:
> unimplemented
> > && or unreasonable conversion requested
> >
> > RELEASE loCommand
> > loConnection.Close
> > RELEASE loConnection
>
> [excessive quoting removed by server]
>
[excessive quoting removed by server]
[excessive quoting removed by server]
©2002 wdurban /AT/ datascantech DO.T com |
