Home | Forums | Contact | Search | Syndication  
 [login] [create account]   Monday, June 24, 2024 
View User Profile  

 User Profile - Jiho Han  
Jiho Han
(SalesLogix Business Partner)
Infinity Info Systems


Log in to send this member a message!

 Jiho Han's Contributions
Jiho Han has contributed 11 comments and 0 articles.
Select to view:    
Comment: Re: The Myths and Legends of Prepared Queries
I see.

So then I assume the migration from BDE to ADO is still in progress as of 6.2?

If possible, can you shed a little light on what version 7(or the next major upgrade) will bring about? I've only found one snippet on the web about the web client being re-architected. Will the client stay Delphi-based and VBScript as the customization langauge? Any consideration regarding moving to .NET?

Is there a provider technical bulletin for 6.2 similar to existing 6.1 one?


Author: Jiho Han - 9/22/2004

Comment: Re: The Myths and Legends of Prepared Queries

Thanks for your response. If it seemed that I was speaking outside the context of SalesLogix system regarding the provider, I apologize for misleading you.

I understand the performance enhancement made by your reworking of the provider in 6.2. I had forgotten SalesLogix is built in Delphi(still true I assume). Onto your second point, you mention that Borland forces you to do things in a certain way. If you don't mind, can you elaborate on the new provider design? Especially where Borland and its technology fits in - unless you mean your provider is written in Delphi and is using its "ADO wrapper" between your provider and the SQL OLE DB Provider.

"I believe this forum is continuing to expose a lot of technical details about the provider."

Still the forum is not an adequate documentation although it and Ryan's blog is turning out be an oasis in the desert that is the SalesLogix developer information. :) And until you provide a guide or tutorial on the topic we're on, most SalesLogix developers will go on writing static SQL statements conjoined with ampersands and replacing single quotes for every data modifications which is, needless to say, buggy, less maintainable, and less performant.

Oh and has there been changes to the SQL parser as well? What's allowed and what's not?

Thanks for chiming in. I appreciate it. I know you don't have to and the questions could have been stranded out here until nobody really cares.


Author: Jiho Han - 9/22/2004

Comment: Re: The Myths and Legends of Prepared Queries

On the contrary to what? I am not disagreeing with you that 6.1 was not performant with its preparing every statement and that 6.2 is an improvement over 6.1. I am in complete agreement with you in that 6.2 is a vast improvement over 6.1. See my comment on 7/30/2004. I know about 6.1 provider's faults and have known about it since it came out. And I laude SalesLogix for the effort that's been made toward improving the provider for 6.2.

I also acknowledge that the current model employeed by 6.2 provider is indeed efficient. Anyone who's read the SQL BOL can attest to this. Look up sp_executesql in the documentation and it's all spelled out - that it basically optimizes the heck out of statements and caches the execution plans, etc. that sometimes it may even prove it performs as well as sp_prepare. And the SQL Server team put tremendous efforts to this functionality apparently. These also I've known for a long time. So if it seemed like I wasn't giving proper credit to SQL Server, let it be duly noted that I do indeed give credit where credit is due.

Now to the issues that I am NOT in agreement:

"In a disconnected model, prepares rarely make any sense. Given our client is now disconnected, and ADO.NET is also a disconnected model, a prepare (by the SalesLogix provider) is even more unnecessary."

ADO.NET is not only a disconnected model. It gives you an option. DataSet is disconnected while DataReader is not.
And I'd argue that prepares in some cases would make sense in disconnected model. If you are disconnected, then you will have to sync up with the host eventually. When this occurs, chances are the statements that need to run against the host server may fit the pattern where preparing the statements before executing a whole bunch of similar queries may make sense. Again, you may argue that the difference between sp_executesql and sp_prepare is minimal if not non-existent. This I am not in total disagreement. But I'm not going to go as far as you did and say the performance difference is not worth it.

Also, my being "upset" with the fact that it doesn't actually "prepares" a statment is in that it's misleading. I expect Command.Prepared = True to either create me a prepared query or generate an error. Should SalesLogix have provided a documentation noting the difference I may have yielded.

One question though is in your statement that says "Given our client is now disconnected", I assume you mean the SalesLogix client. Has SalesLogix recently changed its architecture that this is now true? Was it a connected client and now it's not?

I don't want to argue for the sake of argument because it's a waste of time. I can live with all these issues - I do it anyway, everyday - but I just wish SalesLogix would provide a documentation where it's seriously needed.

Author: Jiho Han - 9/22/2004

Comment: Re: Using Classes in VBScript
One thing that really sucked the juice out of me for the longest time in using classes in VBScript is that you never know when the referred class is going to be picked up by the garbage collector and disposed of. This has some implications in how you would code.

You see, I had an error handling on the client - the "user" of the class - and this function checked for a certain error code and if not in the range, it simply exited so the error would be propagated to the next tier. The thing is that the error was disappearing as soon as the function exited and where I expected an error to be caught by the next tier, there was none. You see, I had error handling routine in the Class_Terminate sub in the class I was using. So, the On Error Resume Next in the sub basically erased my previous error that I was looking for.

One work around other than not doing error handling in your Class_Terminate, is to set all class objects to Nothing before you exit. Before that though, you would have copied the error info somewhere otherwise you'd still lose it. Then when the objects are set to Nothing - this does not mean they are disposed though, it only means Class_Terminate is called -, you can rethrow the error. This is not a cure-all either though since if you have classes that use other classess that use yet other classes, and so on, it can get very hairy and almost impossible to manage. So, don't use error handling in Class_Terminate if you don't have to although I know sometimes you can't avoid it.

Thank God for the exception system in .NET...

