Solved

Transaction Queue

Posted on 1998-06-02
6
329 Views
Last Modified: 2012-05-04
I need to build a queue processing program. This program will read records from a SQL Server database. It will use this record to access another table and then launch a program or store procedure. The results of this transaction will then be e-mailed.

This seems like something that someone must have done already. Anyone have any leads on this?
0
Comment
Question by:flfmdll
6 Comments
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
Yes! The answer is....programming!  You've just described the typical mix of operatons that result in a custom database oriented program.  Recomendation: Use VB5 Enterprise Edition, RDO Access method.  You can easily put your resultset read in a slow loop to pick up rows as they appear, then use those results as keys (whatever) to create another SQL statement to perform your lookup in either the same or another database.  VB also has relatively good tools to launch programs and access email systems using MAPI or DDE.  

A slower and poorer alternative to this would be to use Access as an intermediate tool, link your SQL tables to the Access database, and perform parallel type of code in a VBA module in Access.

Much beyond this and you're asking somebody else to do the programmng for you.  If you have specific questions about how to actually perform some of these steps, ask additional questions here.
0
 

Author Comment

by:flfmdll
Comment Utility
Okay. I'm not asking someone else to do the programming for me. That's a given. I've been programming in depth for 14 years. This has covered a range of OSs, languages, business needs, etc. But what I am looking for is someone who has done this sort of thing using SQL Server, VB or VC, CGI, and Store Procedures. I'm not necessarily looking for code, but I am looking for paths to follow or not to follow; little known capabilities of SQL, Transaction Server, and IIS. I'm not looking for an in depth analysis, but if there is something that will make my design and implementation cleaner, more robust, and easier then I would like to know about it.

That's all.

I will be using VC rather than VB.
0
 
LVL 1

Expert Comment

by:JayMerritt
Comment Utility
You could probably do the whole thing in SQL if you want to utilize CURSORS.  It might get pretty thick, though.  SQL does not have a decent debugging mechanism for huge routines.

For the last part, you can use XP_SENDMAIL to send mail.  This will also allow for attaching query results as attachments.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:flfmdll
Comment Utility
What is XP_SENDMAIL? I guess it's not SQL Servers mail? Is XP_SENDMAIL readily available?
0
 
LVL 1

Accepted Solution

by:
lekshmikr earned 50 total points
Comment Utility
this program reads table from the sql server and prints to a file .
check the code in the line " (dbnextrow (dbproc) != NO_MORE_ROWS)" in the program i have shown .I t could prove usefil to you.

Then about xp_sendmail ,it is an extended stored procedure in MS SQL server which Sends a message, and/or a query results set, and/or an attachment to the specified recipients.


#define DBNTWIN32
#define SERVER "VSOUND"
#define USER   "sa"      
#define PAGELENGTH 40
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <string.h>
#include <sqldb.h>
#include <malloc.h>
#include <conio.h>
//#include "PrintTit.h"
// Forward declarations of the error handler and message handler.
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
                LPCSTR, DBUSMALLINT);
struct Node
{
    struct Node *Prv;
    char TableName[31];
      struct Node *Next;
};

