11/21/2024 10:52:25 AM
slxdeveloper.com
Now Live!
|
|
| |
| Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior) |
|
| Description: |
The new OLE DB Provider allows you to access SalesLogix data via ADO or ADO.NET. However, before you can use the provider, it is important to understand how to connect to it. This article will examine the various parts of the SalesLogix OLE DB Provider connection string. Applies to version 6.1 and prior.
|
| Category: |
SalesLogix OLE DB Provider
|
| Author: |
Ryan Farley
|
| Submitted: |
11/7/2002
|
| |
|
| Stats: |
Article has been read 54001 times
|
Rating:
 - 5.0 out of 5 by 8 users |
|
|
|
fiogf49gjkf0d
The new OLE DB Provider allows you to access SalesLogix data via ADO or ADO.NET. However, before you can use the provider, it is important to understand how to connect to it. This article will examine the various parts of the SalesLogix OLE DB Provider connection string.
A connection string is something used when connecting to an OLE DB provider. This is something that you will need to connect to a database via ADO or ADO.NET. This article will explain the various parts of the connection string, not how to use it. Other articles are provided on this site to explain using the SalesLogix connection string with ADO & ADO.NET. Note that this article focuses on the parts of the connection string used when making a connection. There are other uses of some of these connection string parameters that will be explored in a future article (watch for Related Articles at the end of this article).
-
Provider
The provider indicates the OLE DB provider, or driver, to use. For SalesLogix, this will always be 'SLXNetwork.1'
-
Data Source
The Data Source is an alias defined in the Connection Manager. This alias points to a SalesLogix server and database - but is defined on the server (that will be defined in the extended properties below).
-
User ID
This is a valid SalesLogix user name (not a database level user, like sysdba, but a SalesLogix level user, like Admin or Lee). The user provided here will dictate the SalesLogix security for the connection. For example. If you use the user 'Lee' here, then the data returned from SELECT statements opened with this connection will only return rows that Lee has access to.
-
Password
The password that corresponds to the user name given for the User ID.
The extended properties is a list of extra properties that are required by the SalesLogix provider. These properties are:
-
SLX Server
This is the name of the server where the Data Source (defined above) exists. Note: previously, this article did not cover this parameter. If this parameter is omitted, then the 'local' server is assumed. However, be aware, in some cases, such as on a remote or a computer that is not connected to a network, the connection will fail without this parameter explicitly included in the string. Therefore, this parameter should always be present to indicate the SLX Server when making a connection, even if the local server is used. Thanks to Stuart Carnie for this correction (introduced in v6 beta3) and to Stephen Redmond for tracking down the problem. (There are some exceptions to this which will be discussed in a future article).
-
Address
This is the location of the provider (Not to be confused with the SLX Server). This should always be 'localhost' (or some equivalent to indicate the local machine such as 127.0.0.1, but 'localhost' is recommended).
-
Type
For the current build of SalesLogix (initial gold release build 6.0.0.3205) this is always ODBC. Don't be confused by this. The SalesLogix Provider does not use ODBC. This basically tells the provider which SQL parser to use for parsing SQL statements.
-
Port
The port that the SalesLogix Server is running on. Although this can be changed, the default is 1706.
Now that we have seen all of the parts of the connection string, let's look at how to put it together. Let's first put standard properties together. The order does not matter. The list or properties must be delimited with semi-colons (;). Let's use the SalesLogix Admin user with no password and connect to an alias named SALESLOGIX_EVAL. Remember, this is the alias defined in the Connection Manager on the server (which we'll add later).
Provider=SLXNetwork.1;User ID=Admin;Password="";Data Source=SALESLOGIX_EVAL;
Now, let's add the extended properties. The extended properties is a quoted string. We'll use a server named SLGXSERVER (This is where the alias used as the Data Source defined above exists).
Provider=SLXNetwork.1;User ID=Admin;Password="";Data Source=SALESLOGIX_EVAL;Extended Properties="SLX Server=SLGXSERVER;
ADDRESS=localhost;Type=ODBC;PORT=1706"
Now we have a complete connection string. One thing to note is that this connection string will open a read-only connection. In order to have a "writable" connection we must define the RWPassword which will be discussed in the next section.
There are several extended properties that can be used in the connection string. These are simply added to the extended properties part of the connection string.
-
RWPass
This is the most important of the extended properties. This is the property that will allow you to open a "writable" connection. The RWPassword is defined for each alias in the Connection Manager. Look for further explanation of the RWPassword in a future slxdeveloper.com article.
-
IncludeCalcFields
This extended property is set to either ON or OFF. The default setting is OFF. This tells the provider to include calculated fields in SELECT * queries. You can still manually select individual calculated fields (i.e. SELECT FULLNAME FROM CONTACT)
-
Log
Set to either ON or OFF. The default is ON. This enables or disables sync logging for the connection.
-
Impersonate
Set this extended property to a valid SalesLogix username (such as lee or bgates). Adding this to the connection string will enable security for the user you are impersonating for security. For example, if I supply 'lee' as the value for Impersonate, then all queries will return only rows that lee has access to, even though you are logged in as admin. Why is this useful? Because you can act as the user 'lee' in the database without logging in as lee - so you don't need to know lee's password.
- AdoNetSupport
This new parameter was added for ADO.NET support. It is not required for ADO.NET, but will fix the error "cannot convert VARCHAR to INT is reported" when using an updateable DataSet. Valid values are ON & OFF.
Understanding the parts of the SalesLogix connection string will provide you with the knowledge to build great new applications to take full advantage of the SalesLogix OLE DB provider. Stay tuned for articles showing how to use the connection string via ADO and ADO.NET from various languages such as C#, VB, & VB.NET.
Until next time, happy coding.
-Ryan
- 12/2/2002 - Updated to reflect correct information about SLX Server parameter introduced in v6 beta3. Thanks to Stuart Carnie for this correction (introduced in v6 beta3) and to Stephen Redmond for tracking down the problem.
- 1/22/2004 - Updated to include the ADONETSUPPORT param for ADO.NET support.
|
|
|
|
|
|
Rate This Article
|
you must log-in to rate articles. [login here]
|
|
|
|
Please log in to rate article. |
|
|
|
Comments & Discussion
|
you must log-in to add comments. [login here]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Data Link files
Posted: 2/4/2003 9:12:03 AM | fiogf49gjkf0d In case someone else is wondering how to build these expressions using the OLE DB connection builder and put them into a file (like you do with ODBC) it's very easy. Some times you will have the option to create a new "Data Link file" directly in the windows explorer, but if you don't - don't panic - just create a text file and change the extension to .udl then you can access the OLE DB builder just double-clicking the file. This is convenient for some apps, for example Crystal Reports 9 where for some reason you can only enter database, name and password in the first screen ... and of course you're missing the server name which has to be entered in the Extended Properties field. A Data Link file makes is much easier and you can use it every time you create a report. | |
|
| Re: Data Link files...
Posted: 2/4/2003 6:10:36 PM | fiogf49gjkf0d True, you can use UDL files to create connection strings, however, it is still important to understand the various parts of any connection string so you know exactly what you're creating.
To assist in creating connection strings, I uploaded a utility which makes it easy to create connection strings (sits in tray for easy access). Take a look here:
http://www.slxdeveloper.com/devhome/page.aspx?id=35&articleid=36 | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/16/2004 11:19:47 AM | fiogf49gjkf0d Is there a way using the Connection String to determine if the OLE DB connection is against an Oracle database vs. a SQL database? I've written a VB COM object that connects to the SLX database and inserts accounts and contacts. However, the insert statements are slightly different between the two databases - primarily the handling of date fields. Since I have network users hitting Oracle and remote users hitting the MSDE, I need a way to know which database they are using so I can apply the proper SQL statements. | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/16/2004 6:44:32 PM | fiogf49gjkf0d Eric,
Yes, use the slx_getNativeConnInfo procedure to return the underlying native connection string used by the provider. This will tell you based on the provider name in that string if it is SQL or Oracle.
-Ryan | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/17/2004 8:54:38 AM | fiogf49gjkf0d Thanks Ryan. I also learned that the Application.BasicFunctions.SystemInfoFor("DatabaseType") will give you similar information. I pass the value from this function to my COM object and I know which database I'm using.
A tech support person from SalesLogix also informed me that when inserting date values into SalesLogix, use the following format so you won't need to write separate sql statements for Oracle and SQL Server. Apparently all dates sent to the SLX OLEDB provider need to be in this ISO standard. My example uses VB code to format the current date:
Format(Now(), "YYYYMMDDHHMMSS")
I tried it and it works. Hopefully others will find it helpful too.
Eric | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/17/2004 9:51:24 AM | fiogf49gjkf0d Eric,
Yes all dates need to be ISO formatted dates. While in VB you can use the Format function, however, you'll need to modify it a bit from what you have. There needs to be a space between the date and time portions, and colons separating the time parts. This should give you a proper ISO formatted date for the SLX provider:
Format(Now(), "YYYYMMDD HH:MM:SS")
Keep in mind however that you only need to format the date this way when using the date as a string in a SQL statement, not when using parameterized queries or updateable recordsets. You can take a look here for more info:
http://saleslogixblog.com/rfarley/archive/2004/03/02/392.aspx | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/23/2004 7:25:12 PM | fiogf49gjkf0d The date strings are not quite right. They should be: Format(Now(), "YYYYMMDD HH:NN:SS")
If you use MM for minutes you WILL be surprised!!! From experience....MM is for month and NN is for day.
| |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 3/23/2004 9:22:09 PM | fiogf49gjkf0d Doh! Thanks Larry, good catch. I typically don't use VB, I am a semi-colon curly-brace kind of guy so I really should have checked before. Thanks. | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 5/26/2004 10:07:34 AM | fiogf49gjkf0d If i try do a INERT-Statement over the SLX OLEDB Provider i get the following error:
"Failed to parse SQL.INSERT INTO sysdba.ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio');"
What am i doing wrong? | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 5/26/2004 11:01:41 AM | fiogf49gjkf0d Alexander,
You have this:
INSERT INTO sysdba.ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio');
1) No need for the sysdba part, get rid of that.
2) Drop the semi-colon from the end.
ie:
INSERT INTO ACCOUNTPRODUCTGROUPS (ACCOUNTID,PRODUCTGROUP) VALUES ('AA2EK0013017','Audio')
-Ryan | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 6/25/2004 2:03:44 PM | fiogf49gjkf0d Can you call stored procedures through the 6.1 provider? I get "Failed to parse SQL" errors everytime I try through OleDb namespace in in VS.NET. | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 6/25/2004 2:49:10 PM | fiogf49gjkf0d Rich,
The 6.1 provider does not allow for stored procedures to be called. However, there is some hope coming in 6.2.
-Ryan | |
|
| Re: Understanding the SalesLogix OLE DB Connection String
Posted: 7/29/2004 3:59:27 PM | fiogf49gjkf0d I'm having some trouble when executing an insert statement against a remote database. I've written code that inserts Account and Contact data using the OLE DB Provider via a VB COM object. All is well when I run it against my server database, but I'm having issues when I attempt to run the same code against the remote database. I receive the error "Cannot start more transactions on this session" when the insert statement executes. Has anyone else run into this error?
Microsoft has a published KB article stating the OLE DB Provider for SQL Server does not allow nested transactions, but as far as I can tell, I'm not using nested transactions. | |
|
| Re: Understanding the SalesLogix OLE DB Connection String (For versions 6.1 and prior)
Posted: 5/10/2005 4:00:08 PM | fiogf49gjkf0d Has anyone attempted to use the OLEDB provider as a linked server in another SQL Server database. I am attempting to do this and receive a SLX...IDBInit error from the provider.
Help would be great! | |
|
|
|
|
|
Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
|
|
|
|
|
|