I am using LLBLGen 2.6 and Sybase 11 and I am trying to call an Action Stored Procedure. When I call the action stored procedure, I get an error back from the SqlAnywhere .NET provider that simply says "Communication Error". I know that I can communicate with Sybase because all of the other database stuff works right up to that and continues after (I have it in a try/catch) but that call to the Action Stored Procedure fails. I am passing an Adapter instance into the Action Stored Procedure call for it to use. I tried taking that out and putting the connection string in my app.config and it does the same thing.

Is there something that I have missed with the Action Stored Procedures that might be causing this?

Thanks,

Allen

Hi Allen,

Please post the exact error message and stack trace of the exception, also the runtime library version (http://llblgen.com/TinyForum/Threads.aspx?ForumID=9).

How often does this happen?

Here is the stack trace of the error that I am receiving:

   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery()
   at PayclockDAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, SAParameter[] parameters) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\DataAccessAdapter.cs:line 171
   at PayclockDAL.DatabaseSpecific.ActionProcedures.AddRegistration(Int32 companyId, Int32 employeeId, DateTime punchDateTime, Int32 terminalId, Int32 departmentId, Int64 punchTimeOfDay, Int32& punchTypeId, Int32& resultCode, DataAccessAdapter adapter) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\ActionProcedures.cs:line 81
   at Lathem.USBTM.Service.BusinessLogic.DBManager.AddPunch(RegistrationsEntity reg) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.USBTM\Lathem.USBTM.Service.BusinessLogic\DBManager.cs:line 667

Here is the message:

Communication error

The inner exception is null.

I listed that I am using version 2.6 of LLBLGen - when I click on the runtime file in Visual Studio, it says this under runtime version:

v2.0.50727

If I go into Windows Explorer and look at the File Version, it says: 2.6.09.0616

I am using Sybase version 11.0.1.22222.

As I mentioned in my previous post, I am calling the stored procedure and passing in a DataAccessAdapter that has been initialized (and seems to work fine with all other operations - fetching entities, etc.) Any stored procedure calls are failing with the "Communication Error" listed above.

Thanks,

Allen

Can you successfully call the sybase stored procedures using standard ADO.NET code (no LLBL generated code in there) ?

Does sybase have the equivalent of SQL Profiler - can you actually watch or trace the call to the stored procedure ?

Matt

I wrote a small test app to make sure that I could call the stored procedures using this code:

static DateTime GetServerTime()
        {
            SACommand cmd = new SACommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "[DBA].[GetServerTime]";
            SAParameter p1 = new SAParameter();
            p1.SADbType = SADbType.DateTime;
            p1.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(p1);

            SAConnection conn = new SAConnection("UserID=dba;Password=sql;ENG=PayclockDB;LINKS=SHMEM;PROWS=200");
            cmd.Connection = conn;
            conn.Open();

            cmd.ExecuteNonQuery();
            if (cmd.Parameters[0].Value != System.DBNull.Value)
            {
                return (System.DateTime)cmd.Parameters[0].Value;
            }
            else
            {
                return new DateTime();
            }
        }

When I run this, it returns the correct server time based on the stored procedure call. When I call this same stored procedure through LLBLGen, it fails with the communication error message. I am using the exact same ConnectionString property for my DataAccessAdapter initialization as I am using in the code above.

I tried to see if Sybase could see the connection attempt by LLBLGen and either I am not using it correctly, or it never connects.

Allen

Mmm.

The error is happening at iAnywhere assembly. Please attach your .config file.

In my test app, I just have this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="iAnywhere.Data.SQLAnywhere" publicKeyToken="F222FC4333E0D400" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-11.0.1.22222" newVersion="11.0.1.22222"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

In my main app, I have a bit more:

