Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Transaction Queue

Posted on 1998-06-02
6
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 200 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

609 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