ODBC Connection to dump data frm PSoft to MS-SQL

[SoothSayer]

Hello frnds,
I am new here, and was really delighted to find a forum for PSoft.
By the way, I am developing a new customised module in PSoft 7.51 HRMS and facing a problem in my PSoft application and would appreciate if any1 would lend a helping hand.
What I am trying to do is ->

I have an application running on MS-SQL database (and ASP) and also there is PSoft 7.51 HRMS is fully functional, now I need to insert some data ( say someting like emails ) into a MS-SQL table, "when a Panel in Psoft is saved". In other words, I need to establish a ODBC connection to MS-SQL thru a DSN. I already have a working DSN and hence I tried to use methods like SQLConnect in PeopleCode on SavePostChng Event , but when I save the code, the editor says that "SQLConnect is not a valid Method", and simply does not allow me to establish a Connection.

Any1 have any ideas or ways to do this communication btewn MS-SQL and PeopleCode ?

Thanx,

 

[rekclaw]

I think you are probably looking at writing a .dll written in C to resolve this problem. You could write a stand alone application that you run with an Exec, but I've always felt that you end up with a little less flexibility in terms of communicating information back to the calling application. You could also use OLE automation to resolve the problem. I have done this one with a simple sample application (that I've probably discarded). The drawbacks to this idea are that you will need to install the ODBC driver and set up the DSN on each workstation. In addition, OLE automation only works in two tier mode. The .dll idea will work in three tier and even has some support for version 8 and working in four tier mode.

We have implemented calls to some C .dll's in our system for showing the contents of log files to our end users. The .dll's can also be implemented in both 2 and 3 tier modes.

If you are a seasoned C programmer this should be no big deal (I'm not a great C programmer). I would start by creating a simple shell application that you call from VB. I like using VB because it is quicker to test with than working through all of the panels in PeopleSoft. I have never really understood the data types of PeopleSoft numeric fields, so I pass everything to the dll as string. C tends to deal with that a little bit better. Also make sure that you are allocating memory space for all variables passed to the C function in PeopleCode (or VB for your development phase). And of course, once PeopleSoft loads the .dll into memory, it's there until you close that instance of the application.

If you are interested, I can send you a working shell to start with. It will be the program that we used for showing log files and the related PeopleCode. I've done the ODBC stuff in C .dll's, but from VB and not from PeopleCode. I have some opinions on this matter, but that's from a novice C programmer. I will leave it up to you to tackle that monster unless you are REALLY interested in my thoughts.

Good Luck

Rekclaw

 

[mrfreezie]

I am new to ODBC and peoplesoft.
How would I connect to an application server using sqlconnect?
We are trying to get a list of people from the server using sql statements through odbc..

this is what I have so far..
SqlConnect(m_hDBC,"DSN={PeopleSoft PeopleTools};DRIVER={PeopleSoft};",SQL_NTS,"username",SQL_NTS,"password",SQL_NTS);

I tried a few otherthings for the 2nd parameter but none seem to work....anyone have experience in this area?

 

[mrfreezie]

Sorry this is the example I followed:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odch21cpr_2.asp

and this is the error I get when calling connect:

[Microsoft][ODBC Driver Manager]Invalid String or Buffer Length

Any help is greatly appreciated....