<?xml version="1.0"?>
<configuration>
  <system.windows.forms jitDebugging="true"/>
    <system.serviceModel>
        <bindings>
            <netNamedPipeBinding>
                <binding name="NetNamedPipeBinding_IUnifiedTerminalService" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" transactionFlow="false" transferMode="Buffered" transactionProtocol="OleTransactions" hostNameComparisonMode="StrongWildcard" maxBufferPoolSize="524288" maxBufferSize="65536" maxConnections="10" maxReceivedMessageSize="65536">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>
                    <security mode="Transport">
                        <transport protectionLevel="EncryptAndSign"/>
                    </security>
                </binding>
            </netNamedPipeBinding>
        </bindings>
        <client>
            <endpoint address="net.pipe://localhost/UnifiedTerminalService" binding="netNamedPipeBinding" bindingConfiguration="NetNamedPipeBinding_IUnifiedTerminalService" contract="UnifiedTerminalService.IUnifiedTerminalService" name="NetNamedPipeBinding_IUnifiedTerminalService">
                <identity>
                    <userPrincipalName value="host/localhost"/>
                </identity>
            </endpoint>
        </client>
    </system.serviceModel>
    <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly>
          <assemblyIdentity name="DPFPShrNET" publicKeyToken="D902FE275A8D6CF2" culture="neutral"/>
          <bindingRedirect oldVersion="0.0.0.0-1.3.0.1" newVersion="1.3.0.1"/>
        </dependentAssembly>
        <dependentAssembly>
          <assemblyIdentity name="iAnywhere.Data.SQLAnywhere" publicKeyToken="F222FC4333E0D400" culture="neutral"/>
          <bindingRedirect oldVersion="0.0.0.0-11.0.1.22222" newVersion="11.0.1.22222"/>
        </dependentAssembly>
      </assemblyBinding>
    </runtime>

  <system.diagnostics>
    <sources>
      <!-- This section defines the logging configuration for My.Application.Log -->
      <source name="DefaultSource" switchName="DefaultSwitch">
        <listeners>
          <add name="FileLog"/>
          <!-- Uncomment the below section to write to the Application Event Log -->
          <add name="EventLog"/>
        </listeners>
      </source>
    </sources>
    <switches>
      <add name="DefaultSwitch" value="Information"/>
      <add name="SybaseAsaDQE" value="0" /> <!--use 4 for verbose--> 
    </switches>
    <sharedListeners>
      <add name="FileLog" type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" initializeData="FileLogWriter"/>
      <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
      <add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="Payclock V6"/>
    </sharedListeners>
  </system.diagnostics>
</configuration>

As I mentioned before, all of the LLBLGen stuff is working as it should 'except' for the stored procedure calls. They return with "communication error". I am 100% confident I can connect to the database, but something about the connecting during a stored procedure call is failing.

Thanks,

Allen

I don't see any connection string setting in your app config file.

Are you running the application against the same database instance based on which the code was generated?

I am running the application against the same database. In addition, I am initializing my DataAccessAdapter using this code:

DataAccessAdapter adapter;

        public DBManager() 
        {
            adapter = new DataAccessAdapter(SybaseDB.GetDBConn(),true);
        }

The above function calls this code to get the DB connection String:

private static string dbConnect = "UserID=dba;Password=sql;DBN=PayclockDB;ENG=P@yclock;LINKS=SHMEM,TCPIP(HOST=192.168.100.121);PROWS=200";
              public static string GetDBConn()
        {
            return dbConnect;
        }

I am also passing in the DataAccessAdapter to the ActionProcedure call like this:

ActionProcedures.AddRegistration(reg.CompanyId,reg.EmployeeId, reg.PunchDateTime, reg.TerminalId, reg.DepartmentId, reg.PunchTimeOfDay, ref punchTypeID, ref resultCode, adapter);

Again, everything else is working except calling a stored procedure.

Allen

Would you please troubleshoot this, by examining the generated query? Please check the docs section: Using the generated code -> Troubleshooting and debugging

I set the switch to "4" and ran it again and get this in the Output window -

