11/21/2024 10:52:25 AM
slxdeveloper.com
Now Live!
|
|
| |
| Understanding the SalesLogix 6.2 Connection String |
|
| Description: |
The SalesLogix OLE DB Provider's connection string has changed for version 6.2. These changes are a result of the completely new OLE DB Provider built for SalesLogix 6.2. Taking advantage of all the new things the SalesLogix Provider has to offer starts with understanding the connection string.
|
| Category: |
SalesLogix OLE DB Provider
|
| Author: |
Ryan Farley
|
| Submitted: |
9/6/2004
|
| |
|
| Stats: |
Article has been read 98487 times
|
Rating:
  - 4.9 out of 5 by 7 users |
|
|
|
fiogf49gjkf0d
The SalesLogix OLE DB Provider's connection string has changed for version 6.2. These changes are a result of the completely new OLE DB Provider built for SalesLogix 6.2. Taking advantage of all the new things the SalesLogix Provider has to offer starts with understanding the 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).
Let's start by taking a look at a complete SalesLogix connection string:
Provider=SLXOLEDB.1;Data Source=MYSLXSERVER;Initial Catalog=MYSLXALIAS;User Id=Admin;Password="";Persist Security Info=True;
Extended Properties="Port=1706;Log=On"
As you can see, there are some differences compared to the older connection string format used in previous versions of SalesLogix. Let's break some of those down.
- Provider
This specifies the provider name. For a SalesLogix connection, this will always be SLXOLEDB.1
- Data Source
This specifies the host name or IP address of the SalesLogix Server.
- Initial Catalog
A valid alias that has been configured in the Connection Manager on the SalesLogix Server.
- 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.
- Persist Security Info
When set to 'true' it will persist the user authentication information. See ADO documentation for additional details.
- RWPass
This is the property that will allow you to open a "writable" connection. The RWPassword is defined for each alias in the Connection Manager. There are various scenarios for the RWPass value. Possible Scenarios for ReadWrite/ReadOnly passwords in the Connection Manager:
- Only RW Password configured
RWPass must be present and contain valid R/W password to update data outside of the SalesLogix clients.
- RW and RO password configured
RWPass must be present and contain valid R/O password to view data outside of SalesLogix clients, however data cannot be updated.
If RWPass is present, then data can also be updated outside of the SalesLogix clients.
The extended properties is a list of extra properties that can be used with the SalesLogix provider. These properties are:
- Port
The port that the specified SalesLogix Server is listening on. The default port is 1706. This is configured in the SlxLocalServers.xml file found on the SalesLogix Server.
- Log
Set to either ON or OFF. The default is ON. This enables or disables sync logging for the connection.
- Timezone
Values for this property can be an Index or a Name value for the specified Timezone. Specifies the time zone to use when the OLE DB provider converts date / time fields between the database and client. Date time data is stored in UTC in the database, and must be converted to local time on the client. If this property is omitted, the provider will default to the current time zone settings of the client computer.
The preferred method is using the numeric INDEX value, however as a user could edit the registry entries or create new time zones, this property may not be present or duplicated, so the option to use the STANDARDNAME is also provided as a back up. The STANDARDNAME is actually the registry key name (See HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersions\Time_Zones) in the time zones section, and cannot be duplicated.
If the INDEX or STANDARDNAME is not found (or in the case of INDEX, duplicated) a time zone not found error will be reported.
To disable date / time conversion, specify NONE as the value. In a 6.2 system, this will typically mean all dates will be in GMT.
For a list of INDEX and STANDARDNAME values, see the slx_timezonelist stored procedure.
- 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)
- 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.
- TrimCharFields
This connection string property provides additional backward compatibility for connecting to legacy code. In earlier versions of SalesLogix, the Borland Database Engine (BDE) trimmed strings coming from the database, but ADO does not. TrimCharFields allows trimming to be turning on when returning Data from the database on CHAR Data Types. The default setting is off.
Note: There is also an option for connection using the new SalesLogix 6.2 Integrated Security option. Take a look at Integrated Logins with the SalesLogix 6.2 Provider for more details.
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.
Until next time, happy coding.
-Ryan
|
|
|
|
|
|
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.
|
|
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 12:58:58 PM | fiogf49gjkf0d Where is the slx_timezonelist stored procedure stored?
Does anyone have a good function/procedure for converting from GMT to Local time.
Most of our reports are written with sprocs and hence cant use the OLE Provider to do the timezone conversion.
David | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 1:13:41 PM | fiogf49gjkf0d David,
I have a post on my SLX related blog that goes into this. However, as Todd Hardin mentions in the comments, it does not take daylight savings into consideration.
See http://saleslogixblog.com/rfarley/archive/2004/11/01/1159.aspx
-Ryan | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 11/8/2004 1:34:01 PM | fiogf49gjkf0d I spotted that one. Unfortunately the SQL getutcdate() cannot be used within a function.
What I'm really looking for is the Table of timezones SLX recognises. If you look at the USERINFO table you get a short description of the timezone the user is in. I suspect SLX hasnt made this very sophisticated with a table of Daylight savings zones etc.
Anyway, Try this one we will use
ALTER FUNCTION [SYSDBA].[UTC_LocalTime] (@UTCTime DateTime, @USERID CHAR(12))
RETURNS DateTime AS
-- SELECT [SYSDBA].[UTC_LocalTime] ('10/25/2004 6:29:39 PM','ADMIN')
BEGIN
DECLARE @Year Int
SELECT @Year = DatePart(Year,@UTCTime)
Declare @TimeZone varchar(50)
Declare @LocalTime datetime
DECLARE @BegApr varchar(30)
DECLARE @EndOct varchar(30)
SET @BegApr = Convert(varchar,@Year)+'-04-01 00:00:00'
SET @EndOct = CONVERT (varchar,@Year)+'-10-31 00:00:00'
--checks the first day of month in relat
-- ion to the first Sunday of the month. Se
-- ts @AprDay to the appropriate date. For
-- Daylight savings.
DECLARE @AprDay Int
SET @AprDay =
CASE Datepart(Weekday,@BegApr)
WHEN 1 THEN 01
WHEN 2 THEN 07
WHEN 3 THEN 06
WHEN 4 THEN 05
WHEN 5 THEN 04
WHEN 6 THEN 03
WHEN 7 THEN 02
END
DECLARE @DSTStart DateTime
SET @DSTStart = Convert(DateTime,Convert(varchar,@Year)+'-04-'+Convert(Varchar,@AprDay)+' 07:00:00')
--checks the last day of month in relati
-- on to the last Sunday of the month. Sets
-- @OctDay to the appropriate date. For Da
-- ylight savings.
DECLARE @OctDay Int
SET @OctDay =
CASE Datepart(Weekday,@EndOct)
WHEN 1 THEN 31
WHEN 2 THEN 30
WHEN 3 THEN 29
WHEN 4 THEN 28
WHEN 5 THEN 27
WHEN 6 THEN 26
WHEN 7 THEN 25
END
-- default to Central if the user doesnt have a timezone in the database.
select @timezone = isnull((select timezone from userinfo where userid=@userid),'Central Standard Time')
DECLARE @DSTEnd DateTime
SET @DSTEnd = Convert(DateTime,Convert(varchar,@Year)+'-10-'+Convert(Varchar,@OctDay)+' 06:00:00')
DECLARE @OffSet DateTime
-- NON-Daylight savings adjustments.
SELECT @LocalTime =
(CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 8, @UTCTime)
ELSE DATEADD(hh, - 6, @UTCTime)
END)
-- Daylight savings adjustments.
IF @UTCTime >= @DSTStart AND @UTCTime <= @DSTEnd
Begin
SELECT @LocalTime =
CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 4, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
ELSE DATEADD(hh, - 5, @UTCTime)
END
End
RETURN @LocalTime
END
| |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 4/11/2005 7:49:28 PM | fiogf49gjkf0d does it support command paramaters from within .net? i can only find examples that use adodb via com interop in .net. i would like to do it natively throuhg the oledbcommand.parameters.add but this only generates an empty exception when trying to run this. if this is possible, anyone have an example? or must i only go through adodb or not use sql params and have it all inline? thanks. | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 2/16/2007 12:48:12 PM | fiogf49gjkf0d Ryan , What about the connect timeout property in our connection string.By Default it is 0 meaning there is no timeout.Is there any information availble for that property in 6.2.4 provider version?
My customer portal users complain that insert ticket process is a "race against time" thing...dont understand why/how..when i test things are OK till ~20 min | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 8/7/2007 10:07:31 PM | fiogf49gjkf0d Not working in C# .NEt
static string SLXconnstring = "Provider=SLXOLEDB.1;Password="";Persist Security Info=True;User ID=Admin;Initial Catalog=OCC;Data Source=RJSAMP-D610;Extended Properties="PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;SVRCERT=;" + Char(34);
Help!
RJ Samp | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 8/7/2007 11:09:13 PM | fiogf49gjkf0d RJ,
In C#, you can simply escape the quotes. For example, if I want a string variable to have the contents This is a "quoted" string I need to set it as follows:
string var = "This is a \"quoted\" string";
So, for a connection string you'd escape the quotes the same way:
static string SLXconnstring = "Provider=SLXOLEDB.1;Password=\"\";Persist Security Info=True;User ID=Admin;Initial Catalog=OCC;Data Source=RJSAMP-D610;Extended Properties=\"PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;SVRCERT=;\"";
Make sense? | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 10/31/2007 4:03:53 PM | fiogf49gjkf0d Hi all:
Does anyone know if RWPass is still required in 7.2? I'm trying to test this part of my external application, and it seems I can still update data even without the "RWPass" part of the connection string, even though the Read/Write password is defined.
Any comments on current experience would be appreciated! Does anyone know what the error should be in the above case?
thanks,
Kathy Lass
Expandable Software, Inc. | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:15:24 AM | fiogf49gjkf0d I have an ASP web page accessing a SQL server using the connection string:
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Driver={SQL Server};Server=SERVER;Database=TESTDB;Trusted_Connection=yes;"
objConn.Open
How do I modifiy the the connection string to work with the SLX provider? I tried the code below and I get an "Expected end of statement" error.
DIM conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=TESTSLX;User Id=admin;Password="password";Persist Security Info=True;Extended Properties="Port=1706;Log=On";"
conn.Open
If I take out the extended properties portion of the string I get an error like below:
ADODB.Connection error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/includes/SLXconnection.asp, line 5
Thanks in advance for any help on this.
| |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:28:45 AM | fiogf49gjkf0d KevLar,
You can't have quotes in the middle of a string. If you do (as you have in the code you posted) it ends the string. So the error you're getting is accurate.
You can either omit the quotes and all will still work fine, or escape them. Try this:
DIM conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SLXOLEDB.1;Data Source=SERVER;Initial Catalog=TESTSLX;User Id=admin;Password=password;Persist Security Info=True;Extended Properties=Port=1706;Log=On;"
conn.Open
-Ryan | |
|
| Re: Understanding the SalesLogix 6.2 Connection String
Posted: 3/12/2008 12:55:04 AM | fiogf49gjkf0d That was quick thanks Ryan.
No more errors, but the page just loads and loads. Nothing happens, it doesn't even time out. I created a test page that just opens and closes the connection just to be sure.
I'm running SBS 2003, SQL 2000, and SLX on the same box (which is also serving up the ASP page in question). Are there certain permissions I need to set to allow access to the SLX provider? Is there somewhere else I should be looking for the problem? | |
|
| |
|
|
|
|
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...
|
|
|
|
|
|