StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Huge Strings in Oracle or Varchar2(4000)

Huge Strings in Oracle or Varchar2(4000)

LeRoy Jackson, Jr. — Tue, 19 Dec 2006 17:55:34 GMT

I am trying to bring a string down that is in a field that is Varchar2(4000).

In the BO Mappper, I think the only thing that the type can be is a string.

[codesnippet]

<Browsable(False), _

BusinessFieldDisplayInEditor(), _

Description("CSIGSTRING"), _

DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _

Public Property [CSIGSTRING]() As System.String

Get

Return CType(Me.CurrentRow.Item("CSIGSTRING"), System.String) ' Errors out here'

End Get

Set(ByVal value As System.String)

Me.CurrentRow.Item("CSIGSTRING") = value

End Set

End Property

[/codesnippet]

I know this is a huge string but it is the value of a signature.

What can I do to make this work

Thanks in advance

RE: Huge Strings in Oracle or Varchar2(4000)

Trent L. Taylor — Tue, 19 Dec 2006 17:55:34 GMT

I am glad you got going.

RE: Huge Strings in Oracle or Varchar2(4000)

LeRoy Jackson, Jr. — Tue, 19 Dec 2006 17:53:45 GMT

Ok There is more than one way to skin a cat :-)

It was my Sytax with Oracle So I did a String.Format instead.

This Worked:

[codesnippet]

Public Sub FillbyKeys(ByVal lcCompany As String, _

ByVal lcWarehouse As String, _

ByVal lcBol_no As String, ByVal lcSo_no As String)

Dim loCommand As New OracleCommand()

loCommand.CommandText = String.Format("Select * from RFI.BOL_SIGNATURE " _

& "where company = '{0}' " _

& "and warehouse = '{1}' " _

& "and bol_no = {2} " _ ' Notice the Quotes taken away

& "and so_no = {3} " _ ' Notice the Quotes taken away

, lcCompany, lcWarehouse, lcBol_no, lcSo_no)

Me.FillDataTable(loCommand)

End Sub

[/codesnippet]

Thanks for pointing me in the right direction.

RE: Huge Strings in Oracle or Varchar2(4000)

Trent L. Taylor — Tue, 19 Dec 2006 17:34:59 GMT

I would have to setup an Oracle test, but at first glance, it looks like your CommandText is wrong. You have the variables surrounding the parameters. This will more than likely produce an error....I know that it will in SQL Server and every other ANSI 92 query language.

You Have:
" and so_no = '@so_no' "

Should Be:
" and so_no = @so_no"

This will be true for every other parameter as well. The exception given to you is a valid error, Oracle does not know what to do with the parameters because the command text is wrong.

RE: Huge Strings in Oracle or Varchar2(4000)

LeRoy Jackson, Jr. — Tue, 19 Dec 2006 17:28:42 GMT

I just Have a button that on the click Does This:

[codesnippet]

Me.BoL_Signature1.FillbyKeys(Me.txtCompany.Text, _

Me.txtWarehouse.Text, _

Me.txtBolNo.Text, _

Me.txtSoNo.Text)

[/codesnippet]

I changed my FillbyKeys Method from

[codesnippet]

Public Sub FillbyKeys(ByVal lcCompany As String, _

ByVal lcWarehouse As String, _

ByVal lcBol_no As String, ByVal lcSo_no As String)

Dim loCommand As New OracleCommand()

loCommand.CommandText = String.Format("Select * from RFI.BOL_SIGNATURE " _

& "where company = '{0}' " _

& "and warehouse = '{1}' " _

& "and bol_no = '{2}' " _

& "and so_no = '{3}'" _

, lcCompany, lcWarehouse, lcWarehouse, lcBol_no, lcSo_no)

Me.FillDataTable(loCommand)

End Sub

[/codesnippet]

to look like this

[codesnippet]

Public Sub FillbyKeys(ByVal lcCompany As String, _

ByVal lcWarehouse As String, _

ByVal lcBol_no As String, ByVal lcSo_no As String)

Dim loCommand As New OracleCommand()

loCommand.CommandText = "Select * from RFI.BOL_SIGNATURE " _

& "where company = '@Company' " _

& " and warehouse = '@warehouse' " _

& " and bol_no = '@bol_no' " _

& " and so_no = '@so_no' "

loCommand.Parameters.Add("@Company", OracleType.VarChar)

loCommand.Parameters("@Company").Value = lcCompany

loCommand.Parameters.Add("@warehouse", OracleType.VarChar)

loCommand.Parameters("@warehouse").Value = lcWarehouse

loCommand.Parameters.Add("@bol_no", OracleType.VarChar)

loCommand.Parameters("@bol_no").Value = lcBol_no

loCommand.Parameters.Add("@so_no", OracleType.VarChar)

loCommand.Parameters("@so_no").Value = lcSo_no

Me.FillDataTable(loCommand)

End Sub

[/codesnippet]

Now it gives me this Error on the Me.FillDataTable(loCommand) :

[codesnippet]

System.Data.OracleClient.OracleException was unhandled by user code
ErrorCode=-2146232008
Message="ORA-01036: illegal variable name/number
"
Source="System.Data.OracleClient"
StackTrace:
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
       at MicroFour.StrataFrame.Data.DataLayer.GetDataTable(DbCommand Command, Boolean RegisterNotification)
       at MicroFour.StrataFrame.Business.BusinessLayer.FillDataTable(DbCommand CommandToExecute)
       at BOL_BO.BOL_Signature.FillbyKeys(String lcCompany, String lcWarehouse, String lcBol_no, String lcSo_no) in C:\VsNetProjects\StrataFrameProjects\BOL_NET\BOL_BO\BOL_BO\BOL_Signature.vb:line 94
       at BOL_Win.Bol_sigTest.Button1_Click(Object sender, EventArgs e) in C:\VsNetProjects\StrataFrameProjects\BOL_NET\BOL_Win\BOL_Win\Bol_sigTest.vb:line 4
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

[/codesnippet]

But all of the Types are Strings or Varchar2

So what type could it be looking for?

RE: Huge Strings in Oracle or Varchar2(4000)

Trent L. Taylor — Tue, 19 Dec 2006 17:03:06 GMT

This does not have anything to do with the data type or the back-end. What are you trying to do prior to getting the error? Are you trying to set the value of the field. It looks like you are trying to update the field without having a record loaded. You either need to add a new row or load an existing row before you can set the property.

RE: Huge Strings in Oracle or Varchar2(4000)

LeRoy Jackson, Jr. — Tue, 19 Dec 2006 17:00:49 GMT

Here is the execption

[codesnippet]

MicroFour.StrataFrame.Business.BusinessLayerException was unhandled by user code
Message="The CurrentRow could not be evaluated because the CurrentRowIndex is out of range. Business object record count: 0. CurrentRowIndex: -1."
Source="MicroFour StrataFrame Business"
StackTrace:
       at MicroFour.StrataFrame.Business.BusinessLayer.get_CurrentRow()
       at BOL_BO.BOL_Signature.get_CSIGSTRING() in C:\VsNetProjects\StrataFrameProjects\BOL_NET\BOL_BO\BOL_BO\BOL_Signature.Designer.vb:line 257
       at BOL_Win.Bol_sigTest.Button1_Click(Object sender, EventArgs e) in C:\VsNetProjects\StrataFrameProjects\BOL_NET\BOL_Win\BOL_Win\Bol_sigTest.vb:line 20
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

[/codesnippet]

RE: Huge Strings in Oracle or Varchar2(4000)

Trent L. Taylor — Tue, 19 Dec 2006 16:57:24 GMT

What is the error that you are getting? Could you please provide the exception (an any inner exceptions) that occur? Thanks.