'DBTester.vshost.exe' (Managed): Loaded 'C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\DBTester\bin\Debug\SD.LLBLGen.Pro.DQE.SybaseAsa.NET20.dll'
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [DBA].[Employee].[CompanyID] AS [CompanyId], [DBA].[Employee].[EmployeeID] AS [EmployeeId], [DBA].[Employee].[EmployeeFirstName], [DBA].[Employee].[EmployeeMiddleInitial], [DBA].[Employee].[EmployeeLastName], [DBA].[Employee].[DepartmentID] AS [DepartmentId], [DBA].[Employee].[BadgeNumber], [DBA].[Employee].[EmployeeNumber], [DBA].[Employee].[NameAtTerminal], [DBA].[Employee].[BasePay], [DBA].[Employee].[UseSalariedHours], [DBA].[Employee].[SalariedHours], [DBA].[Employee].[DateOfHire], [DBA].[Employee].[InactiveFlag], [DBA].[Employee].[DateOfInactive], [DBA].[Employee].[TerminatedFlag], [DBA].[Employee].[DateOfTerminated], [DBA].[Employee].[EmployeeTypeID] AS [EmployeeTypeId], [DBA].[Employee].[NoExport], [DBA].[Employee].[EmployeePassword] AS [Password], [DBA].[Employee].[HolidayCalendarID] AS [HolidayCalendarId], [DBA].[Employee].[DelayHolidayRulesDays], [DBA].[Employee].[PayclassID] AS [PayclassId], [DBA].[Employee].[JobClassID] AS [JobClassId], [DBA].[Employee].[SupervisorFlag], [DBA].[Employee].[Address1], [DBA].[Employee].[Address2], [DBA].[Employee].[City], [DBA].[Employee].[State], [DBA].[Employee].[Zip], [DBA].[Employee].[Country], [DBA].[Employee].[HomePhone], [DBA].[Employee].[SocialSecurityNumber], [DBA].[Employee].[DateOfBirth], [DBA].[Employee].[FirstContact], [DBA].[Employee].[SecondContact], [DBA].[Employee].[FirstDayPhone], [DBA].[Employee].[SecondDayPhone], [DBA].[Employee].[FirstRelationshipID] AS [FirstRelationshipId], [DBA].[Employee].[SecondRelationshipID] AS [SecondRelationshipId], [DBA].[Employee].[IgnoreHolidaySchedule], [DBA].[Employee].[IgnorePremiumTimeRules], [DBA].[Employee].[EmployeeRulesID] AS [EmployeeRulesId], [DBA].[Employee].[EnvironmentId], [DBA].[Employee].[overrideEnvironment] AS [OverrideEnvironment], [DBA].[Employee].[CreateDateTime], [DBA].[Employee].[LastUpdateDateTime], [DBA].[Employee].[OverrideMaxHoursOutRule], [DBA].[Employee].[OverrideMaxWorkDay], [DBA].[Employee].[MaxHoursToNewShift], [DBA].[Employee].[MaxWorkDayLength], [DBA].[Employee].[Picture] FROM [DBA].[Employee]

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
'DBTester.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'iAnywhere.Data.SQLAnywhere.SAException' occurred in iAnywhere.Data.SQLAnywhere.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>DBTester.vshost.exe</AppDomain><Exception><ExceptionType>iAnywhere.Data.SQLAnywhere.SAException, iAnywhere.Data.SQLAnywhere, Version=11.0.1.22222, Culture=neutral, PublicKeyToken=f222fc4333e0d400</ExceptionType><Message>Communication error</Message><StackTrace>   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery()
   at PayclockDAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, SAParameter[] parameters) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\DataAccessAdapter.cs:line 171
   at PayclockDAL.DatabaseSpecific.ActionProcedures.GetServerTime(DateTime&amp;amp; currentDateTime, DataAccessAdapter adapter) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\ActionProcedures.cs:line 126
   at Lathem.USBTM.Service.BusinessLogic.DBManager.GetServerTime(BaseTimeSetting baseTimeSetting) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.USBTM\Lathem.USBTM.Service.BusinessLogic\DBManager.cs:line 526
   at DBTester.Program.Main(String[] args) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\DBTester\Program.cs:line 44
   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()</StackTrace><ExceptionString>iAnywhere.Data.SQLAnywhere.SAException: Communication error
   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery()
   at PayclockDAL.DatabaseSpecific.DataAccessAdapter.CallActionStoredProcedure(String storedProcedureToCall, SAParameter[] parameters) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\DataAccessAdapter.cs:line 171
   at PayclockDAL.DatabaseSpecific.ActionProcedures.GetServerTime(DateTime&amp;amp; currentDateTime, DataAccessAdapter adapter) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.Payclock.DAL\DatabaseSpecific\ActionProcedures.cs:line 126
   at Lathem.USBTM.Service.BusinessLogic.DBManager.GetServerTime(BaseTimeSetting baseTimeSetting) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\Lathem.USBTM\Lathem.USBTM.Service.BusinessLogic\DBManager.cs:line 526
   at DBTester.Program.Main(String[] args) in C:\Projects\Team\Lathem\Trunk\Source\Payclock.Net\DBTester\Program.cs:line 44
   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()</ExceptionString></Exception></TraceRecord>

