I am getting error: SQLCode -301

[Chrisma]

hi guys,

I need someone to help me to figure out what is wrong to my program. Everytime I run my program I always recieved a message:

SQLCODE = -301, error: The value of input host variable or parameter number 005 cannot be used as specified because of its Data type.

I used DB2 for my database.

Here is my code program

Dim commandADO As New ADODB.Command, prmADO As New ADODB.Parameter
Dim szReturn As String, szConnect As String
Dim mstream As ADODB.Stream
Dim strArgs() As String
Dim strTimeStamp As String
Dim strQueueStatus As String
Dim strBLOBFile As String
Dim strCustImageID As String
Dim strUserID As String
Dim strPassword As String
Dim strDSN As String
Dim lngSize As Long
Dim strSuccess As String
Dim bolInterLock As Boolean

strSuccess = "0"
bolInterLock = True 'by default we want to use interlockin to prevent multiple copies of InsertBLOB to
'overwrite each others results.

'Put command line arguments into array
strArgs = Split(Command, " ")

'ensure all arguments were passed
If UBound(strArgs) < 3 Then
Err.Raise vbObjectError + 1, , USAGE_INSTRUCTIONS
End If
'Check and assign timestamp
If (Len(strArgs(0)) <> 26) _
Or (Mid(strArgs(0), 5, 1) <> "-") _
Or (Mid(strArgs(0), 8, 1) <> "-") _
Or (Mid(strArgs(0), 11, 1) <> "-") _
Or (Mid(strArgs(0), 14, 1) <> ".") _
Or (Mid(strArgs(0), 17, 1) <> ".") _
Or (Mid(strArgs(0), 20, 1) <> ".") _
Or (Not IsDate(Mid(strArgs(0), 1, 10))) _
Or (Not IsDate(Mid(strArgs(0), 12, 8))) _
Or (Not IsNumeric(Mid(strArgs(0), 21, 6))) Then
Err.Raise vbObjectError + 1, , "Please enter the 26 character timestamp as follows:" & vbCrLf & vbCrLf & _
"YYYY-MM-DD-hh.mm.ss.mmmmmm" & vbCrLf & vbCrLf & _
"Eg. 2002-09-25-17.59.59.123456"
Else
strTimeStamp = strArgs(0)
End If
'Check and assign queue status
If Len(strArgs(1)) = 1 Then
Select Case strArgs(1)
'queue status has a db2 constraint and will only allow the following characters.
Case "C", "E", "H", "I", "P", "R", "X"
strQueueStatus = strArgs(1)
Case Else
Err.Raise vbObjectError + 1, , "The queuestatus must be one of ""C"", ""E"", ""H"", ""I"", ""P"", ""R"", ""X"""
End Select
Else
Err.Raise vbObjectError + 1, , "The queuestatus must not be longer than 1 character."
End If
'Ensure a '.jpg' image is passed in and assign it
If Right(strArgs(2), 4) <> ".jpg" Then
Err.Raise vbObjectError + 1, , "The path and filename parameter did not include a "".jpg"" file."
Else
strBLOBFile = strArgs(2)
End If
'Ensure the image id is numeric (even though it is handled as a string, internally) if it is, assign it.
If Not IsNumeric(Trim(strArgs(3))) Then
Err.Raise vbObjectError + 1, , "The image ID must be numeric"
Else
If Len(strArgs(3)) > 9 Then
Err.Raise vbObjectError + 1, , "The image ID must be smaller than 10 digits."
Else
'The stored procedure on DB2 requires the image number to be exactly
'nine characters wide, so if it shorter we'll pad it with zeroes.
strCustImageID = Left("000000000", 9 - Len(Trim(strArgs(3)))) & Trim(strArgs(3))
End If
End If
If UBound(strArgs) > 3 Then
If UCase(strArgs(4)) = "FORCE" Then
bolInterLock = False
Else 'Check for UserID, Password and DB2 subsystem

'Get the user id from the registry
strUserID = ""
If UBound(strArgs) > 3 Then
strUserID = strArgs(4)
End If
If strUserID = "" Then
strUserID = GetSetting("IMPOLITE", "Parameters", "DB2UID")
End If
If strUserID = "" Then
Err.Raise vbObjectError + 1, , "No user ID found in registry," & vbCrLf & _
" and none specified on the command line."
End If

'Get the password from the registry
strPassword = ""
If UBound(strArgs) > 4 Then
strPassword = strArgs(5)
End If
If strPassword = "" Then
strPassword = GetSetting("IMPOLITE", "Parameters", "DB2PWD")
End If
If strPassword = "" Then
Err.Raise vbObjectError + 1, , "No password found in registry," & vbCrLf & _
"and none specified on the command line."
End If