Author: Jiho Han - 9/22/2004

Comment: Re: The Myths and Legends of Prepared Queries
ok, so I've done some testing on 6.2 and there are some issues which may truly be issues or maybe not.

"Version 2 of the SalesLogix OLE DB provider (first released in Beta 3 of the 6.2 release) supports prepared commands, and natively. It does not generate a prepare on the server, but still binds to the underlying provider only once, so the performance increase is very significant."

So, I missed the part about the provider not generating a prepare on the server and got a little upset that it still wasn't working in 6.2. Indeed, on the server, the statements execute as sp_executesql which isn't half bad; however, it's far from what Prepared is supposed to do. There really may be some performance gains between the SLX provider and the underlying provider. I have a feeling that the reason it doesn't generate prepares on the server is due to the way SLX provider pools its connections perhaps, I don't know. In fact, I am not completely sure, whether a connection I create and hold on to, will still be the same connection later on. It's not so in 6.1. Maybe it is in 6.2.

One thing I've noticed in 6.1 is that if you start a transaction on the connection, it will retain the same underlying connection. By the way, while on the subject of transaction, I've seen situations where a series of statements inside a transaction committed when an error occurs in the script and the connection closed or garbage collected without me explicitly committing, which is an incorrect behavior. I do not know whether this is still true in 6.2. We'll see.

I really hope that SalesLogix publish some kind of technical brief/doc on the provider: features supported, not supported, quirks, etc. Me having to test out all scenarios - and sometimes finding out in production - is not very nice.

From the quote above, though, what does "natively" in "supports prepared commands, and natively" mean?

Author: Jiho Han - 9/22/2004

Comment: Re: The Myths and Legends of Prepared Queries

As far as I know, Prepared property is not supported by SalesLogix OLE DB Provider. Actually, every single query is "prepared" which is terrible.
Simply it plain don't work.

Author: Jiho Han - 7/30/2004

Comment: Re: Using Code Templates in Saleslogix 6.x to Develop Class Objects
Check this out!

What you said inspired me and led me to this solution. Simply add a reference to SalesLogix.exe(since it's com) in VB6 IDE and everything is there:

Dim Application As SlxApplication
strAccountID = Application.BasicFunctions.GetIDFor("ACCOUNT")

It should also be possible to do this using VS.NET.

Author: Jiho Han - 7/23/2004

Comment: Re: Using Code Templates in Saleslogix 6.x to Develop Class Objects
Yo, that'd rule if SLX did that.

In any case, you wouldn't happen to have one of those stubbed out dlls lying around would you? :)
Ahh.. I guess I got some mad typing to do...

Author: Jiho Han - 7/22/2004

Comment: Re: Using Code Templates in Saleslogix 6.x to Develop Class Objects

That's an interesting idea. Actually one of my co-worker uses a third party editor - textpad or something - but I didn't want to copy and paste constantly when there weren't enough values to switch. Hmm... you actually gave me another idea altogether but I just don't know how or if it's possible. I want to stay in VS.NET all the time if I can but I don't know whether I can extend intellisense to support vbscript(or saleslogix intrinsic objects). It might involve vs extensibility api etc. Hmm....

Too bad about the architect situation but I guess you would know if you were inside at one time.

Author: Jiho Han - 7/22/2004

Comment: Re: Using Code Templates in Saleslogix 6.x to Develop Class Objects
I think this is great! I've also been trying to use some kind of code generation scheme to do my "dirty work" for me for some time.

I already have classes similar to what Scott generates using the template albeit untyped, meaning I access my fields as Acct.Fields("ACCOUNTMANAGERID") vs. Acct.AIS_AccountManagerID. Clearly, the latter is a better approach in most cases since knowing which fields are available compile time is better than finding out at run time. However, this is kind of moot since SLX environment is unable to take advantage of the latter approach since intellisense can't confirm our custom class members as it does the built-in objects. Also, even if you did make a mistake typing Acct.AIS_AccountManagerIT(note the typo), you won't know that you did this until you run the code.

ok so this is sounding like a feature request again but I would like the SLX guys to release info on their architect environment so that we can develop extensions. The architect is clearly lacking in many features. Even if they just released the spec on their plugin model, I'd be happy or an API to create/update a new plugin would be great because then I can have a code gen scheme generate all my classes as above and create/update vbscript plugins automatically.(I actually know that I can now but I want an official way to do this) Imagine this - every time you create/update tables, you can run the process and your classes will be ready for use right away!

In any case, kudos to you for the awesome stuff and bring us that much closer to our modern development! If only we had a way to use TDD with vbscript...

Author: Jiho Han - 7/22/2004

Comment: Re: The Myths and Legends of Prepared Queries

You can retrieve the native connection string and use that for complex queries - I think, never tried - instead of sps if you want.


This is a great info. I am still working with v6.1 and I find that there are a lot of issues with the provider. Maybe these are known issues and have been fixed in 6.2(BTW, when is the ETA for this?):
1. Command.Prepared = True, does not work - has no effect, and furthermore, if you tried to execute more than once, it errors out.
2. Maybe related to #1, but every single .Execute is prepared! What's up?
3. Connection.Execute methods should not be prepared but it does! Instead it should be using sp_executesql
4. There are some issues with varchar and longvarchar type parameters and using null or empty string values.
5. Unless you start a transaction on a given connection, there is no guarantee that the underlying connection to db(aka spid) is the same one you just used. This is either a consequence of #2 or a design decision that was made on the provider, which makes me wonder why. BTW transactions do work right? I hope so because if not, man...
I could go on and on ad infinitum.

Author: Jiho Han - 6/28/2004


 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
page cache (param): 6/24/2024 9:42:54 PM