I added a call to fetch an entity which works fine (you see the sql), and then the next line of code attempts to execute the stored procedure which causes the failure with "Communication Error".

I have asked Sybase for assistance, but because it works outside of LLBLGen like it is supposed to, they cannot help (or won't).

I am really stuck. We have invested a huge amount of time in LLBLGen and if I can't get these stored procedures working, we will have to abandon LLBLGen.

What testing have you done with LLBLGen and version 11 of Sybase? I know that I have to use the assembly redirects to use version 11, but it works fine for all operations except for calling stored procedures.

Help!

Allen

Hi Allen,

Weird indeed. Will test this on my box...

(Edit) Can't reproduce that issue here.

LLBLGen RTL 2.6.09.0616 (the latest)

Sybase ASA version 11.0.1

iAnywhere Data Provider for .Net 11.0.1.20442 (the latest)

Stored Procedures The stored procedures on the demo.db

It's unfortunate you run into this, but I can't reproduce. Is it possible that you provide us more info so we can reproduce it, or prepare for us a repro solution that shows the issue?

please contact Sybase with this, it's an error thrown by their provider. As that provider has proven to be a, let's put it nicely, not so great piece of software in the past, it's not unlikely the error is in their court, especially since it throws a 'communication error', which IMHO can be anything, from the server not being reachable, to a bug in the provider. Our code just uses ADO.NET commands, which can't result in communication errors.

Unfortunately, I am stuck in the middle of finger-pointing at this time and it is really frustrating. Sybase says that they can't help me make LLBLGen work and you say it is in their provider. Since I can call stored procedures fine outside of LLBLGen, they say the provider is fine. You say it isn't.

The sad news is that I am supposed to get up in front of a group of Sybase users/customers at the end of August at the Sybase Techwave Symposioum and talk about our OEM development experience with Sybase and LLBLGen and I can't make it work.

By the way, the most recent version of Sybase 11 and the provider is 11.0.1.22222, not 11.0.1.20442 .

Allen

asowles wrote:

Unfortunately, I am stuck in the middle of finger-pointing at this time and it is really frustrating. Sybase says that they can't help me make LLBLGen work and you say it is in their provider. Since I can call stored procedures fine outside of LLBLGen, they say the provider is fine. You say it isn't.

I hate fingerpointing but we can't reproduce it, and they have dragged their feet about bugs in their code before. The stored proc call code is all in the generated code. You can follow it in the debugger if you want to, there's nothing magical about it: create connection, create command, add parameters, set command text, execute it.

It would be great if you could check in the debugger if you see something odd happening during the code path of the proc execution in our generated code: is the connection string set to a proper value? Is the proc name specified wrong?

I feel your pain, but I have no idea what to change to make it work as I have no idea what "Communication error" means other than that there's something going wrong apparently with connection / sending data. The connection string you use with the adapter is different btw: "UserID=dba;Password=sql;DBN=PayclockDB;ENG=P@yclock;LINKS=SHMEM,TCPIP(HOST=192.168.100.121);PROWS=200"

vs. the one in your testapp: "UserID=dba;Password=sql;ENG=PayclockDB;LINKS=SHMEM;PROWS=200"

you don't specify the TCPIP(HOST... ) part and DBN and ENG are different. Not all parameters in the connection string ring a bell to me, but they can perhaps influence how things are transfered back and forth. Your stacktrace shows that the exception comes from a call to ExecuteNonQuery. The generated code by then already opened the connection. This means that the connection string itself isn't the problem: the execution of the proc is. (SACommand.ExecuteNonQuery directly calls into an unmanaged dll, in where the problem occurs, (hence the stacktrace missing), so it's impossible to track down what it causes without sybase looking into it or by putting in some serious trial/error time)

What's weird is that there's no inner exception so Sybase's code doesn't show where exactly the exception originates (in their code, but where is a mistery, likely in their java stuff)

You can see this yourself when you step through the code: the proc call code is all in generated code so it should be easy to step through and see what we do differently.

The sad news is that I am supposed to get up in front of a group of Sybase users/customers at the end of August at the Sybase Techwave Symposioum and talk about our OEM development experience with Sybase and LLBLGen and I can't make it work.

Well, if I knew what to change to make it work, I definitely would do it right this minute so you could continue with your work, but as we can't reproduce it and the error is very weird (and smells like a connection problem of some sort), what do you want us to investigate further? Please do understand that Sybase is one of the companies which really likes to avoid doing any work / help us in any way, in all the time we've worked with them. It's not the first time they have bugs in their code which were first denied and later on suddenly fixed.

By the way, the most recent version of Sybase 11 and the provider is 11.0.1.22222, not 11.0.1.20442 .

Our code is very straight forward, so I don't see how it relies on a given provider build.

So what I'd like you to investigate: - try the same connection string, you now didn't use the same connection string. - try other procs, do all procs fail? (yes they do, as you said in a previous message) - Try to rewrite your test app with code which mimics the call of your proc, so with the same parameters, types. Just copy over the code from the generated code, everything is there except the connection creation and name overwriting, you can skip that (and create the connection manually). It might also be that some parameter value isn't passable or something. I have the suspicion that it might be something related to the parameters and the types of the parameters, e.g. data isn't transferable to the service due to some lame reason. Hence my request to modify the test app you wrote to call a proc to make it call the proc which fails with the same code. - It might be your proc is in a server which is linked and now not reachable from the service you're connecting to?

Frans,

I've detailed my experience thus far with trying to get this to work, how I created the test app to make sure that I didn't have something wrong in my main app (neither work), I've tried different connection strings, detailed how I can fetch entities fine, it's only the stored procedures that fail so I know the basic "connection" to the database is fine.

The reason that the connection strings are slightly different is that I have tried to connect to a different database instance to see if the problem followed and it does (one was on a remote server and the other was running right on my machine to eliminate network issues). I have tried it with not passing in the adapter to the sp call (and putting the connection string in my app.config). That didn't help. I have tried dropping and re-creating the stored proc. I've tried different variations of the connection string parameters to try to get around this problem and none had any effect. All stored procedure fail with this same error if I call them from LLBLGen, but work fine if I call them from my app using straight ADO.NET code.

I have tried an explicit open of the connection (instead of and in addition to the base.open in DataAccessAdapter).

All of these efforts fail and I don't know why. Unfortunately, I don't have more time to invest in this problem and we will have to move on.

Sorry if I sound a bit annoyed, but first you want us to solve the problem and now you don't have time anymore to get this solved? I understand that things get cumbersome and time consuming when trying to find a solution to a problem, and with respect to sybase you're not alone in that (not by far I can tell you, we too have spend countless hours trying to find some solution to what didn't appear to be caused by our code at all).

asowles wrote:

Frans, I've detailed my experience thus far with trying to get this to work, how I created the test app to make sure that I didn't have something wrong in my main app (neither work), I've tried different connection strings, detailed how I can fetch entities fine, it's only the stored procedures that fail so I know the basic "connection" to the database is fine.

The reason that the connection strings are slightly different is that I have tried to connect to a different database instance to see if the problem followed and it does (one was on a remote server and the other was running right on my machine to eliminate network issues). I have tried it with not passing in the adapter to the sp call (and putting the connection string in my app.config). That didn't help. I have tried dropping and re-creating the stored proc. I've tried different variations of the connection string parameters to try to get around this problem and none had any effect. All stored procedure fail with this same error if I call them from LLBLGen, but work fine if I call them from my app using straight ADO.NET code.

That's not what you said nor showed, Allen. You posted an example app with a simple call to a simple proc. You didn't post a testapp which calls AddRegistration for example with the same parameters, same values and same parameter setup. That's what I would like you test, for the reason I gave above: apparently some communication with the service with respect to passing data (my guess, Sybase calls directly into their unmanaged dll so it's anyone's guess what's it doing). If you can copy/paste the code from our generated code into a test app and it also fails, you have a repro case for sybase to look at (as it doesn't rely on llblgen code anymore).

I have tried an explicit open of the connection (instead of and in addition to the base.open in DataAccessAdapter). All of these efforts fail and I don't know why. Unfortunately, I don't have more time to invest in this problem and we will have to move on.

If we can quit spending time on this, please say so and I'll close the thread. However I don't see how that will help you, as in the end you likely will run into the same problem again. E.g.: it might also be the assembly redirect you're having in the config file (which should be unnecessary, sybase installs a policy file) or that you referenced the .net 1.x assembly perhaps, or have an outdated client dll on your system (as the sybase provider calls directly into that dll for the work it needs to do).

saving an entity calls also Command.ExecuteNonQuery(), which is different from fetching an entity which opens a datareader. If you can't save an entity either, it's something to narrow the problem on.

I have spent over a two weeks trying to solve this problem so this hasn't been a trivial issue for us. I have tried every combination that I can think of. I am using the assembly redirect for 11 based on this direction from LLBLGEN Support:

Location: Egypt Joined on: 21-Aug-2005 16:03:48 Posted: 8115 posts # Posted on: 19-Mar-2009 06:15:09. Quote
That's normal coz the application is looking for iAnywhere.Data.SQLAnywhere, Version=10.0.1.34152, and it can't find it.

You should add an Assembly Redirect tag in the LLBLGenPro.exe.config file. To redirect references from any older version of the iAnywhere.Data.SQLAnywhere to the specific version that is installed.

I had to do this because the Designer would crash on startup because the version 10 assemblies were not there and it was looking for them specifically.

I posted the code previously for the call to AddRegistration AND the call in my test app. I greatly simplified the test app code to show that it wasn't a parameter issue (at least I dont' think so) and BOTH fail. Perhaps that wasn't clear. Both apps fail so it isn't that particular SP or that collection of parameters or that one app works and one app doesn't. Calls to ExecuteNONQuery simply fail regardless of the SP called or the parameter collection if called from within the LLBLGEN generated code. The same SP works fine if I call it with ADO.NET code in my app.

I can save, retrieve and update entities just fine. I just can't call SP's.

I even went as far as building a new development machine from scratch to eliminate dll versioning issues or anything being out of synch.

I'm sorry you are annoyed, but I have definitely put in the effort to try to make this work. Sybase won't help because it is a third party tool and once you say "calling an SP from ADO.NET code works fine" they are done. My biggest fear with going with a 3rd party tool for this stuff was this exact problem where we would hit a roadblock with no solution. We are early enough in our development that we can take LLBLGen out of the mix and go with a different solution. I hate to lose the work that we have done, but we are now jeopardizing our schedule and don't have a choice.

asowles wrote:

I have spent over a two weeks trying to solve this problem so this hasn't been a trivial issue for us. I have tried every combination that I can think of. I am using the assembly redirect for 11 based on this direction from LLBLGEN Support:

Location: Egypt Joined on: 21-Aug-2005 16:03:48 Posted: 8115 posts # Posted on: 19-Mar-2009 06:15:09. Quote
That's normal coz the application is looking for iAnywhere.Data.SQLAnywhere, Version=10.0.1.34152, and it can't find it.

You should add an Assembly Redirect tag in the LLBLGenPro.exe.config file. To redirect references from any older version of the iAnywhere.Data.SQLAnywhere to the specific version that is installed.

I had to do this because the Designer would crash on startup because the version 10 assemblies were not there and it was looking for them specifically.

Hmm, are you using a patch build from Sybase perhaps? Their installers install a policy file for the providers, at least they did do that in the past.

I'm sorry to hear you spend over two weeks on this, though please keep in mind that you reported this 8 days ago and we can't see your code in front of us nor see what you already did to make this work. Also, if it takes so long to get things solved, it might have been a good idea to step forward earlier and provide more information earlier. We don't see your code nor have your database at hand, so all we can work with is what you provide.

I posted the code previously for the call to AddRegistration AND the call in my test app. I greatly simplified the test app code to show that it wasn't a parameter issue (at least I dont' think so) and BOTH fail. Perhaps that wasn't clear.

No that wasn't clear indeed. I see one code snippet which calls GetServerTime and another set of small snippets which call the llblgen pro code. I then only conclude that you tried a simple app to see if it works at all (the GetServerTime) and you say it does, and for the rest I see only llblgen pro calling code, so I don't see a proc call snippet build from the generated code in AddRegistration + the CallActionProcedure code in the adapter to see if the proc can be called with that code OUTSIDE the llblgen pro code, like you tried with GetServerTime which worked.

My guess is that the parameters you pass into AddRegistration are all simple types but if they weren't, it might be that that's the cause. Like I said: we dont' see your code in front of us, we therefore have to guess what might be wrong. And I have to add, the error is inside the sybase dll dbdata11.dll (AsaCommand_ExecuteNonQuery). What the error means... no idea.

As Sybase apparently has no energy to invest a single minute in this, we too are in a problematic position: what do you want us to do? I don't see how we could find this without a repro case.

Both apps fail so it isn't that particular SP or that collection of parameters or that one app works and one app doesn't. Calls to ExecuteNONQuery simply fail regardless of the SP called or the parameter collection if called from within the LLBLGEN generated code. The same SP works fine if I call it with ADO.NET code in my app.

And you've verified in the debugger that the SACommand object in CallActionProcedure in the generated dataaccessadapter class has indeed still the proper proc name? (as it's post processed for name overwriting). ? It's the same kind of ADO.NET code as you used in your snippet, with the exception that we define the proc parameters with InputOutput and use a different constructor. These things do matter for these kind of issues. See the generated code for AddRegistration.