'Get the database subsystem we are connecting to from the registry
strDSN = ""
If UBound(strArgs) > 5 Then
strDSN = strArgs(6)
End If
If strDSN = "" Then
strDSN = GetSetting("IMPOLITE", "Parameters", "DB2DSN")
End If
If strDSN = "" Then
Err.Raise vbObjectError + 1, , "There was no entry in the registry," & vbCrLf & _
"indicating which DSN to connect to!" & vbCrLf & _
"and none specified on the command line."
End If
End If 'Force InterLocking
End If 'strArgs > 3

'See whether we want to update the registry with result status
If UBound(strArgs) > 6 Then
If UCase(strArgs(7)) = "FORCE" Then
bolInterLock = False
End If
End If

'Create the ADO Stream object
Set mstream = New ADODB.Stream

'Make it a binary type
mstream.Type = adTypeBinary

'Open the stream
mstream.Open

'Read the binary file into the stream buffer
mstream.LoadFromFile strBLOBFile
lngSize = mstream.Size
'ensure that the maximum BLOB size in DB2 is not exceeded,
'Just truncate the image to MAX_BLOBSIZE.

If lngSize > MAX_BLOBSIZE Then
'Err.Raise vbObjectError + 1, "Image " & strCustImageID & " could NOT be inserted!" & vbCrLf & _
' "It exceeded the maximum of 32768 bytes!"
lngSize = MAX_BLOBSIZE
End If

szConnect = "PROVIDER=IBMDADB2; DSN=" & strDSN & "; UID=" & strUserID & "; PWD=" & strPassword

'Here we create the command object
With commandADO
.Prepared = False
.ActiveConnection = szConnect
.CommandText = "MOVERS.MRCPIDCU"
.CommandType = adCmdStoredProc
'Create Parameter objects and append to Parameters
'Collection
Set prmADO = .CreateParameter("@DTIME", adChar, adParamInput, 26, strTimeStamp)
.Parameters.Append prmADO
Set prmADO = .CreateParameter("@CQUEUEST", adChar, adParamInput, 1, strQueueStatus)
.Parameters.Append prmADO
Set prmADO = .CreateParameter("@CUSTIMG", adChar, adParamInput, 9, strCustImageID)
.Parameters.Append prmADO
Set prmADO = .CreateParameter("@SQLCODE", adInteger, adParamOutput, 9, 0)
.Parameters.Append prmADO
Set prmADO = .CreateParameter("@CUSTIMGLO", adLongVarBinary, adParamInput, lngSize, LeftB(mstream.Read, MAX_BLOBSIZE))
.Parameters.Append prmADO
'Now execute the command
.Execute
End With

'Now, capture the Primary Key of the newly inserted row
szReturn = commandADO.Parameters("@SQLCODE")
If CInt(szReturn) <> 0 Then
Err.Raise vbObjectError + 1, , "Return Code = " & szReturn & vbCrLf & _
"Image " & strCustImageID & " could NOT be inserted!"
End If

Exit_Main:
If bolInterLock Then
While GetSetting("IMPOLITE", "Parameters", "InterLock", "1") <> "0"
DoEvents
Randomize
Sleep Int(250 * Rnd + 1)
Wend
SaveSetting "IMPOLITE", "Parameters", "InterLock", "1"
End If
SaveSetting "IMPOLITE", "Parameters", "Image", strCustImageID
SaveSetting "IMPOLITE", "Parameters", "ImageResult", strSuccess
Exit Sub

SPError:
'We are running silently on the UNICARD server and won't use message boxes,
'instead we log messages.
App.StartLogging App.Path & "\InsertBLOB.log", vbLogToFile
App.LogEvent Now & vbCrLf & Err.Description, vbLogEventTypeError
Err.Clear
strSuccess = "1"
GoTo Exit_Main
End Sub

your help is highly appreciated.

 

[deepsheep]

Without knowing where the error happens in the code or what the stored procdure is like, my guess is that it doesn't like your binary data. Maybe there is a character code in there causing problems. Slashes, quotes, carrage returns (and others) can all possibly cause problems. Maybe it's too long and it's getting truncated somewhere. Maybe you need to pass it as a different data type and see if it gets to the stored procedure. Maybe you need to try inserting all the data except the binary stuff and then do an update in a separate stored procedure to add to the record.

It could also be that it doesn't like .read function in prepareing the parameter.

You may also want to try a different approch. Save the path & file name of the image in the database and the acutal file elsewhere. (That's what I ended up doing, it wan't worth my time to find an elusive error to stick a image in the DB)

Hope this helps,
Tracy