main()
{
    PDBPROCESS  dbproc;    // The connection with SQL Server.
    PLOGINREC   login;     // The login information.
    DBCHAR      name[100];
    DBCHAR      date[100];
      DBCHAR      sDbname[100];
      DBCHAR      Server[30];
      FILE            *ptr;
      int         Exists = 0, i = 0,c, PageNo = 1, LinNo = 0, SlNo = 0;
      struct Node *TableList, *FirstNode, *NodeSave;
    char        typename[31];
    char        length[4];
    char        precision[6],uLine[80];
      char        UserName[30];
      char        PassWord[30];
    char        IndexName[25];
      char        IndexDescription[60];
      char        IndexKeys[260];
    // Install user-supplied error- and message-handling functions.
    dberrhandle (err_handler);
    dbmsghandle (msg_handler);

    // Initialize DB-Library.
    dbinit ();

    // Get a LOGINREC.
    login = dblogin ();
       
   

    // Get a DBPROCESS structure for communication with SQL Server.
      printf("Enter Server   Name :");
      scanf ("%s",Server);
      printf("Enter DataBase Name :");
      scanf ("%s",sDbname);
    printf("Enter User     Name :");
      scanf("%s",UserName);
      printf("Enter Password      :");
      while((c = getch()) != 13 && c != 10)
      {
         if(c == 10 || c == 13)
         {
              break;
         }
         PassWord[i++] = c;
      }
    PassWord[i] = '\0';
      if( (ptr = fopen("dbinfo.txt","w")) == (FILE *) NULL)
      {
       printf("File Open Error \n");
         fclose(ptr);
         dbexit();
         exit(1);
      }
      //printf("%s\n",PassWord);

    DBSETLUSER (login, UserName );
    DBSETLPWD (login, PassWord);
    DBSETLAPP (login, "example");
    if( (dbproc = dbopen (login, Server)) == NULL)
      {
      dbexit();
        printf("Login to %s failed.\n",Server);
        exit(1);
      }
      //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
      //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
      fprintf(ptr,
            "                          Tables and Indexes Listing    Page : %3d",PageNo++);
      fprintf(ptr,
            "                          --------------------------    Date : %s", "04/04/1997");
    fprintf(ptr,"DATABASE  : %s\n", sDbname);
      fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
      //fprintf(ptr,"USER NAME : %s\n",UserName);
      memset(uLine,'-',79);
    fprintf(ptr,"%s\n",uLine);
      LinNo += 6;

    dbcmd (dbproc, "SELECT name, convert(char, crdate, 103) FROM  Master.dbo.SYSdatabases");
    dbsqlexec (dbproc);


    if (dbresults (dbproc) == SUCCEED)
    {
        // Bind column to program variables.
        dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
        dbbind (dbproc, 2, NTBSTRINGBIND, 0, date);

        // Retrieve and print the result rows.
        while (dbnextrow (dbproc) != NO_MORE_ROWS)
        {
            if( (strcmp(strupr(name),strupr(sDbname)) == 0) )
                  {
                    Exists = 1;
                  }
        }
    }
      if(Exists <= 0)
      {
      dbexit ();
      printf("No Such Database\n");
        exit(1);
    }

    // Retrieve some columns from the "authors" table in the
    // "pubs" database.

    dbfcmd (dbproc, "use %s",sDbname);
    dbsqlexec (dbproc);
    if (dbresults (dbproc) == SUCCEED)
    {
       while (dbnextrow (dbproc) != NO_MORE_ROWS)
         {
         
         }
    }
    dbuse(dbproc, sDbname);
    // First, put the command into the command buffer.
    TableList = (struct Node *)malloc(sizeof(struct Node));
      TableList->Prv  = (struct Node*)NULL;
      TableList->Next = (struct Node*)NULL;
    FirstNode = TableList;
  /*printf ("%20s  %20s\n", "Table Name", "Created Date");
      printf ("%20s  %20s\n", "----------", "------------");
      fprintf (ptr,"%20s  %20s\n", "Table Name", "Created Date");
      fprintf (ptr,"%20s  %20s\n", "----------", "------------");*/
    dbfcmd (dbproc, "SELECT name, convert(char, crdate, 103) FROM  %s.dbo.SYSOBJECTS WHERE TYPE = 'U'",sDbname);

    dbsqlexec (dbproc);

    // Process the results.
    if (dbresults (dbproc) == SUCCEED)
    {
        // Bind column to program variables.
        dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
        dbbind (dbproc, 2, NTBSTRINGBIND, 0, date);

        // Retrieve and print the result rows.
        while (dbnextrow (dbproc) != NO_MORE_ROWS)
        {
          /*printf ("%20s %20s\n", name, date);
            fprintf (ptr,"%20s %20s\n", name, date);*/
                  sprintf(TableList->TableName,"%s",name);
                  NodeSave        = TableList;
            TableList       = TableList->Next = (struct Node *) malloc(sizeof(struct Node));
                  NodeSave->Next  = TableList;
                  TableList->Prv  = NodeSave;
                  TableList->Next = (struct Node *)NULL;
        }
            NodeSave->Next = (struct Node *)NULL;
    }
    //getch();
    // Close the connection to SQL Server.
      TableList = FirstNode;
      while(1)
      {
         memset(uLine,'-',79);
       printf("\n\nTable : %-30s\n",TableList->TableName,name);
         printf("%s\n", uLine);
       printf("%-4s %-30s %-30s %-8s %-4s\n","SlNo", "Name","DType", "Length", "Prec");
         printf("%s\n",uLine);
       fprintf(ptr,"\n\nTable : %30s\n",TableList->TableName,name);
         fprintf(ptr,"%s\n", uLine);
       //fprintf(ptr,"%-4s %-30s %-30s %-8s %-4s\n","SlNo","Name","DType", "Length", "Prec");
       fprintf(ptr,"%-4s %-30s %-30s \n","SlNo","Name","DType");
         fprintf(ptr,"%s\n",uLine);
         LinNo += 6;
       //dbfcmd (dbproc,"execute Master.dbo.sp_GetColumns '%s'",TableList->TableName);

         dbfcmd (dbproc, "select c.name, t.name,");
         dbfcmd (dbproc, "length = convert(char(3),c.length),");
       dbfcmd (dbproc, "Prec = convert(char(5),c.prec)");
       dbfcmd (dbproc, " from %s.dbo.syscolumns c, %s.dbo.systypes t", sDbname, sDbname);
         dbfcmd (dbproc, " where c.id = object_id('%s')", TableList->TableName);
       dbfcmd (dbproc, " and c.usertype *= t.usertype");

       dbsqlexec (dbproc);

       // Process the results.
       if (dbresults (dbproc) == SUCCEED)
       {
        // Bind column to program variables.
          dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
          dbbind (dbproc, 2, NTBSTRINGBIND, 0, typename);
          dbbind (dbproc, 3, NTBSTRINGBIND, 0, length);
          dbbind (dbproc, 4, NTBSTRINGBIND, 0, precision);
              SlNo = 0;
          while (dbnextrow (dbproc) != NO_MORE_ROWS)
          {
             printf("%4d %-30s %-30s %-8s %-4s\n",++SlNo, name,typename, length, precision);
             //fprintf(ptr,"%4d %-30s %-30s %-8s %-4s\n",SlNo, name,typename, length, precision);
             fprintf(ptr,"%4d %-30s %s(%s)\n",SlNo, name,typename, length, precision);
             LinNo ++;
                     if (LinNo > PAGELENGTH)
                   {
                        //fprintf(ptr,"\n%c\n",12);
                //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
                  //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
                         fprintf(ptr,"%c\n",12);
                              fprintf(ptr,
            "                          Tables and Indexes Listing    Page : %3d",PageNo++);
                    fprintf(ptr,
            "                          --------------------------    Date : %s", "04/04/1997");

                fprintf(ptr,"DATABASE  : %s\n", sDbname);
                        fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
                  //fprintf(ptr,"USER NAME : %s\n", UserName);
                     LinNo = 6;
                    }
          }
              //getch();
         }
         dbfcmd (dbproc, "execute sp_helpindex %s",TableList->TableName );
         printf (    "\nSlNo Index Name           Index Description                                        Index Keys\n");                                                                                                                                                                                                                                                      
       printf (      "---- --------------- -------------------------------------------------------- --------------------------------------------------\n");
         fprintf(ptr,"\nSlNo Index Name           Index Description                                        Index Keys\n");                                                                                                                                                                                                                                                      
       fprintf(ptr,  "---- --------------- -------------------------------------------------------- --------------------------------------------------\n");

       dbsqlexec (dbproc);

       if (dbresults (dbproc) == SUCCEED)
       {
          dbbind (dbproc, 1, NTBSTRINGBIND, 0, IndexName);
          dbbind (dbproc, 2, NTBSTRINGBIND, 0, IndexDescription);
          dbbind (dbproc, 3, NTBSTRINGBIND, 0, IndexKeys);
              SlNo = 0;
          while (dbnextrow (dbproc) != NO_MORE_ROWS)
          {
             printf ("%4d %-20s %-56s %-50s\n",++SlNo, IndexName, IndexDescription, IndexKeys);
             fprintf(ptr,"%4d %-20s %-56s %-50s\n",SlNo, IndexName, IndexDescription, IndexKeys);
             LinNo ++;
                     if (LinNo > PAGELENGTH)
                   {
                        fprintf(ptr,"\n%c\n",12);
                //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
                  //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
                              fprintf(ptr,
                         "                          Tables and Indexes Listing    Page : %3d",PageNo++);
                      fprintf(ptr,
                        "                          --------------------------    Date : %s", "04/04/1997");

                fprintf(ptr,"DATABASE  : %s\n", sDbname);
                        fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
                  //fprintf(ptr,"USER NAME : %s\n", UserName);
                     LinNo = 6;
                    }
              }

         }

         if( (TableList = TableList->Next) == (struct Node *) NULL)
              break;

      }
    fclose(ptr);
    dbexit ();
    return (0);
}



int err_handler (PDBPROCESS dbproc, INT severity,
    INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
    printf ("DB-Library Error %i: %s\n", dberr, dberrstr);
    if (oserr != DBNOERR)
    {
        printf ("Operating System Error %i: %s\n", oserr, oserrstr);
    }
    return (INT_CANCEL);
}


int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
    INT severity, LPCSTR msgtext, LPCSTR server,
    LPCSTR procedure, DBUSMALLINT line)
{
    printf ("SQL Server Message %ld: %s\n", msgno, msgtext);
    return (0);
}


0
 

Author Comment

by:flfmdll
Comment Utility
Thank you very much.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now