## 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 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
for (i =0; i <180; i = i + 18) // create basketball rotation images
{
setcolor(RED);
setfillstyle(SOLID_FILL, RED);
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;
}
// pickup image
// 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:
dx = dx -.01;
break;
case R_ARROW:
dx = dx + .01;
break;
case U_ARROW:
dy = dy + .01;
break;
case D_ARROW:
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;

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

Dim dbDatabase
Dim snpData
Dim comData
Dim varData
Dim objIE

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.Toolbar = False
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=""https://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

'Database configuration
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
.CommandTimeout = 30

.ActiveConnection = dbDatabase

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

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 = "https://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```

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.

### 2 responses

13 09 2010

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 = "https://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 = "https://hoopercharles.files.wordpress.com/2010/09/sqlorbitingball" & cStr(i/2 Mod 8 ) & ".png"
```

To:

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

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).