Solved

Transaction Queue

Posted on 1998-06-02
6
338 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
ID: 1091416
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
ID: 1091417
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
ID: 1091418
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:flfmdll
ID: 1091419
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
ID: 1091420
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
ID: 1091421
Thank you very much.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 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