/****************************************************************************
**
** Source File Name = lnaddrbk.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1999, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** IMPLEMENTATION :
**
** DB2NSFSearch
** DB2FTSearch
**
** PURPOSE :
**
** The two table functions read "Person" documents from Lotus Notes
** Address Book NSF databases. The table functions return ~50 fields
** from person documents as table function output columns.
**
** See lnaddrbk.db2 for DDL/DML and documentation of input parameters.
** See function headers for documentation of function implementations.
**
** To make the library:
** Drop any instances of the function that previously existed.
** Compile without linking.
** Link with the DB2 libraries and Lotus Notes C libraries.
** Copy the compiled library to the "function" directory of
** the DB2INSTANCE that the library will used with.
**
** Example on Win32 with Microsoft Visual C++ compiler:
** modify sqllib\samples\c\bldmudf.bat
** add "-DW32 /I notesapi\include" to cl compile options
** add "notesapi\lib\mswin32\notes.lib" to link command
** compile and build
** sqllib\samples\c\bldmudf lnaddrbk
**
** Example on AIX with IBM Cset++ compiler:
** modify sqllib/samples/c/bldxlcudf
** add "-DUNIX -DAIX -I notesapi/include" to xlc compile options
** add "notesapi/lib/aix/notes0.o lib/notes/libnotes_r.a" to link command
** compile and build
** bldxlcudf lnaddrbk DB2NSFSearch
**
**
** EXTERNAL DEPENDENCIES :
** - Lotus Notes C API from http:// www.lotus.com
** (code tested with Lotus Notes C API Release 4.6.2)
** - Lotus Notes installation
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
****************************************************************************/
#include "stdio.h"
#include "time.h"
#ifdef W32
#include <windows.h>
#endif
#include <global.h> /* Notes C API Database */
#include <osmisc.h> /* Notes C API Database */
#include <osmem.h> /* Notes C API Database */
#include <osfile.h> /* Notes C API Database */
#include <nsfnote.h> /* Notes C API Database */
#include <nsfdb.h> /* Notes C API Database */
#include <nsferr.h> /* Notes C API Database */
#include <nsfsearc.h> /* Notes C API Database */
#include <idtable.h> /* Notes C API Database */
#include <ft.h> /* NOTES C API Database */
#include <fterr.h> /* Notes C API Database */
#include <sqludf.h> /* DB2 UDF interface */
/********************************************************************************
Prototypes for helper functions (implementation at the end of this file)
********************************************************************************/
STATUS LNPUBLIC AddIDUnique (void *, SEARCH_MATCH *, ITEM_TABLE *);
STATUS ConvertTStoTD (SQLUDF_STAMP *pSince_ts, TIMEDATE *pSince_td);
void SQLReportError (STATUS rc, char *state, char *sqlstate, char *sqlmsg);
void NGetItem (NOTEHANDLE, char *, SQLUDF_VARCHAR *, SQLUDF_NULLIND *,
BOOL Deleted, WORD, char *, char*);
WORD ColStrlen (char *x);
void swmemcpy (char to[4], char from[4]);
/********************************************************************************
Scratchpad for Lotus Notes table functions
********************************************************************************/
struct db2scratchpad
{
DBHANDLE hDb; /* handle to Notes database */
HANDLE hIdTable; /* handle of the newly allocated ID Table */
BOOL fFirst; /* first note id in scan? */
NOTEID NoteId; /* current note id */
SQLUDF_VARCHAR **Col; /* array of columns */
SQLUDF_NULLIND **ColInd; /* array of columns NULL indicators */
WORD *ColLen; /* array of column length */
char **ColItem; /* array of column names */
char not_used[SQLUDF_SCRATCHPAD_LEN
-sizeof(DBHANDLE)
-sizeof(HANDLE)
-sizeof(BOOL)
-sizeof(NOTEID)
-sizeof(SQLUDF_VARCHAR*)
-sizeof(SQLUDF_NULLIND*)
-sizeof(WORD*)
-sizeof(char*)];
};
/********************************************************************************
Macros
********************************************************************************/
#define SQL_STAMP_STRLEN 26
/* Number of table function result columns */
/* Ordinal numbers of columns are 1-based */
/* for projection push down */
#define NBRRESULTCOLUMN 51+1
#define UNIDCID 1
/* Map items from Notes document to result columns */
#define AssignColumnsToItems \
Col [UNIDCID] = Col1 ; \
ColInd [UNIDCID] = ColInd1; \
ColItem[UNIDCID] = "UNID"; \
ColItem[2 ] = "Title" ; Col[2 ] = Col2 ; ColInd[2 ] = ColInd2 ; \
ColItem[3 ] = "FirstName" ; Col[3 ] = Col3 ; ColInd[3 ] = ColInd3 ; \
ColItem[4 ] = "MiddleInitial" ; Col[4 ] = Col4 ; ColInd[4 ] = ColInd4 ; \
ColItem[5 ] = "LastName" ; Col[5 ] = Col5 ; ColInd[5 ] = ColInd5 ; \
ColItem[6 ] = "Suffix" ; Col[6 ] = Col6 ; ColInd[6 ] = ColInd6 ; \
ColItem[7 ] = "JobTitle" ; Col[7 ] = Col7 ; ColInd[7 ] = ColInd7 ; \
ColItem[8 ] = "CompanyName" ; Col[8 ] = Col8 ; ColInd[8 ] = ColInd8 ; \
ColItem[9 ] = "BusinessAddress" ; Col[9 ] = Col9 ; ColInd[9 ] = ColInd9 ; \
ColItem[10] = "OfficeZIP" ; Col[10] = Col10; ColInd[10] = ColInd10; \
ColItem[11] = "OfficeCountry" ; Col[11] = Col11; ColInd[11] = ColInd11; \
ColItem[12] = "OfficeStreetAddress" ; Col[12] = Col12; ColInd[12] = ColInd12; \
ColItem[13] = "OfficeCity" ; Col[13] = Col13; ColInd[13] = ColInd13; \
ColItem[14] = "OfficeState" ; Col[14] = Col14; ColInd[14] = ColInd14; \
ColItem[15] = "StreetAddress" ; Col[15] = Col15; ColInd[15] = ColInd15; \
ColItem[16] = "City" ; Col[16] = Col16; ColInd[16] = ColInd16; \
ColItem[17] = "State" ; Col[17] = Col17; ColInd[17] = ColInd17; \
ColItem[18] = "AreaCodeFromLoc" ; Col[18] = Col18; ColInd[18] = ColInd18; \
ColItem[19] = "OfficePhoneNumber" ; Col[19] = Col19; ColInd[19] = ColInd19; \
ColItem[20] = "OfficeFAXPhoneNumber"; Col[20] = Col20; ColInd[20] = ColInd20; \
ColItem[21] = "CellPhoneNumber" ; Col[21] = Col21; ColInd[21] = ColInd21; \
ColItem[22] = "PhoneNumber" ; Col[22] = Col22; ColInd[22] = ColInd22; \
ColItem[23] = "HomeFAXPhoneNumber" ; Col[23] = Col23; ColInd[23] = ColInd23; \
ColItem[24] = "PhoneNumber_6" ; Col[24] = Col24; ColInd[24] = ColInd24; \
ColItem[25] = "MailAddress" ; Col[25] = Col25; ColInd[25] = ColInd25; \
ColItem[26] = "PhoneLabel_1" ; Col[26] = Col26; ColInd[26] = ColInd26; \
ColItem[27] = "PhoneLabel_3" ; Col[27] = Col27; ColInd[27] = ColInd27; \
ColItem[28] = "PhoneLabel_5" ; Col[28] = Col28; ColInd[28] = ColInd28; \
ColItem[29] = "PhoneLabel_2" ; Col[29] = Col29; ColInd[29] = ColInd29; \
ColItem[30] = "PhoneLabel_4" ; Col[30] = Col30; ColInd[30] = ColInd30; \
ColItem[31] = "PhoneLabel_6" ; Col[31] = Col31; ColInd[31] = ColInd31; \
ColItem[32] = "WebSite" ; Col[32] = Col32; ColInd[32] = ColInd32; \
ColItem[33] = "HomeAddress" ; Col[33] = Col33; ColInd[33] = ColInd33; \
ColItem[34] = "DisplayHomeAddress" ; Col[34] = Col34; ColInd[34] = ColInd34; \
ColItem[35] = "Zip" ; Col[35] = Col35; ColInd[35] = ColInd35; \
ColItem[36] = "country" ; Col[36] = Col36; ColInd[36] = ColInd36; \
ColItem[37] = "Categories" ; Col[37] = Col37; ColInd[37] = ColInd37; \
ColItem[38] = "Location" ; Col[38] = Col38; ColInd[38] = ColInd38; \
ColItem[39] = "Department" ; Col[39] = Col39; ColInd[39] = ColInd39; \
ColItem[40] = "Manager" ; Col[40] = Col40; ColInd[40] = ColInd40; \
ColItem[41] = "Assistant" ; Col[41] = Col41; ColInd[41] = ColInd41; \
ColItem[42] = "Spouse" ; Col[42] = Col42; ColInd[42] = ColInd42; \
ColItem[43] = "Children" ; Col[43] = Col43; ColInd[43] = ColInd43; \
ColItem[44] = "FullName" ; Col[44] = Col44; ColInd[44] = ColInd44; \
ColItem[45] = "MailDomain" ; Col[45] = Col45; ColInd[45] = ColInd45; \
ColItem[46] = "MailSystem" ; Col[46] = Col46; ColInd[46] = ColInd46; \
ColItem[47] = "ShortName" ; Col[47] = Col47; ColInd[47] = ColInd47; \
ColItem[48] = "Type" ; Col[48] = Col48; ColInd[48] = ColInd48; \
ColItem[49] = "DocumentAccess" ; Col[49] = Col49; ColInd[49] = ColInd49; \
ColItem[50] = "UpdatedBy" ; Col[50] = Col50; ColInd[50] = ColInd50; \
ColItem[51] = "Revisions" ; Col[51] = Col51; ColInd[51] = ColInd51;
/* Declaration of table function result columns */
#define AllCols \
SQLUDF_VARCHAR *Col1 , \
SQLUDF_VARCHAR *Col2 , \
SQLUDF_VARCHAR *Col3 , \
SQLUDF_VARCHAR *Col4 , \
SQLUDF_VARCHAR *Col5 , \
SQLUDF_VARCHAR *Col6 , \
SQLUDF_VARCHAR *Col7 , \
SQLUDF_VARCHAR *Col8 , \
SQLUDF_VARCHAR *Col9 , \
SQLUDF_VARCHAR *Col10, \
SQLUDF_VARCHAR *Col11, \
SQLUDF_VARCHAR *Col12, \
SQLUDF_VARCHAR *Col13, \
SQLUDF_VARCHAR *Col14, \
SQLUDF_VARCHAR *Col15, \
SQLUDF_VARCHAR *Col16, \
SQLUDF_VARCHAR *Col17, \
SQLUDF_VARCHAR *Col18, \
SQLUDF_VARCHAR *Col19, \
SQLUDF_VARCHAR *Col20, \
SQLUDF_VARCHAR *Col21, \
SQLUDF_VARCHAR *Col22, \
SQLUDF_VARCHAR *Col23, \
SQLUDF_VARCHAR *Col24, \
SQLUDF_VARCHAR *Col25, \
SQLUDF_VARCHAR *Col26, \
SQLUDF_VARCHAR *Col27, \
SQLUDF_VARCHAR *Col28, \
SQLUDF_VARCHAR *Col29, \
SQLUDF_VARCHAR *Col30, \
SQLUDF_VARCHAR *Col31, \
SQLUDF_VARCHAR *Col32, \
SQLUDF_VARCHAR *Col33, \
SQLUDF_VARCHAR *Col34, \
SQLUDF_VARCHAR *Col35, \
SQLUDF_VARCHAR *Col36, \
SQLUDF_VARCHAR *Col37, \
SQLUDF_VARCHAR *Col38, \
SQLUDF_VARCHAR *Col39, \
SQLUDF_VARCHAR *Col40, \
SQLUDF_VARCHAR *Col41, \
SQLUDF_VARCHAR *Col42, \
SQLUDF_VARCHAR *Col43, \
SQLUDF_VARCHAR *Col44, \
SQLUDF_VARCHAR *Col45, \
SQLUDF_VARCHAR *Col46, \
SQLUDF_VARCHAR *Col47, \
SQLUDF_VARCHAR *Col48, \
SQLUDF_VARCHAR *Col49, \
SQLUDF_VARCHAR *Col50, \
SQLUDF_VARCHAR *Col51
/* Declaration of null indicators for table function result columns */
#define AllColInds \
SQLUDF_NULLIND *ColInd1 , \
SQLUDF_NULLIND *ColInd2 , \
SQLUDF_NULLIND *ColInd3 , \
SQLUDF_NULLIND *ColInd4 , \
SQLUDF_NULLIND *ColInd5 , \
SQLUDF_NULLIND *ColInd6 , \
SQLUDF_NULLIND *ColInd7 , \
SQLUDF_NULLIND *ColInd8 , \
SQLUDF_NULLIND *ColInd9 , \
SQLUDF_NULLIND *ColInd10, \
SQLUDF_NULLIND *ColInd11, \
SQLUDF_NULLIND *ColInd12, \
SQLUDF_NULLIND *ColInd13, \
SQLUDF_NULLIND *ColInd14, \
SQLUDF_NULLIND *ColInd15, \
SQLUDF_NULLIND *ColInd16, \
SQLUDF_NULLIND *ColInd17, \
SQLUDF_NULLIND *ColInd18, \
SQLUDF_NULLIND *ColInd19, \
SQLUDF_NULLIND *ColInd20, \
SQLUDF_NULLIND *ColInd21, \
SQLUDF_NULLIND *ColInd22, \
SQLUDF_NULLIND *ColInd23, \
SQLUDF_NULLIND *ColInd24, \
SQLUDF_NULLIND *ColInd25, \
SQLUDF_NULLIND *ColInd26, \
SQLUDF_NULLIND *ColInd27, \
SQLUDF_NULLIND *ColInd28, \
SQLUDF_NULLIND *ColInd29, \
SQLUDF_NULLIND *ColInd30, \
SQLUDF_NULLIND *ColInd31, \
SQLUDF_NULLIND *ColInd32, \
SQLUDF_NULLIND *ColInd33, \
SQLUDF_NULLIND *ColInd34, \
SQLUDF_NULLIND *ColInd35, \
SQLUDF_NULLIND *ColInd36, \
SQLUDF_NULLIND *ColInd37, \
SQLUDF_NULLIND *ColInd38, \
SQLUDF_NULLIND *ColInd39, \
SQLUDF_NULLIND *ColInd40, \
SQLUDF_NULLIND *ColInd41, \
SQLUDF_NULLIND *ColInd42, \
SQLUDF_NULLIND *ColInd43, \
SQLUDF_NULLIND *ColInd44, \
SQLUDF_NULLIND *ColInd45, \
SQLUDF_NULLIND *ColInd46, \
SQLUDF_NULLIND *ColInd47, \
SQLUDF_NULLIND *ColInd48, \
SQLUDF_NULLIND *ColInd49, \
SQLUDF_NULLIND *ColInd50, \
SQLUDF_NULLIND *ColInd51
/********************************************************************************
Function DB2NSFSearch
Purpose: See lnaddrbk.db2 for usage and documentation of input/output
parameters.
This function opens a local or remote Lotus Notes addressbook, and
returns fields of "Person" notes in the database. If "Since" is
specified, it returns DELETED notes as well with UNID and NULL values
for items. It compiles "Person" formula, and uses NSFSearch with a
AddIDUnique call back function to build an IDTable of
notes. Subsequent FETCH calls scan through the IDTable and returns the
fields of the qualified notes. It returns only the requested items as
specificed in DBINFO.
DB2NSFSearch uses Lotus Notes NSFSearch and Idtable.
The function determines the max length of the return columns and
copies max length of field values to return columns. In case of data
truncation, the function raises a data truncation warning with SQL
state 01H00, and field name and size of truncated data in the message
text.
The function returns 3860x SQL states with Lotus Notes error
messages.
********************************************************************************/
void DB2NSFSearch (SQLUDF_VARCHAR *in_ServerName,
SQLUDF_VARCHAR *in_NsfDb,
SQLUDF_STAMP *in_Since,
AllCols,
SQLUDF_NULLIND *in_ServerName_ind,
SQLUDF_NULLIND *in_NsfDb_ind,
SQLUDF_NULLIND *in_Since_ind,
AllColInds,
SQLUDF_TRAIL_ARGS_ALL,
SQLUDF_DBINFO *sqludf_dbinfo)
{
NOTEHANDLE hNote; /* Handle to note */
OID retNoteOID; /* returned originator ID */
STATUS rc = NOERROR; /* API return code */
BOOL Del = FALSE; /* Note deleted? */
TIMEDATE retModified; /* modified timedate */
WORD retNoteClass; /* note class */
int i;
/* Retrieve DB2 scratchpad */
struct db2scratchpad *spad = (struct db2scratchpad *) (SQLUDF_SCRAT->data);
SQLUDF_VARCHAR **Col = spad->Col;
SQLUDF_NULLIND **ColInd = spad->ColInd;
WORD *ColLen = spad->ColLen;
char **ColItem = spad->ColItem;
switch (SQLUDF_CALLT)
{
case SQLUDF_TF_FIRST:
{
/* For Debugging on NT uncomment the following line */
/* MessageBox (HWND_DESKTOP, "FIRST", "DB2NSFSearch", MB_OK|MB_APPLMODAL); */
/* Initialize Notes environment */
if (rc = NotesInitExtended (0, 0))
{
SQLReportError (rc, "38600", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
/* Setup for result columns */
spad->Col = (SQLUDF_VARCHAR **) malloc (sizeof (void *) * NBRRESULTCOLUMN);
spad->ColInd = (SQLUDF_NULLIND **) malloc (sizeof (void *) * NBRRESULTCOLUMN);
spad->ColLen = (WORD *) malloc (sizeof (WORD) * NBRRESULTCOLUMN);
spad->ColItem = (char **) malloc (sizeof (char *) * NBRRESULTCOLUMN);
Col = spad->Col;
ColInd = spad->ColInd;
ColLen = spad->ColLen;
ColItem = spad->ColItem;
AssignColumnsToItems;
for (i = 1; i < NBRRESULTCOLUMN; i++)
ColLen[i] = ColStrlen (Col[i]);
break;
} /* FIRST */
case SQLUDF_TF_OPEN:
{
TIMEDATE Since_td; /* Since TIMEDATE */
TIMEDATE *pSince_td; /* pointer to Since TIMEDATE */
TIMEDATE retDataModified; /* DB last-mod date of all data notes */
TIMEDATE retNonDataModified; /* DB last-mod date of all non-data notes */
FORMULAHANDLE fhandle; /* formula handle */
WORD wdc; /* word compile */
char retPathName[MAXPATH]; /* NSF path name */
char formula[] = "SELECT Type = 'Person'";
/* Filter by date? */
if (*in_Since_ind == -1)
{
/* NULL to not filter by date. */
pSince_td = NULL;
}
else
{
/* convert in_Since to Since_td (Lotus Notes TIMEDATE); */
if (rc = ConvertTStoTD (in_Since, &Since_td))
{
SQLReportError (rc, "38601", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
pSince_td = &Since_td;
}
/* Notes database on server? */
if (*in_ServerName_ind == -1)
{
/* Require expanded path name */
strcpy (retPathName, in_NsfDb);
}
else
{
/* Require canonical path name relative to Notes data directory */
if (rc = OSPathNetConstruct(NULL, /* PortName */
in_ServerName, /* ServerName */
in_NsfDb, /* FileName */
retPathName))
{
SQLReportError (rc, "38602", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
}
/* Open NSF database */
rc = NSFDbOpenExtended (retPathName,
0, /* options */
NULLHANDLE, /* internal use */
pSince_td, /* ModifiedTime */
&(spad->hDb), /* out: DBHANDLE */
&retDataModified, /* out: */
&retNonDataModified); /* out: */
if (rc == ERR_NO_MODIFIED_NOTES)
{
/* no modified notes */
spad->hIdTable = NULLHANDLE;
break;
}
else if (rc)
{
SQLReportError (rc, "38603", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
/* Create ID table for call to NSFSearch. */
if (rc = IDCreateTable(sizeof(NOTEID), &(spad->hIdTable)))
{
SQLReportError (rc, "38604", SQLUDF_STATE, SQLUDF_MSGTX);
NSFDbClose (spad->hDb);
break;
}
/* Compile selection formula. */
if (rc = NSFFormulaCompile (NULL, /* name of formula (none) */
(WORD) 0, /* length of name */
formula, /* the ASCII formula */
(WORD) strlen(formula), /* length */
&fhandle, /* handle to compiled formula */
&wdc, /* compiled formula length */
&wdc, /* return code from compile */
&wdc, &wdc, &wdc, &wdc)) /* compile error info */
{
SQLReportError (rc, "38605", SQLUDF_STATE, SQLUDF_MSGTX);
IDDestroyTable(spad->hIdTable);
NSFDbClose (spad->hDb);
break;
}
/* Scan all the notes in a database restricted by selection */
/* formula and modification date. NSFSearch will find all data */
/* notes and call the action routine, AddIDUnique, on */
/* each. AddIDUnique adds the ID of the data note to the ID */
/* table if and only if the ID is not already in the table. */
/* The result is a table of Note IDs where each ID is */
/* guaranteed to appear only once. */
/* NOTE: If the Since argument is specified, the flag */
/* SEARCH_ALL_VERSIONS will be automatically enabled, which */
/* returns deleted notes and non-matching notes modified since */
/* the specified date. In this situation, you must check the */
/* "MatchesFormula" field of the SEARCH_MATCH structure */
/* (passed to the action routine) for the value SE_FMATCH in */
/* order to determine if the note is a matching note. */
rc = NSFSearch (spad->hDb, /* database handle */
fhandle, /* selection formula */
NULL, /* title of view in selection formula */
0, /* search flags */
NOTE_CLASS_DATA, /* note class to find */
pSince_td, /* starting date */
AddIDUnique, /* call for each note found */
&(spad->hIdTable), /* argument to AddIDUnique */
NULL); /* returned ending date */
/* Free memory allocated for compiled formula */
OSMemFree (fhandle);
if (rc)
{
SQLReportError (rc, "38606", SQLUDF_STATE, SQLUDF_MSGTX);
IDDestroyTable(spad->hIdTable);
NSFDbClose (spad->hDb);
break;
}
/* Lock ID table */
if (spad->hIdTable != NULLHANDLE)
{
OSLock (void, spad->hIdTable);
};
/* Initialization for subsequent IDScan */
spad->fFirst = TRUE;
break;
} /* OPEN */
case SQLUDF_TF_FETCH:
{
/* Access the next note ID (i.e., fFirst = FALSE). NoteId */
/* must contain the note ID most recently returned by IDScan. */
if ((spad->hIdTable == NULLHANDLE) ||
(IDScan(spad->hIdTable, /* ID table */
spad->fFirst, /* First ? */
&(spad->NoteId)) == FALSE))
{
strcpy( SQLUDF_STATE, "02000");
break;
}
else
{
/* IDScan successful */
spad->fFirst = FALSE;
/* Interested in any return columns, or just: */
/* select count(*) from ... */
if (sqludf_dbinfo->numtfcol == 0)
{
/* No items requested. Do not do anything. Just loop through. */
;
}
else
{
/* Is Note deleted? */
if (RRV_DELETED & spad->NoteId)
{
Del = TRUE;
}
else
{
Del = FALSE;
/* Read note into memory and return handle to in-memory copy */
if (rc = NSFNoteOpen (spad->hDb, /* NSF file handle */
spad->NoteId, /* Note ID */
OPEN_NOOBJECTS, /* open flags */
&hNote)) /* out: note handle*/
{
SQLReportError (rc, "38608", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
}
/* Get requested items from Note */
for (i = 0; i < sqludf_dbinfo->numtfcol; i ++)
{
if (sqludf_dbinfo->tfcolumn[i] != UNIDCID)
{
/* Retrieve Item. If note is deleted or item is */
/* not present, set null indicator. If item value */
/* is too long, raise truncation warning. */
NGetItem (hNote,
ColItem[sqludf_dbinfo->tfcolumn[i]],
Col[sqludf_dbinfo->tfcolumn[i]],
ColInd[sqludf_dbinfo->tfcolumn[i]],
Del,
ColLen[sqludf_dbinfo->tfcolumn[i]],
SQLUDF_STATE,
SQLUDF_MSGTX);
}
else
{
/* Retrieve UNID from note. This works for deleted */
/* notes and notes that do qualify for compiled */
/* notes formula in NSFSearch. */
if (rc = NSFDbGetNoteInfo(spad->hDb, /* DBHANDLE */
spad->NoteId, /* NOTEID */
&retNoteOID, /* out: OID */
&retModified, /* out: */
&retNoteClass)) /* out: */
{
SQLReportError (rc, "38607", SQLUDF_STATE, SQLUDF_MSGTX);
i = sqludf_dbinfo->numtfcol;
}
else
{
#ifdef BIG_ENDIAN_ORDER
memcpy (Col[UNIDCID], &retNoteOID, sizeof (UNID));
#else
swmemcpy (&(Col[UNIDCID][0]), (char*) &retNoteOID.File.Innards[1]);
swmemcpy (&(Col[UNIDCID][4]), (char*) &retNoteOID.File.Innards[0]);
swmemcpy (&(Col[UNIDCID][8]), (char*) &retNoteOID.Note.Innards[1]);
swmemcpy (&(Col[UNIDCID][12]),(char*) &retNoteOID.Note.Innards[0]);
#endif
}
} /* UNID */
}; /* for all requested items */
/* Deallocate the memory associated with an open note */
if (!Del)
NSFNoteClose (hNote);
}; /* Note items are reuested */
} /* IDScan successful */
break;
} /* FETCH */
case SQLUDF_TF_CLOSE:
{
/* Release ID table */
if (spad->hIdTable != NULLHANDLE)
{
OSUnlock (spad->hIdTable);
IDDestroyTable (spad->hIdTable);
}
/* Close NSF Database */
rc = NSFDbClose (spad->hDb);
break;
} /* CLOSE */
case SQLUDF_TF_FINAL:
{
/* Shut down Notes runtime environment */
NotesTerm();
/* Free setup for result columns */
free (spad->Col);
free (spad->ColInd);
free (spad->ColLen);
free (spad->ColItem);
} /* FINAL */
} /* switch SQLUDF_CALLT */
} /* DB2NSFSearch */
/********************************************************************************
Function DB2FTSearch
Purpose: See lnaddrbk.db2 for usage and documentation of input/output
parameters.
This function opens a local or remote Lotus Notes addressbook,
performs Lotus Notes full text search query and returns fields of
"Person" notes in the database. It returns only the requested items
as specificed in DBINFO.
DB2FTSearch uses Lotus Notes FTSearch and IdTables.
The function determines the max length of the return columns and
copies max length of field values to return columns. In case of data
truncation, the function raises a data truncation warning with SQL
state 01H00, and field name and size of truncated data in the message
text.
The function returns 3860x SQL states with Lotus Notes error
messages.
********************************************************************************/
void DB2FTSearch (SQLUDF_VARCHAR *in_ServerName,
SQLUDF_VARCHAR *in_NsfDb,
SQLUDF_VARCHAR *in_FTQuery,
AllCols,
SQLUDF_NULLIND *in_ServerName_ind,
SQLUDF_NULLIND *in_NsfDb_ind,
SQLUDF_NULLIND *in_FTQuery_ind,
AllColInds,
SQLUDF_TRAIL_ARGS_ALL,
SQLUDF_DBINFO *sqludf_dbinfo)
{
NOTEHANDLE hNote; /* Handle to note */
OID retNoteOID; /* returned originator ID */
STATUS rc = NOERROR; /* API return code */
char FormName[7]; /* name of form */
TIMEDATE retModified; /* modified timedate */
WORD retNoteClass; /* note class */
int i;
/* Retrieve DB2 scratchpad */
struct db2scratchpad *spad = (struct db2scratchpad *) (SQLUDF_SCRAT->data);
SQLUDF_VARCHAR **Col = spad->Col;
SQLUDF_NULLIND **ColInd = spad->ColInd;
WORD *ColLen = spad->ColLen;
char **ColItem = spad->ColItem;
switch (SQLUDF_CALLT)
{
case SQLUDF_TF_FIRST:
{
/* For Debugging on NT uncomment the following line */
/* MessageBox (HWND_DESKTOP, "FIRST", "DB2NSFSearch", MB_OK|MB_APPLMODAL); */
/* Initialize Notes environment */
if (rc = NotesInitExtended (0, 0))
{
SQLReportError (rc, "38600", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
/* Setup for result columns */
spad->Col = (SQLUDF_VARCHAR **) malloc (sizeof (void *) * NBRRESULTCOLUMN);
spad->ColInd = (SQLUDF_NULLIND **) malloc (sizeof (void *) * NBRRESULTCOLUMN);
spad->ColLen = (WORD *) malloc (sizeof (WORD) * NBRRESULTCOLUMN);
spad->ColItem = (char **) malloc (sizeof (char *) * NBRRESULTCOLUMN);
Col = spad->Col;
ColInd = spad->ColInd;
ColLen = spad->ColLen;
ColItem = spad->ColItem;
AssignColumnsToItems;
for (i = 1; i < NBRRESULTCOLUMN; i++)
ColLen[i] = ColStrlen (Col[i]);
break;
} /* FIRST */
case SQLUDF_TF_OPEN:
{
HANDLE hSearch; /* FT search handle */
DWORD dwRetDocs; /* returned documents */
TIMEDATE retDataModified; /* DB last-mod date of all data notes */
TIMEDATE retNonDataModified; /* DB last-mod date of all non-data notes*/
char retPathName[MAXPATH]; /* NSF path name */
/* Notes database on server? */
if (*in_ServerName_ind == -1)
{
/* Require expanded path name */
strcpy (retPathName, in_NsfDb);
}
else
{
/* Require canonical path name relative to Notes data directory */
if (rc = OSPathNetConstruct(NULL, /* PortName */
in_ServerName, /* ServerName */
in_NsfDb, /* FileName */
retPathName)) /* out: */
{
SQLReportError (rc, "38601", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
}
/* Open NSF database */
if (rc = NSFDbOpenExtended (retPathName,
0, /* options */
NULLHANDLE, /* internal use */
NULL, /* ModifiedTime */
&(spad->hDb), /* out: DBHANDLE*/
&retDataModified, /* out: */
&retNonDataModified)) /* out: */
{
SQLReportError (rc, "38602", SQLUDF_STATE, SQLUDF_MSGTX);
break;
}
/* Open search handle */
if (rc = FTOpenSearch(&hSearch))
{
SQLReportError (rc, "38603", SQLUDF_STATE, SQLUDF_MSGTX);
NSFDbClose (spad->hDb);
break;
}
if (*in_FTQuery_ind == -1)
in_FTQuery = NULL;
/* Do FT query search */
rc = FTSearch (spad->hDb, /* DB handle */
&hSearch, /* FT search handle */
NULL, /* all docs, no view */
in_FTQuery, /* FT query string */
FT_SEARCH_STEM_WORDS | /* OPTIONS */
FT_SEARCH_RET_IDTABLE, /* */
0, /* unlimited result */
NULLHANDLE, /* no refining IDTABLE */
&dwRetDocs, /* returned number of docs*/
NULL, /* reserved */
&(spad->hIdTable)); /* returned info */
if (rc == ERR_FT_NOMATCHES)
{
spad->hIdTable = NULLHANDLE;
}
else if (rc)
{
SQLReportError (rc, "38604", SQLUDF_STATE, SQLUDF_MSGTX);
FTCloseSearch (hSearch);
NSFDbClose (spad->hDb);
break;
}
/* Deallocate memory associated with search */
FTCloseSearch (hSearch);
/* Lock ID table */
OSLock (FT_SEARCH_RESULTS, spad->hIdTable);
/* Initialization for subsequent IDScan */
spad->fFirst = TRUE;
break;
} /* OPEN */
case SQLUDF_TF_FETCH:
{
/* Accessing the next note ID (i.e., fFirst = FALSE), *NoteId */
/* must contain the note ID most recently returned by IDScan. */
nextdoc:
if ((spad->hIdTable == NULLHANDLE) ||
(IDScan(spad->hIdTable, /* ID table */
spad->fFirst, /* First ? */
&(spad->NoteId)) == FALSE))
{
strcpy( SQLUDF_STATE, "02000");
break;
} /* IDScan FALSE */
else
{ /* IDScan successful */
spad->fFirst = FALSE;
/* Is Note deleted? */
rc = NSFNoteOpen (spad->hDb, /* NSF file handle */
spad->NoteId, /* Note ID */
OPEN_NOOBJECTS, /* open flags */
&hNote); /* out: note handle */
if (rc)
{
goto nextdoc;
}
/* Check on "Person" form name */
NSFItemGetText (hNote, /* note handle */
"Form", /* item name */
FormName, /* entr text */
7); /* text len */
if (strcmp (FormName, "Person"))
{
NSFNoteClose (hNote);
goto nextdoc;
}
/* Interested in any return columns, or just: */
/* select count(*) from ... */
if (sqludf_dbinfo->numtfcol == 0)
{
/* No items requested. Do not do anything. Just loop through. */
;
}
else
{
/* Get requested items from Note */
for (i = 0; i < sqludf_dbinfo->numtfcol; i ++)
{
if (sqludf_dbinfo->tfcolumn[i] != UNIDCID)
{
/* Retrieve Item. If item is */
/* not present, set null indicator. If item value */
/* is too long, raise truncation warning. */
NGetItem (hNote,
ColItem[sqludf_dbinfo->tfcolumn[i]],
Col[sqludf_dbinfo->tfcolumn[i]],
ColInd[sqludf_dbinfo->tfcolumn[i]],
FALSE,
ColLen[sqludf_dbinfo->tfcolumn[i]],
SQLUDF_STATE,
SQLUDF_MSGTX);
}
else
{
/* Retrieve UNID from note. This works for deleted */
/* notes and notes that do qualify for compiled */
/* notes formula in NSFSearch. */
if (rc = NSFDbGetNoteInfo(spad->hDb, /* DBHANDLE */
spad->NoteId, /* NOTEID */
&retNoteOID, /* out: OID */
&retModified, /* out: */
&retNoteClass)) /* out: */
{
SQLReportError (rc, "38607", SQLUDF_STATE, SQLUDF_MSGTX);
i = sqludf_dbinfo->numtfcol;
}
else
{
#ifdef BIG_ENDIAN_ORDER
memcpy (Col[UNIDCID], &retNoteOID, sizeof (UNID));
#else
swmemcpy (&(Col[UNIDCID][0]), (char*) &retNoteOID.File.Innards[1]);
swmemcpy (&(Col[UNIDCID][4]), (char*) &retNoteOID.File.Innards[0]);
swmemcpy (&(Col[UNIDCID][8]), (char*) &retNoteOID.Note.Innards[1]);
swmemcpy (&(Col[UNIDCID][12]),(char*) &retNoteOID.Note.Innards[0]);
#endif
}
} /* UNID */
}; /* for all requested items */
}; /* Note items are reuested */
/* Deallocate the memory associated with an open note */
NSFNoteClose (hNote);
} /* IDScan successful */
break;
} /* FETCH */
case SQLUDF_TF_CLOSE:
{
/* Release ID table */
if (spad->hIdTable != NULLHANDLE)
{
OSUnlock (spad->hIdTable);
IDDestroyTable (spad->hIdTable);
}
/* Close NSF Database */
rc = NSFDbClose (spad->hDb);
break;
} /* CLOSE */
case SQLUDF_TF_FINAL:
{
/* Shut down Notes runtime environment */
NotesTerm();
/* Free column length */
free (spad->Col);
free (spad->ColInd);
free (spad->ColLen);
free (spad->ColItem);
} /* FINAL */
} /* switch SQLUDF_CALLT */
} /* DB2FTSearch */
/********************************************************************************
Function AddIDUnique
NSFSearch callback function. This function adds NoteIDs to the IdTable
if the table does not already contain that ID.
********************************************************************************/
STATUS LNPUBLIC AddIDUnique (void *phNoteIDTable,
SEARCH_MATCH *pSearchMatch,
ITEM_TABLE *summary_info)
{
SEARCH_MATCH SearchMatch;
HANDLE hNoteIDTable;
STATUS rc;
BOOL flagOK;
memcpy( (char*)&SearchMatch, (char*)pSearchMatch, sizeof(SEARCH_MATCH));
hNoteIDTable = *((HANDLE *)phNoteIDTable);
/* NOTE: If the Since argument in NSFSearch is specified, the flag */
/* SEARCH_ALL_VERSIONS will be automatically enabled, which returns */
/* deleted notes and non-matching notes modified since the specified */
/* date. In this situation, you must check the "MatchesFormula" */
/* field of the SEARCH_MATCH structure (passed to the action */
/* routine) for the value SE_FMATCH in order to determine if the */
/* note is a matching note. */
if ((RRV_DELETED & SearchMatch.ID.NoteID) ||
(SearchMatch.SERetFlags & SE_FMATCH) )
{
/* Insert ID into the ID Table only if the table does not already */
/* contain that ID. flagOK == TRUE indicates insertion, flagOK == */
/* FALSE indicates already inserted. */
rc = IDInsert(hNoteIDTable, SearchMatch.ID.NoteID, &flagOK);
if (rc)
return (ERR(rc));
}
return (NOERROR);
}
/********************************************************************************
Function ConvertTStoTD
This function converts SQL TIMESTAMP to Lotus Notes TIMEDATE. It uses
"strftime" to format a date/time string appropriate for locale.
********************************************************************************/
STATUS ConvertTStoTD (SQLUDF_STAMP *pSince_ts, TIMEDATE *pSince_td)
{
char Since_locale[SQL_STAMP_STRLEN];
char *pSince_locale;
struct tm Since_tm;
STATUS rc;
Since_tm.tm_year = strtol (&pSince_ts[0], NULL, 10) - 1900;
Since_tm.tm_mon = strtol (&pSince_ts[5], NULL, 10) - 1;
Since_tm.tm_mday = strtol (&pSince_ts[8], NULL, 10);
Since_tm.tm_hour = strtol (&pSince_ts[11], NULL, 10);
Since_tm.tm_min = strtol (&pSince_ts[14], NULL, 10);
Since_tm.tm_sec = strtol (&pSince_ts[17], NULL, 10);
/* Format a time string; "%c" means date and time representation */
/* appropriate for locale. */
strftime (Since_locale, SQL_STAMP_STRLEN, "%c", &Since_tm);
pSince_locale = Since_locale;
rc = ConvertTextToTIMEDATE (NULL, /* IntlFormat */
NULL, /* TextFormat */
&pSince_locale, /* Text */
SQL_STAMP_STRLEN, /* MaxLength, */
pSince_td); /* GetTIMEDATE*/
return rc;
}
/********************************************************************************
Function NGetItem
This function retrieves a field (item) from a Notes document. If the
document is deleted or the field is not present, then it sets the null
indicator. If the field is a collection, then it copies the first
entry in the collection. If the entry is too big, the function sets a
warning flag wit appropriate warning information.
********************************************************************************/
void NGetItem (NOTEHANDLE NoteHandle,
char *ItemName,
SQLUDF_VARCHAR *Column,
SQLUDF_NULLIND *ColumnInd,
BOOL Deleted,
WORD len,
char *sqlstate,
char *sqlmsg)
{
WORD entry_len;
if (Deleted)
{
*ColumnInd = -1;
}
else
{
/* Return the entry text in the buffer provided and the */
/* function itself returns the length of the entry. */
if (NSFItemIsPresent(NoteHandle, /* note handle */
ItemName, /* item name */
(WORD) strlen (ItemName))) /* item name length*/
{
entry_len = NSFItemGetTextListEntry(NoteHandle, /* note handle */
ItemName, /* item name */
0, /* entryposition */
Column, /* entr text */
len); /* 0xFFFF text len */
if (entry_len > len)
{
/* Raise truncation warning */
strcpy (sqlstate, "01H00");
sprintf (sqlmsg, "%s truncated by %u characters",
ItemName, entry_len - len);
}
*ColumnInd = 0;
}
else
{
*ColumnInd = -1;
}
}
}
/********************************************************************************
Function SQLReportError
This function sets SQL state and SQL message using Lotus Notes error message.
********************************************************************************/
void SQLReportError (STATUS rc, char *state, char *sqlstate, char *sqlmsg)
{
WORD ln;
strcpy (sqlstate, state);
if (rc)
{
ln = OSLoadString(NULLHANDLE, ERR(rc), (char far*)sqlmsg, (WORD)SQLUDF_MSGTEXT_LEN);
if (!ln)
sprintf (sqlmsg, "Notes API return code %d not found.", rc);
}
return;
}
/********************************************************************************
Function ColStrlen
This function returns the size of a return column by "poking" the
memory for value delimiters (DEADBEEF).
********************************************************************************/
WORD ColStrlen (char *x)
{
WORD y = 0;
sqlint32 *z;
z = (sqlint32 *) x;
while ( (*z) != 0xDEADBEEF)
{
x++;
y++;
z = (sqlint32 *) x;
}
return y--;
}
/********************************************************************************
Function swmemcpy
This function swaps a 4-byte value (little endian)
********************************************************************************/
void swmemcpy (char to[4], char from[4])
{
to[0] = from [3];
to[1] = from [2];
to[2] = from [1];
to[3] = from [0];
}