The SQL to the Orbiting Ball

12 09 2010

September 12, 2010

Several years ago I developed a somewhat simple program in 16 bit Borland C (last compiled in 1994, so it pre-dates graphical web pages) that was optimized for 386 and 486 computers which sported VGA graphics but was also able to work with EGA graphics cards.  The simple program, after creating 18 images on the fly that simulated a rotating basketball, generated X and Y coordinates using a specially crafted formula that was capable of producing X and Y cordinates in a circular pattern.  With slightly different inputs the same formula produced a slightly out of round circular pattern that eventually might have generated straight line patterns.  With a slight adjustment to the inputs the same formula produced W patterns within a bounded box.  Keyboard input allowed the user to specify adjusted inputs as the program executed.  The C program looked like this:

#include <graphics.h>               // for graphics functions
#include <conio.h>                  // for kbhit()
#include <math.h>
#include <dos.h>
#define LEFT      100               // boundary of rectangle
#define TOP       100
#define RADIUS    20                // radius of ball
#define pi        3.14159276
#define L_ARROW   75
#define R_ARROW   77
#define U_ARROW   72
#define D_ARROW   80
#define ESC       27

void main(void)
   {
   int driver, mode;                // for initgraph()
   float x, y, dx, dy, i, sped, temp, x1, x2, y1, y2, sta1, sta2, ena1, ena2, rad;    // ball coordinates
   int imgnum, tim, del;
   char key;
   unsigned char ballbuff[10][5000];    // buffer for ball image

   driver = DETECT;                 // auto detect
        // set graphics mode
   initgraph(&driver, &mode, "c:\\bc4\\bgi");

   x = LEFT + RADIUS;          // designate center where ball is created
   y = TOP  + RADIUS;
   for (i =0; i <180; i = i + 18) // create basketball rotation images
       {
 setcolor(RED);
 setfillstyle(SOLID_FILL, RED);
 circle(x, y, RADIUS);
 floodfill(x, y, RED);
 setcolor(BLACK);
 rad = i / (2 * pi);
 x1 = x + 30 * cos(rad);
 x2 = x - 30 * cos(rad);
 y1 = y + 30 * sin(rad);
 y2 = y - 30 * sin(rad);
 sta1 = (180 + i -62);
 ena1 = (180 + i + 42);
 sta2 = (i -62);
 ena2 = (i + 42);
 if ((i/36) != int(i/36))    // must be included to swap arcs
    {
     temp = sta1;
     sta1 = sta2;
     sta2 = temp;
     temp = ena1;
     ena1 = ena2;
     ena2 = temp;
    }
 ellipse (x1, y1, sta1, ena1, RADIUS , RADIUS);
 ellipse (x2, y2, sta2, ena2, RADIUS , RADIUS);
 line (x - cos(rad + pi/2) * RADIUS, y - sin(rad + pi/2) * RADIUS, x + cos(rad + pi/2) * RADIUS, y + sin(rad + pi/2) * RADIUS);
        // pickup image
 getimage(x-RADIUS-20, y-RADIUS-20, x+RADIUS+20, y+RADIUS+20, ballbuff[i/18]);
        // clear screen
 setcolor(WHITE);
 rectangle(-1,-1, 640, 480);
 setfillstyle(SOLID_FILL, BLACK);
 floodfill(100,100, WHITE);
       }

   imgnum = 10;                         // load first position + 1
   tim = 0;                             // set delay to zero
   dx = .1;                             // set constant in x equation
   dy = .1;
   sped = 1;
   del = 1;
   while ( key != ESC )             
      {
      if (kbhit())
      {
       key = getch();
       if (key == 61) del++;            // = key pressed
       if (key == 43) del++;            // + key pressed
       if (key == 45) del--;            // - key pressed
       if (key == 47) sped = sped +.1;  // / key pressed
       if (key == 92) sped = sped -.1;  // \ key pressed
       if (key == 0)                    // place image on screen
 switch(getch())
 {
  case L_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx -.01;
     break;
  case R_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dx = dx + .01;
     break;
  case U_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dy = dy + .01;
     break;
  case D_ARROW:
     putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], XOR_PUT);
     dy = dy -.01;
     break;
  case ESC:
     key = 27;
     break;
        }
      }
      tim = tim + sped;
      x = (sin(dx * tim)*100) + 300;
      y = (cos(dy * tim)* 100) + 300;
      imgnum--;                   // cycle through images
      if (imgnum == -1) imgnum = 9;
      putimage(x-RADIUS, y-RADIUS, ballbuff[imgnum], COPY_PUT);

      // move ball across screen
      // to make ball move rapidly increase +
      // set height on screen
      delay(del);                 // make delay smaller for slow computers
      }
   getch();

   closegraph();
   }