But frankly I have no idea where to look for an answer, unless Sybase stops being a pain and starts acting responsible too and tries to help too.

I can save, retrieve and update entities just fine. I just can't call SP's. I even went as far as building a new development machine from scratch to eliminate dll versioning issues or anything being out of synch.

I'm sorry you are annoyed, but I have definitely put in the effort to try to make this work. Sybase won't help because it is a third party tool and once you say "calling an SP from ADO.NET code works fine" they are done. My biggest fear with going with a 3rd party tool for this stuff was this exact problem where we would hit a roadblock with no solution. We are early enough in our development that we can take LLBLGen out of the mix and go with a different solution. I hate to lose the work that we have done, but we are now jeopardizing our schedule and don't have a choice.

I understand you made the effort, that's not the problem. The thing is that apparently there's some problem along the way but as sybase isn't willing to help either, we're the ones to take the blame. I read in your words that you drop our work for something hand-written because of this? Keep in mind that we also use ADO.NET code to use the provider, so 10 to 1 you will run into the same issue sooner or later with other code unless this is cleared up.

In the 6 years LLBLGen Pro is now on the marked I have never left a bug unfixed so a customer would be left in the cold, never. However I feel pretty powerless now, but I can't do much about it, as I don't know what to fix as we can't reproduce the problem. You have to know I'm pretty pissed off about this. Not at you, you have the project to complete and decisions to make according to that, fully understood. However these kind of problems are not the ones I want to deal with either as we are powerless against this.

