The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Getting a full connection string to SLX DB
Posted: 10 Jan 07 7:26 PM
|
fiogf49gjkf0d Hi,
i want to develop a little external application in .NET 2.0. I want to use a OLEDB Connection to modify the data. But how do I get the correct OLEDB connection string of the currently running SLX Application.
With SLX.GetNewConnection.ConnectionString, I will get something like this:
Provider=SLXOLEDB.1;Integrated Security="" ersist Security Info=False;User ID=Admin;Initial Catalog=SALESLOGIX_SERVER;Data Source=SLXSRV;Extended Properties="PORT=1706;LOG=ON";Location="";Mode=ReadWrite
But there is the Password missing. I can insert the correct connectionstring directly to the code or to a config, but I don't like that, and app. should be generic.
I have read Ryan's article "Connecting with the RWPassword From External Applications", but this seems to work only with v. lower 6.2.
I need some kind of conversion that I open the connection like this:
con=new oledb.oledbconnection(convertconnectionstring(SLX.GetNewConnection.ConnectionString))
Can anyone help.
Thanks in advance.
regards
Mark
|
|
|
|
Re: Getting a full connection string to SLX DB
Posted: 10 Jan 07 8:28 PM
|
fiogf49gjkf0d OleDbConnectionStringBuilder. Ryan's example doesn't use it since it was originally written for .NET 1.1 but it's pretty trivial to use.
Example:
OleDbConnectionStringBuilder stringBuilderConnection = new OleDbConnectionStringBuilder(SLX.GetNewConnection.ConnectionString);
stringBuilderConnection["Password"] = "Blah";
MessageBox.Show(stringBuilderConnection.ConnectionString);
There's a couple of other possible issues: Properties=" must be escaped correctly, usually with adequate \". If you do a MessageBox.Show it should look exactly like you pasted. I believe RWPassword is negated by logging in as a user. RWPassword is for external access by applications without the need of having a valid SLX login. |
|
|
|
Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 6:03 AM
|
fiogf49gjkf0d Thanks for your answer.
Maybe my problem was not clear enough. I want to read out the password on the fly, so that I can use the app. on different dbs.
The connection string is okay, and If I add the PW manually with stringbuilder it will work. |
|
|
|
Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 6:11 AM
|
fiogf49gjkf0d Originally posted by Mabo
I want to read out the password on the fly, so that I can use the app. on different dbs. |
|
Sorry if I'm missing the point, but is it the slx_RWPass() function that you need to get the RW password?
I'm not sure how you call it in .NET though.
|
|
|
| |
|
Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 7:48 AM
|
fiogf49gjkf0d Originally posted by Mabo
I think this will only work before 6.2 and the new oledb provider.
I cannot start the function, because I cannot connect in 6.2. |
|
It works for me in 6.2.3.
I first connect to the DB RO, then run slx_RWPass(), then close the connection and reopen it RW.
I could post the code, but as I implied earlier it's in VB6. |
|
|
|
Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 8:02 AM
|
fiogf49gjkf0d Ryan's article about RWPass explains it all, but it was written for 6.1. This article http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=55 explains all of the parts of a 6.2+ connection string (I don't believe it changed in v7). So you have to look at the article through 6.2+ eyes if you're not on version 6.1.
RWPass is necessary to write data but only necessary to read data when a RO pass is set in Connection Manager. I would say you NEED a valid username/password to run slx_RWPass() and that's the problem. SLX.GetNewConnection isn't giving you the logged in user's password at all and I'm not sure on the best way to retrieve it. |
|
|
|
Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 8:42 AM
|
fiogf49gjkf0d Hello,
first at all. ->Thank you very much for so many answers in such a short time. Good community.
The main problem is what Jeremy wrote at the end. I need the password to connect, so a function which will be execute on an open connection could not work. Maybe it is not possible to read out the correct password, for some security reasons.
In the COM Connection Object in the VB Script Section, the connection works because it has the hidden password in it. I thought this could be read with a trick.
Maybe I must write a little InputBox for my Tool, so that every user must enter his password for one time, and it will by crypted on the hd.
Best regards
Mark
|
|
|
|
@Stuart: Re: Getting a full connection string to SLX DB
Posted: 11 Jan 07 11:22 AM
|
fiogf49gjkf0d HI,
You use VB6 and SLX returns a normal ADO connection with a hidden password part. In VB.NET I can only get the connection string. The connection is not compatible to ADO.NET. So I only habe the connection string and not the hidden password integrated in the ADO Object.
Regards
Mark
|
|
|
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 12 Jan 07 10:11 AM
|
fiogf49gjkf0d I use the method I described here http://saleslogixblog.com/rfarley/archive/2005/05/25/2856.aspx
1) Add a script to SLX that looks like this (We'll name this System:GetConnectionExternal):
Sub Main
Application.BasicFunctions.InvokeSetResult Application.ConnectionString
End Sub
2) From your .NET application, invoke that script and it will return the connection string in use by the SLX client:
SalesLogix.SlxApplication slx = new SalesLogix.SlxApplication();
slx.BasicFunctions.DoInvoke("ActiveScript", "System:GetConnectionExternal")
string connstring = slx.BasicFunctions.InvokeResult
MessageBox.Show("The connection string is:\r\n\r\n" + connstring);
Make sense?
Works every time and no need to add in password or anything.
|
|
|
| |
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 25 Feb 07 10:51 AM
|
fiogf49gjkf0d Any way to get a Native SQL Server connection at runtime from within SLX and not using a UDP file and NOT going through the OLE DB Provider?
I have a field that the client doesn't want the user's to see.....so Field Level Security is set to Not Accessible.....the Provider is nulling out the field (it's an ACCOUNT NUMBER).
If the Account Number is greater than '0' then the account is owned by corporate....read only for nonAdmin users.....
If the Account Number is '' then the account is a prospect/lead and owned by sales.....write away.
Of course the Provider is nulling out the value, so even corporate data has a 'blank' account number......
Hence the need to go after SLX at runtime in a SQL connection.....whether it's on a remote, remote office, or LAN Server.
Assume SYSDBA masterkey for SQL log in is fine......but what's the server name?
|
|
|
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 25 Feb 07 10:39 PM
|
fiogf49gjkf0d RJ,
You say from within SalesLogix, but NOT going through the provider, so I assume the "slx_GetNativeConnInfo" stored procedure inside the provider won't work for you? Unless I'm missing it in your post, is there a technical or business reason that won't work? It doesn't return the password, but you state that assuming "masterkey" for the password is fine. Is the problem that it won't work on a remote, remote office or LAN server? It will get you the underlying connection string from within SLX - presumably from whatever database the user is logged into. The result column of the procedure is named "CONN".
Jeff
|
|
|
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 25 Feb 07 11:10 PM
|
fiogf49gjkf0d I'm inside SLX......and never have used a stored procedure.
Think this is a lot simpler than I thought......true the screen databound field doesn't have the value (the provider is stripping it out).....
But if I simply do a
TheValue = GETFIELD(" NonViewAbleField", "TABLE", "ACCOUNTID = '" & & "' ")
Then it shouldn't be stripped out by the provider?
|
|
|
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 25 Feb 07 11:54 PM
|
fiogf49gjkf0d I'm not sure what you mean by the provider stripping out a value, could you explain more there?
As far as getting a native connection, you can get the values for the connected server, database, user, etc using queries in SLX (via connections done with the provider) and then form your own native connection string:
-- Get server
select @@servername
-- Get database
select db_name()
-- Get user (will be sysdba)
select user_name()
Or do as Jeff said, use the SLX procedure slx_getNativeConnInfo. This will return a native connection string minus the password for the sysdba user. If you know that then just append ";password=masterkey" to the end and make your connection.
ie:
Dim rs
Dim connstr
Set rs = Application.GetNewConnection.Execute("slx_getNativeConnInfo()")
connstr = rs.Fields(0).Value
rs.Close
Set rs = Nothing
MsgBox "Native connection string is:" & vbCrLf & connstr
Dim newconn
Set newconn = CreateObject("ADODB.Connection")
newconn.Open connstr & ";password=masterkey"
' do whatever with your new native connection
'...
Make sense? |
|
|
| |
|
Re: @Stuart: Re: Getting a full connection string to SLX DB
Posted: 16 Aug 07 10:33 AM
|
Thanks to Jonathan Douglas:
Written in C# .NET but wouldn't be that hard to redo it in VB .NET
static string SLXconnstring = GetSlxConnString("RJSAMP-D610", "OCC", "Admin", "", "");
//////////////////////
/// Returns a properly formated SLX Provider connection string
///
/// Server name
/// Alias name
/// Username
/// Password
/// Read/Write Password
/// SLX Provider Connection String
///
public static string GetSlxConnString(string server, string connName, string userLogin, string userPassword, string rwPass)
{
StringBuilder Conn = new StringBuilder();
Conn.Append("Provider=SLXOLEDB.1ersist Security Info=False;");
if (!String.IsNullOrEmpty(server))
Conn.Append(String.Format("Data Source='{0}';", server));
else
Conn.Append("Data Source='';");
if (!String.IsNullOrEmpty(connName))
Conn.Append(String.Format("Initial Catalog='{0}';", connName));
else
Conn.Append("Initial Catalog=NO_ALIAS;");
if (!String.IsNullOrEmpty(userLogin))
Conn.Append(String.Format("User ID='{0}';", userLogin));
else
Conn.Append("User ID='';");
if (!String.IsNullOrEmpty(userPassword))
Conn.Append(String.Format("Password='{0}';", userPassword));
else
Conn.Append("Password='';");
Conn.Append("Extended Properties ='");
if (!String.IsNullOrEmpty(rwPass))
Conn.Append(String.Format("RWPass='{0}';", rwPass));
Conn.Append(@"PORT=1706;LOG=ON'");
return Conn.ToString();
}
} |
|
|
|