7/7/2025 4:30:42 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
How to connect to SQL through the Architect and Execute a SQL Query
Posted: 28 Jun 10 8:23 AM
|
Could someone please give me an example of how to connect to SQL using vbscribt in Architect then also execute a query using vbscript? I did some searching prior to posting but the results I found were somewhat unclear as for what to do for the connection string and actually connecting.
Thanks,
Andrew |
|
|
|
Re: How to connect to SQL through the Architect and Execute a SQL Query
Posted: 28 Jun 10 9:38 AM
|
There are numerous examples up here (great articles by Ryan Farley), in the architect, etc.
Start with the SLX Database support included scripts for the general idea.....
the Sussman Book ADO 2.6 has the syntax for a connection string....
Here's what we use in Production:
'**************************************************************
' Name: SLXDatabaseSupport_GetNativeConnectionString
' Purpose: gets the Native SQL connection string from the current SLX database connection.
' Assumptions: uses Application.GlobalInfo.SLX_Native_Conn_String and SYSDBA password is masterkey,
' this may need to be changed!
' Effects:
' Inputs: none, this is an SLX system call.
' Returns: string with the ADO/ODBC Native SQL Server 2005 Connection value.
' Dev Notes:
' strCONN = SLXDatabaseSupport_GetNativeConnectionString
'**************************************************************
Function SLXDatabaseSupport_GetNativeConnectionString
DIM RS
DIM connstr
SLXDatabaseSupport_GetNativeConnectionString = ""
connstr = Application.GlobalInfo.SLX_Native_Conn_String
IF connstr = "" THEN ''' RJS 5/29/2009 Added pretest, do we really need to RE-get the string?
Set rs = Application.GetNewConnection.Execute("slx_getNativeConnInfo()")
' 04/29/2010 RJS pwd changed.
connstr = rs.Fields(0).Value & ";password=AARDVARK"
' ^ ' Application.GlobalInfo.Add "SLX_Native_Conn_String", connstr
rs.Close
SET RS = NOTHING
END IF
SLXDatabaseSupport_GetNativeConnectionString = connstr
END FUNCTION
'**************************************************************
' Name: GetNativeConnection
' Purpose: gets the Native SQL connection string from the current SLX database connection
' and opens up a Native SQL connection object.
' Assumptions: Calls SLXDatabaseSupport_GetNativeConnectionString which uses Application.GlobalInfo.SLX_Native_Conn_String and SYSDBA password is masterkey,
' this may need to be changed!
' Effects:
' Inputs: A connection object variable.
' Returns: That connection object instanciated and open for business.
' Dev Notes:
' GetNativeConnection mynativeconnectionobject
'**************************************************************
Sub SLXDatabaseSupport_GetNativeConnection(ByRef TheConn)
DIM strConn
' instantiate SQL Native Connection
Set TheConn = CreateObject("ADODB.Connection")
strConn = SLXDatabaseSupport_GetNativeConnectionString()
TheConn.OPEN strConn
END SUB
'**************************************************************
' Name: SLXDatabaseSupport_GetADORecordSet
' Purpose: creates an ADO recordset in readonly mode. Client Side Cursor.
' Assumptions: Calls GetNativeConnectionString which uses Application.GlobalInfo.SLX_Native_Conn_String and SYSDBA password is masterkey,
' this may need to be changed!
' Effects:
' Inputs: An ADO recordset object variable.
' Returns: An ADO recordset object variable. Read Only.
' Dev Notes:
' SLXDatabaseSupport_GetADORecordSet myRS
'**************************************************************
SUB SLXDatabaseSupport_GetADORecordSet (ByRef TheRS)
Set TheRS = CreateObject("ADODB.Recordset")
With TheRS
' ** For Developer Reference **
' adUseClient 3 Use client-side cursor supplied by the local cursor library
' adUseServer 2 Default, use the cursor supplied by provider or database
.CursorLocation = adUseClient
' adOpenStatic 3 Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
' If the CursorLocation property is set to adUseClient, the only valid setting for the CursorType property is adOpenStatic
.CursorType = adOpenStatic
' adLockBatchOptimistic 4 Multiple users can modify the data and the changes are cached until BatchUpdate is called
.LockType = adLockReadOnly ' read only.
End With
END SUB
Create a text file on your desktop. Rename the extension to .udl. double click on this, create your connection, and then copy the text to the SLX Architect. This is your connection string.....
loads of examples on the web, here's one:
http://msdn.microsoft.com/en-us/library/ms807027.aspx
Sub ConnectionExample6()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
cnn.Open
' Create a Recordset by executing an SQL statement.
Set rs = cnn.Execute("Select TOP 1 * From CONTACT")
' Show the first contact
MsgBox rs("LASTNAME") & ", " & rs("FIRSTNAME")
' Close the connection.
rs.Close
End Sub
|
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|