Thursday, June 12, 2008

Integration Manager 10 and the GPConnection Object and Properties

The GPConnection object replaces the instantiation previously made with the RetreiveGlobals DLL, used with previous versions of Microsoft Dynamics GP.
Open method

The Open method allows you to open an ADO connection using the current GP user login information. This method uses the data source that is in use when Microsoft Dynamics GP is open. If you want to use a default company database (TWO or GPDAT) for this method, then you must set the Open value in the connection string before using the Open method.

You will not be able to update the connection string after the Open method is called. There is no close method for this object. Once the connection is returned to the same way the connection object was initially created in the script, that connection object can be closed normally.

Syntax

object.Open({suppress})

Comments
All properties for the Open method will return string values.

Examples
The following example is the Open script. It opens the data connection.


set MyCon = CreateObject("ADODB.Connection")
MyCon.Connectionstring = "database=GPDAT"
GPConnection.Open(MyCon)

The following is an example of creating the ADO record set.


set recset = CreateObject("ADODB.Recordset")

The following is an example of creating the ADO connection.


set MyCon = CreateObject("ADODB.Connection")

The following is an example of executing the update command


recset = MyCon.Execute(updatecommand )

The following is an example of closing the ADO Connection.


MyCon.Close

The following are examples of retrieving the properties exposed by the new GPConnection object.


MsgBox GPConnection.GPConnUserDate
MsgBox GPConnection.GPConnInterCompanyID
MsgBox GPConnection.GPConnUserID
MsgBox GPConnection.GPConnUserName
MsgBox GPConnection.GPConnDataSource


UserDate property

The UserDate property contains the current user date.

Syntax

object.UserDate

CompanyID property

The CompanyID property contains the intercompany ID (company database ID).

Syntax

object.CompanyID

Example

The following is an example of initializing the connection string to specify a default
database. In this case it is set to the current company. This could be set to a constant
database, such as GPDAT.


MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID

UserID property

The UserID property contains the current User ID.

Syntax

object.UserID

UserName property

The UserName property contains the name of the current user.

Syntax

object.UserName

Example

The following is an example of creating a string to update the customer name in the customer master table.


updatecommand = "update RM00101 set [CUSTNAME]='IM Customer' where [CUSTNMBR]='AARONFIT0022'"

DataSource property

The DataSource property contains the name of the current data source that is being used in Microsoft Dynamics GP.

Syntax

object.DataSource

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

7 comments:

Grady said...

I have been working with the new GPConnection object. Once you execute a SQL command how do we reference the fields within the returned records. For example with the old methodolgy it was:
CurrentField = pRecordset.Fields(0).Value

But I continue to get errors, "object doesn't support this property or method." I am also having problems with MoveFirst. Can you point me to additional documentation on this new object, thanks.

Mariano Gomez said...

Grady,

Thanks for the inquiry! You will want to change your syntax as follows:

CurrentField.Value = pRecordset(0)

Keep in mind that pRecordset should have been previously defined as an ADODB.Recordset object.

Before you can call the MoveFirst method, you will need to make sure the recordset is not empty:

If not(pRecordSet.EOF) and not (pRecordset.BOF) Then
pRecordset.MoveFirst
Else
pRecordset.Close
End If

Keep in mind that this is just an assumption based on the limited information you provided. It may or may not be accurate based on your situation.

Best regards,

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

edwagner said...

I am trying to create a script to get the next Journal # in IM, eConnect with ADO and the new GPconnection I get the following error: DOC 1 ERROR: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I beleive the following sample code was authored by you;

set oCn = CreateObject("ADODB.Connection")
set oCmd = CreateObject("ADODB.Command")
oCn.ConnectionString = "Database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

Mariano Gomez said...

Ed,

Thanks for your inquiry. I am not sure about the specifics of your environment, so I really cannot assert why you are getting the error message. I tried the code you posted and got no errors (of course, I had to define the constant adUseClient).

Anonymous said...

Hi Mario,
I just upgraded GP9 to GP10. I have alot of scripts in my integration manager and I see that IM version no longer uses retrieve.global anymore. can you help me decipher this script to version 10?

Dim userinfo
Dim luserid
Dim lintercompanyid
Dim lsqldatasourcename
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()


SetVariable "lintercompanyid", lintercompanyid

Any help is greatly appreciated.

Thanks,
ARov

Mariano Gomez said...

set MyCon = CreateObject("ADODB.Connection")MyCon.Connectionstring = "database=" & GPConnection.GPConnInterCompanyID
GPConnection.Open(MyCon)

bubba said...

Hi
I have not used SQL in an integration before but I've looked at other code and tried to do it but keep getting errors. can you tell me where I'm going wrong, thanks!

field script:
Dim EMPID
Dim FILECODE

EMPID = SourceFields("_PR_DTL.F2")
FILECODE = SourceFields("_PR_DTL.F4")
sSQL = "if not exists (SELECT * FROM UPR00400 where PAYRCORD='REL AW' and UPR00400.EMPLOYID ='" & EMPID & "' ) SELECT 'HOURLY' ELSE SELECT 'REL AW'"
set MyCon = CreateObject("ADODB.Connection")
MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID
GPConnection.Open(MyCon)
PAYCODE = MyCon.Execute(sSQL)
Set MyCon = nothing

If FILECODE= "C" then
CurrentField= PAYCODE
ElseIf FILECODE = "W" then
CurrentField= "BUS"
Else
CurrentField= FILECODE
End If