This isn't the first problematic issue with Sybase, there are more, and they often took ages to fix them (if at all).

If you would be so kind, to once again invest a couple of minutes.

• please download the latest developer release for iAnywhere from sybase, this is 11.0.1
• install on some machine
• generate code against the demo.db, and make sure some procs are in the project
• call a proc in demo.db using the generated code.

This is the setup we used and which worked, if it does at your site too, we can safely say to sybase their newer provider assembly (the one you use vs. the one sybase ships at their website) has a problem. Again, for clarity, our proc call code is all in the generated code and straight forward ADO.NET statements, i.o.w.: it's either something about the values used to create objects there or something else like proc name not found. Also, if the code above fails too, please replace the line in CallActionStoredProcedure: SACommand command = new SACommand(CreateCorrectStoredProcedureName(storedProcedureToCall));

with SACommand command = new SACommand(storedProcedureToCall);

and try again. if that fixes it we know where to look.

Frans,

Actually, your last post tipped me off. You said that you create the parameters as IN/OUT regardless of the actual parameter direction. Sybase doesn't allow you to pass these as IN/OUT (push a value into an out parameter) and it looks like the generated code was getting a default value somehow. I had to change my parameters on my Stored Procs to In Out parameters instead of just out and the problem went away. It just so happened that both of the sample apps I was doing involved a time element (it's a timeclock app so it figures) that was configured as an OUT parameter. Changing these to In Out in the procs fixed the problem. Why that results in "Communication Error" is as frustrating to me as it probably is to you.