If you still have a 16 bit Borland C compiler (my copy is in a huge box on the bottom self of my bookcase), feel free to compile the above program to see it in action.  The original compiled C program, last compiled in 1994, may be downloaded here: OrbitBall2.zip (save as OrbitBall.zip and extract the files – download might not work).  The original program is confirmed to work on 32 bit Windows 95 through Windows XP (in DOS full screen mode), but definitely will not work on 64 bit Windows, even in a virtual machine (it also failed to run on a 32 bit Windows 7 netbook).

You are probably thinking, “Neat, but what does that have to do with Oracle?”.  It might be interesting to produce a modernized version of the above program.  We could use a simple SQL statement like the following to generate 3,600 X and Y coordinates, much like the X and Y coordinates that were generated by the above C program (note that the COS function is typically used to derive X values (using mathematical cosine) and the SIN function is typically used to derive Y values – the functions were swapped so that the X and Y cordinates start at the bottom-center of the screen).  The SQL follows:

SELECT
  ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X,
  ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y
FROM
  (SELECT
    0.1 DX,
    0.1 DY,
    1 SPEED,
    1 DELAY,
    300 WIDTH,
    300 HEIGHT,
    LEVEL COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=3600);

Now we have a slight problem, how do we present the X and Y coordinate points produced by the above SQL statement?  We need some sort of object to track the X and Y coordinate pairs.  Drawn basketballs might work, but instead I will use these pictures (created with Microsoft Power Point 2010):


To display the round oak pictures, we will put together a VBS script to build a web page on the fly, cycling through the above eight pictures (every two X,Y coordinate pairs cause the displayed picture to change).  Much like the original program, we will allow the user to control the input parameters as the program runs.  Each time the parameters are adjusted, 3,600 new X,Y coordinate points are retrieved from the database into an array – this allows the VBS script to continue at the next X,Y coordinate pair, rather than starting at the beginning every time the parameters are adjusted. 

Option Explicit

Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
Const adTinyInt = 16
Const adUnsignedBigInt = 21
Const adUnsignedInt = 19
Const adUnsignedSmallInt = 18
Const adUnsignedTinyInt = 17
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adUseClient = 3

Dim dbDatabase
Dim snpData
Dim comData
Dim varData
Dim objIE

Dim strUsername             'Username
Dim strPassword             'Password
Dim strDatabase             'SID name from tnsnames.ora

startup

