LLBLGEN V 3.0 .Net Framework 2.0 Oracle 9i / 10g

Hi, I have a table say for example

ID NAME

Here ID has a sequence and NAME is user entered. In designer I have defined the Oracle sequence in designer also. Everything works fine.

But now I have got a new requirement. I want to add a prefix to ID while saving the entity.

Example: Suppose say sequence value is 1, I would like to save as AF-1.

Is there a way that I can achieve this. ?

ID is numeric as it's a sequenced field, you're not allowed to save a string value in a numeric field. How else do you want to save the 'AF-' part...

Otis wrote:

ID is numeric as it's a sequenced field, you're not allowed to save a string value in a numeric field. How else do you want to save the 'AF-' part...

No. My datatype is Varchar(2). I want to read the sequence and then prefix with AF and then save it

shekar wrote:

Otis wrote:

ID is numeric as it's a sequenced field, you're not allowed to save a string value in a numeric field. How else do you want to save the 'AF-' part...

No. My datatype is Varchar(2). I want to read the sequence and then prefix with AF and then save it

So the value is CONCAT('AF-', (SELECT SEQ_foo.NEXTVAL FROM DUAL)) ?

That's not supported currently. The only way to achieve that is to write a stored procedure which obtains the next value of a sequence, then create the AF prefix string with it and set the ID property of the entity object to that value and save the entity.

Not sure why you want to prefix a sequence though. the sequence is unique enough so why the extra prefix?

Otis wrote:

shekar wrote:

Otis wrote:

ID is numeric as it's a sequenced field, you're not allowed to save a string value in a numeric field. How else do you want to save the 'AF-' part...

No. My datatype is Varchar(2). I want to read the sequence and then prefix with AF and then save it

So the value is CONCAT('AF-', (SELECT SEQ_foo.NEXTVAL FROM DUAL)) ?

That's not supported currently. The only way to achieve that is to write a stored procedure which obtains the next value of a sequence, then create the AF prefix string with it and set the ID property of the entity object to that value and save the entity.

Not sure why you want to prefix a sequence though. the sequence is unique enough so why the extra prefix?

Got it. But alternate would be to read the sequence first (without specifying identity in designer). after reading it prefix it and save it. Though I know I can use DB call to read sequence directly, I do now know the exact code to read sequence directly. So if you could please tell me how to read sequence directly, it would be of immense help to me

Like I said, with a stored procedure which gets the next value from the sequence and returns it in a parameter. Then you map a call to that stored procedure in the llblgen pro designer and in your code you call the method generated to call the stored procedure at runtime to obtain the new sequence value.

Otis wrote:

Like I said, with a stored procedure which gets the next value from the sequence and returns it in a parameter. Then you map a call to that stored procedure in the llblgen pro designer and in your code you call the method generated to call the stored procedure at runtime to obtain the new sequence value.

Ok Thanks

[quotenick="shekar"]

Otis wrote:

Like I said, with a stored procedure which gets the next value from the sequence and returns it in a parameter. Then you map a call to that stored procedure in the llblgen pro designer and in your code you call the method generated to call the stored procedure at runtime to obtain the new sequence value.

Hi

I create a function in oracle for this purpose as follows.

function getNextID return varchar2 is retVal varchar2(15); begin select 'AF-' || SEQ_AFFILIATECLUB.nextval into retVal from dual; return retVal; end;

on my form save click, i wrote : string AffiliateClubNo = null; DataTable resultSet = RetrievalProcedures.Getnextid(ref AffiliateClubNo);

Error in LLBLGEN Generated Code: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

This is the line of LLBLGEN generated code which is giving me error.

returnAffiliateClubNo = call.GetParameterValue<System.String>(-1);

Please help

The SP SQL code shows that it doesn't take any parameters, correct? How come in code it receives a parameter (AffiliateClubNo)?

Walaa wrote:

The SP SQL code shows that it doesn't take any parameters, correct? How come in code it receives a parameter (AffiliateClubNo)?

Exactly. It does not take any parameter only output is returned. request your help what is the correction to be done in the line i wrote.

Please post the stack trace of the exception.

Walaa wrote:

Please post the stack trace of the exception.

Here it is Sir

System.ArgumentOutOfRangeException was unhandled
  Message="Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"
  Source="mscorlib"
  ParamName="index"
  StackTrace:
       at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
       at System.ThrowHelper.ThrowArgumentOutOfRangeException()
       at System.Collections.Generic.List`1.get_Item(Int32 index)
       at SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.GetParameterValue[TValue](Int32 parameterIndex) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\StoredProcedureCall.cs:line 177
       at ClubCentricHelper.DatabaseSpecific.RetrievalProcedures.Getnextid(String& returnAffiliateClubNo, IDataAccessCore dataAccessProvider) in C:\ProjectClubCentric\LLBLGen Pro Projects\DatabaseSpecific\RetrievalProcedures.cs:line 42
       at ClubCentricHelper.DatabaseSpecific.RetrievalProcedures.Getnextid(String& returnAffiliateClubNo) in C:\ProjectClubCentric\LLBLGen Pro Projects\DatabaseSpecific\RetrievalProcedures.cs:line 29
       at ClubCentric.Master.MasterAffiliateClub.btnsave_Click(Object sender, EventArgs e) in C:\ProjectClubCentric\ClubCentric\Master\MasterAffiliateClub.cs:line 31
       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.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager. FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at ClubCentric.Program.Main() in C:\ProjectClubCentric\ClubCentric\Program.cs:line 16
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

The procedure doesn't return a resultset, so what did you do exactly to make this a retrieval procedure? it should have been an action procedure. If you changed the # of resultsets of the proc to 1, please change it back to 0. Generate code and the mapped SP Call should be in the ActionProcedures.

I'll try to reproduce your proc.

Otis wrote:

The procedure doesn't return a resultset, so what did you do exactly to make this a retrieval procedure? it should have been an action procedure. If you changed the # of resultsets of the proc to 1, please change it back to 0. Generate code and the mapped SP Call should be in the ActionProcedures.

I'll try to reproduce your proc.

Ok I changed back to 0.

But I dont understand how do i rewrite this line now

DataTable resultSet = ActionProcedures.Getnextid(............);

Tried my best but no luck

Bug in runtime, or oracle driver, we're looking into the root cause. The main issue is that a return value from a function gets as index -1, while on other databases it's 0, hence the problem.

Otis wrote:

Bug in runtime, or oracle driver, we're looking into the root cause. The main issue is that a return value from a function gets as index -1, while on other databases it's 0, hence the problem.

You are right. That is the cause for problem. Will await your reply.

It turned out to be a small issue in the code generator. The statement which emitted the parameter index assumed the ordinal was always 1-based and should always have 1 substracted, which is wrong, it should do that only when there's no return parameter present.

I've attached the new build of the TDL interpreter dll. Copy this one over the existing one in the task performers folder, and regenerate your code.

Otis wrote:

It turned out to be a small issue in the code generator. The statement which emitted the parameter index assumed the ordinal was always 1-based and should always have 1 substracted, which is wrong, it should do that only when there's no return parameter present.

I've attached the new build of the TDL interpreter dll. Copy this one over the existing one in the task performers folder, and regenerate your code.

Great ! root problem is solved. Everything is ok now