Thank you for all of your help to try to get this going. I really appreciate it.

Allen

aha!

I'm really glad there's an explanation why it fails! .

The template hard-codes the direction, which might not be a good idea. The problem likely is that the language we use for our generated code sees InputOutput both as input and as output and can't distinguish between InputOutput and solely Output parameters, I'll see if can come up with a fix for this without having to add a statement to the template language as that is unfortunately a breaking change.

On iAnywhere 10 (the one we use for our tests here and develop against), I can call this proc:

ALTER PROCEDURE "sa"."prGetEmployeeNames"(in @departmentId integer,out @foo integer)
result(name varchar(50))
begin
  set @foo=10;
  select Name from Employee where WorksForDepartmentID = @departmentId
end

without a problem:

[Test]
public void RetrievalProcCallTest()
{
    int fooValue = 10;
    DataSet result = RetrievalProcedures.PrGetEmployeeNames(205, ref fooValue);
    Assert.AreEqual(1, result.Tables[0].Rows.Count);
}

.

When I change the parameter direction in the generated code for foo, to input, or output, doesn't matter.

So I'd like to ask you the following: - make one proc have an Output parameter, e.g. the AddRegistration one. - when you in the generated code, in the AddGeneration method which accepts the Adapter, change for that particular parameter the direction from InputOutput to Output, does it work then?

This to see if we have to find a problem for the value being passed in perhaps for the parameter (as the ctor accepts such a value)

I'll now do a test with a pure Action procedure, as the above is a retrieval procedure.

I will check this this out and see if I can reproduce it this way. I will do it later today but I will be away from my computer until later this afternoon/evening (I am East coast US). I will follow up later today, though.

Ok, thanks for that! . We're on CET so it will be friday for us before we'll see the result here, but I'm at least already very happy you can proceed with your project.

I don't seem to be able to get a value back from the proc, however also not able to repro the problem you got (but it might be related to the fact I'm on 10 and not 11).

When I call the proc in interactive sql, it returns with the value (205) but when I call it from .net the parameter stays 0, no matter what direction I specify.

ALTER PROCEDURE "sa"."prActionTester"(in @departmentID integer,out @foo integer) 
begin
  update Department set
    MeetingRoomCode = 'code'
    where DepartmentID = @departmentID;
  select DepartmentID into @foo from Department where MeetingRoomCode = 'code'
end

So, it's likely specific to 11, and perhaps even to your build of the provider, but nevertheless, if you can confirm that it 1) works when you specify the parameter direction to Output and 2) it returns indeed a value

we know what to solve. (with a <[CurrentParameterDirection]> statement )