Sub startup()
    Dim strSQL
    Dim strHTML
    Dim objOrbitBall
    Dim objOrbitBallPic
    Dim objCommand
    Dim objSettings
    Dim i
    Dim intQuit

    'Fire up Internet Explorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Left = 0
    objIE.Top = 0
    objIE.Width = 930
    objIE.Height = 820
    objIE.StatusBar = True
    objIE.MenuBar = False
    objIE.Toolbar = False
    objIE.Navigate "about:blank"
    objIE.Document.Title = "The SQL to the Orbiting Ball"
    objIE.Visible = True

    'The data entry area
    strHTML = "<div style=""position: absolute;width: 180px; height: 200px;top: 10px;left: 710px;"">" & vbCrLf
    strHTML = strHTML & "<input type=text id=txtCommand value="""" size=""1""><br>" & vbCrLf
    strHTML = strHTML & "<font size=1><b>+&nbsp;&nbsp;&nbsp;Increase Delay (Not Used)<br>" & vbCrLf
    strHTML = strHTML & "-&nbsp;&nbsp;&nbsp;Decrease Delay (Not Used)<br>" & vbCrLf
    strHTML = strHTML & "/&nbsp;&nbsp;&nbsp;Increase Rotation Speed<br>" & vbCrLf
    strHTML = strHTML & "\&nbsp;&nbsp;&nbsp;Decrease Rotation Speed<br>" & vbCrLf
    strHTML = strHTML & "D&nbsp;&nbsp;&nbsp;Increase Rotation Speed X Axis<br>" & vbCrLf
    strHTML = strHTML & "A&nbsp;&nbsp;&nbsp;Decrease Rotation Speed X Axis<br>" & vbCrLf
    strHTML = strHTML & "W&nbsp;&nbsp;&nbsp;Increase Rotation Speed Y Axis<br>" & vbCrLf
    strHTML = strHTML & "S&nbsp;&nbsp;&nbsp;Decrease Rotation Speed Y Axis<br>" & vbCrLf
    strHTML = strHTML & "L&nbsp;&nbsp;&nbsp;Increase Width X Axis<br>" & vbCrLf
    strHTML = strHTML & "J&nbsp;&nbsp;&nbsp;Decrease Width X Axis<br>" & vbCrLf
    strHTML = strHTML & "I&nbsp;&nbsp;&nbsp;Increase Height Y Axis<br>" & vbCrLf
    strHTML = strHTML & "K&nbsp;&nbsp;&nbsp;Decrease Height Y Axis<br>" & vbCrLf
    strHTML = strHTML & "(space)&nbsp;&nbsp;&nbsp;Restart at 0<br>" & vbCrLf
    strHTML = strHTML & "X&nbsp;&nbsp;&nbsp;Exit</b></font>" & vbCrLf
    strHTML = strHTML & "</div>" & vbCrLf

    'The current orbit information
    strHTML = strHTML & "<div id=""Settings"" style=""position: absolute;width: 180px; height: 100px;top: 600px;left: 710px;""> </div>"
    strHTML = strHTML & "<IMG ID=""picOrbitBall"" style=""position: absolute;"" src=""http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball0.png"">" & vbCrLf
    objIE.Document.Body.InnerHTML = strHTML

    'The sleep here is only necessary if the database connections happen very quickly
    'Wscript.Sleep 500

    Set dbDatabase = CreateObject("ADODB.Connection")
    Set snpData = CreateObject("ADODB.Recordset")
    Set comData = CreateObject("ADODB.Command")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbDatabase.open

    'Should verify that the connection attempt was successful, but I will leave that for someone else to code

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  ROUND((SIN(DX * (SPEED * COUNTER)) * WIDTH/2) + WIDTH/2) X," & vbCrLf
    strSQL = strSQL & "  ROUND((COS(DY * (SPEED * COUNTER)) * HEIGHT/2) + HEIGHT/2) Y" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  (SELECT" & vbCrLf
    strSQL = strSQL & "    ? DX," & vbCrLf
    strSQL = strSQL & "    ? DY," & vbCrLf
    strSQL = strSQL & "    ? SPEED," & vbCrLf
    strSQL = strSQL & "    ? DELAY," & vbCrLf
    strSQL = strSQL & "    ? WIDTH," & vbCrLf
    strSQL = strSQL & "    ? HEIGHT," & vbCrLf
    strSQL = strSQL & "    LEVEL COUNTER" & vbCrLf
    strSQL = strSQL & "  FROM" & vbCrLf
    strSQL = strSQL & "    DUAL" & vbCrLf
    strSQL = strSQL & "  CONNECT BY" & vbCrLf
    strSQL = strSQL & "    LEVEL<=3600)"

    With comData
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase

        'Add the bind variables
        .Parameters.Append .CreateParameter("dx", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("dy", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("speed", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("delay", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("width", adDouble, adParamInput, 30)
        .Parameters.Append .CreateParameter("height", adDouble, adParamInput, 30)
    End With

    comData("dx") = 0.1
    comData("dy") = 0.1
    comData("speed") = 1
    comData("delay") = 1
    comData("width") = 600
    comData("height") = 600

    Set snpData = comData.Execute

    'Retrieve up to 10,000 data points from Oracle
    varData = snpData.GetRows(10000)
    snpData.Close
    Set snpData = Nothing

    'Allow faster access to these objects when executing in the loop
    Set objOrbitBall = objIE.Document.getElementById("picOrbitBall").Style
    Set objOrbitBallPic = objIE.Document.getElementById("picOrbitBall")
    Set objCommand = objIE.Document.All.txtCommand
    Set objSettings = objIE.Document.getElementById("Settings")

    'Write out the current settings for the orbit
    objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
                            "Speed: " & comData("speed") &Chr(10) & _
                            "Width: " & comData("width") & Chr(10) & "Height: " & comData("height")

    intQuit = False

    Do While intQuit = False
        For i = 0 To UBound(varData, 2)
            objOrbitBall.Left = CInt(varData(0, i))
            objOrbitBall.Top = CInt(varData(1, i))
            objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i/2 Mod 8 ) & ".png"

            Wscript.Sleep 50
            Select Case Left(objCommand.Value, 1)
                Case "=", "+"
                    comData("delay") = comData("delay") + 1
                Case "-"
                    comData("delay") = comData("delay") - 1
                Case "/"
                    comData("speed") = comData("speed") + 0.1
                Case "\"
                    comData("speed") = comData("speed") - 0.1
                Case "W", "w"
                    comData("dy") = comData("dy") + 0.0005
                Case "S", "s"
                    comData("dy") = comData("dy") - 0.0005
                Case "D", "d"
                    comData("dx") = comData("dx") + 0.0005
                Case "A", "a"
                    comData("dx") = comData("dx") - 0.0005
                Case "I", "i"
                    comData("height") = comData("height") + 5
                Case "K", "k"
                    comData("height") = comData("height") - 5
                Case "L", "l"
                    comData("width") = comData("width") + 5
                Case "J", "j"
                    comData("width") = comData("width") - 5
                Case "X", "x"
                    intQuit = True
                    Exit For
                Case " "
                     'Reset the loop from the beginning
                     objCommand.Value = ""
                     Exit For
            End Select

            If objCommand.Value <> "" Then
                objCommand.Value = ""

                Set snpData = comData.Execute

                'Retrieve up to 10,000 data points from Oracle
                varData = snpData.GetRows(10000)

                snpData.Close
                Set snpData = Nothing

                'Write out the current settings for the orbit
                objSettings.InnerText = "DX: " & comData("dx") & Chr(10) & "DY: " & comData("dy") & Chr(10) & _
                                        "Speed: " & comData("speed") &Chr(10) & _
                                        "Width: " & comData("width") & Chr(10) & "Height: " & comData("height")
            End If
        Next
    Loop

    objIE.quit
    dbDatabase.Close
    Set dbDatabase = Nothing
    Set objIE = Nothing
End Sub

You may download the above script here: SQLOrbitingBall.vbs (save as SQLOrbitingBall.vbs).

A Circular Orbit:

————-

An Orbit that Changes from a Circular Orbit to a Straight Line:

————-

An Orbit where the Ball Bounces Between the Top and Bottom of the Window:

—————————

As written, the script assumes a minimum of 930 x 820 resolution (1080p resolution or greater should work without any problems).  Adjust the script as necessary for lower resolution screens.  The program written in C certainly is shorter than the moderized version of the program, and had a bit more wow factor prior to the widespread use of Windows and other graphical user interfaces.


Actions

Information

2 responses

13 09 2010
Charles Hooper

A couple of suggestions. If the oak disc does not rotate, immedaitely before this line:

Do While intQuit = False

Add the following to give the computer 2 seconds to download each of the images (increase if necessary):

 
    For i = 1 to 8
        objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i Mod 8 ) & ".png"
        Wscript.Sleep 2000
    Next

The rotating action seems to be a bit smoother if the sleep delay in the Do loop is changed from:

Wscript.Sleep 50

To:

Wscript.Sleep 100

If the above change is made, change (i/2 Mod 8 ) to (i Mod 8 ), change from:

objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i/2 Mod 8 ) & ".png"

To:

objOrbitBallPic.Src = "http://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i Mod 8 ) & ".png"
14 09 2010
Charles Hooper

The link to the original OrbitBall.zip file did not work – WordPress apparently prevented that attachment from being downloaded. For the handful of people who tried to download the old 16 bit program, please try again. I changed the secondary extension from .doc to .pdf to hopefully permit you to download the file (it now works for me).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: