Insert Multiple record in one sql statement

There are instances that one needs to insert multiple records into a database. This is normally for those people having a group of items to be insterted.

Normal SQL statement does a single record insert like below

INSERT INTO mytable (Field1,Field2...) VALUES (value1, value2...)

This is good for a single record insert. What happens is that a person may select a group of items like shopping cart. In the shopping cart you have the item code, quantity and perhaps price as one record. But if user selects more than one item then it caused a database update issue. Usually the server side will use a grouping of the item in a list and do a loop to insert the records one by one.

The above method worked fine if you only have a small list of item to update. In the commercial world, the requirement can end up with thousands of items each with several fields to update. To do update using the above method most probably caused timeout problem on the server side. It has to loop thousands of insert statement within one submission.

The under mentioned method is not my own idea but is copied from the internet while searching for solutions. I would like to give credit to the person/s but has lost the information. Anyway, thanks to the availablilty of this information, it solved my problems.

The multiple record insert SQL statement runs like below

INSERT INTO mytable (Field1, Field2,...)
SELECT valuea1,valuea2,....
UNION ALL
SELECT valueb1,valueb2,...
UNION ALL
SELECT valuec1,valuec2,...


I have tested the SQL statement with MS SQL SERVER and it worked. Not sure if other database server can also work the same way.

This method does has its limitation though. Obviously string variables will ultimately has a length limit. Moreover, to parse huge lengths of strings may cause resource problems. After all, the manupulations are done in the server itself.

SVG BarCode 39

Posted a SVG based BarCode 39 sample program. The sample program can be found at http://www.hiew.per.sg .

This program is just a demo for BarCode generation. It does not fully conform to the BarCode 39 standard but the generated code should be able to be scanned correctly.

Code 39 gets its name from the way it is coded. It contains 5 bars and 4 spaces. each bar or space can be wide or narrow. Three of the bar of space must be wide. Thus the name 3 of 9.

The method of converting the text to ascii is quite simple. The translation codes are stored as text (in binary code) in an array. When user input a string, each character is then translated to its barcode binary by directly translating its ascii code as the array pointer.

The next step is to simply to translate that code as a series of rectangle or a move (if it is supposed to be a space code) using the SVG path command. The rectangle and space code is also stored in an array for easy retrieval by translating the binary code as value and retrieve the value depending on which array to take value from.

The code 39 start and stop character is simply stored as ascii 128 and is inserted before the parsing of the user input.

The inter character spacing is also coded together with the translation table for easy coding thus making the binary string as 10 digits. I used the simple form of the inter character space calculation using I=X (interspace equals smallest bar size). I also use the smallest ration of 1:2 between the narrow and wide code.

There is a limitation to the number of barcode that can be displayed. It depends on your Browser window as the width for the html embed statement is 100%. But by dynamically calculating the barcode width, you should be able to change it programmatically.

If you use viewbox in SVG, you should be able to shrink or expand the barcode overall size. But that means you must really conform to the code 39 specification of calculating the width and height of the overall size corresponding to the barcode width and height properly otherwise the scanner might not be able to decode it.

I have also created a barcode 39 font quite some years ago. This SVG BarCode39 program is based on the setting of the font I created. It took me a few days to create and test the font. Moreover, the font cannot be used on a web. I have to use WSH to convert it to web enabled form. It took me another few day to get the conversion working. It only took me half a morning to compelete the SVG barcode program. What a difference in coding time. However, barcode font can be used in windows. SVG barcode can only be used on web.

SVG Chart

Updated the SVG chart by standardizing the DOM interface for both manual and automation. The EMAScript is now moved to SVG itself. Calls are made to the EMAScript exposed function and values are passed into it.

This method is cleaner because all the necessary chart preparation are done right inside the SVG instead of at the javascript level at the Main page. The main page now just composes the information and passed the variables to the SVG to process. Therefore, the manual chart composition page (linechartform.html) and the automated php (not provided) are now using almost identical method calls.

SVG Xiang Qi

Created a Chinese Chess Board game at http://www.hiew.per.sg.

Currently this game is just a simple game board with mouse clickable seeds. You can move the seed according to the Chinese Chess rule. Only rule not implemented is the Jiang to Shuai facing constrain.

Intend to add in the Player to Computer and Player to Remote Player interface. I am thinking of using existing Chess Engine for the Player to Computer interface. I am also thinking of using Java or XML-RPC to accomplish the peer-to-peer connection. Even thinking of using Existing IM interface to pass the moves.

A less real time mode will be using the PHP with session enabled and the session ID passed between two person so that they could use common variables. It means that the page will be an iframe in the main page that reloads at regular intervals of 1 sec. Since they use common session ID (by setting no cookies. A very bad practice of using the session id) Not bad if the number of users are small.

First user will login to the web page and in the process insert the session ID into the database. The second person simply login and look for the first person. Once selected, the first person's ID will be copied from the database and both can start to use common variables. Of course it does not stop a third person to access the session ID. The program will have to prevent any one other than the first two from playing.

SVG Charts

Added two charts at http://www.hiew.per.sg.

The Line Chart Form is for generating line charts and bar charts. Simple line and bar charts can be created by simply entering the data, separated by commas, into the input fields. You can experiment with the form and see immediate results in the chart below it.

The program is used in conjunction with Adobe SVG Viewer plugin. I is a very powerful Scalable Vector Graphic viewer that conforms to the WWW consortium svg standard.

The Line Chart Form also includes 5 different type of statistical line charts.

1. X-Bar Chart. (Up to 10 input lines. Can accomodate a lot of data.)
2. R Chart. (Up to 10 input lines. Can accomodate a lot of data.)
3. P Chart. (Only two lines are allowed. The first is the value. And the second the total. Percentage is calculated by the program.)
4. U Chart. (Only two lines are allowed. The first is the value. And the second the total.)
5. C Chart. (Up to 10 input lines. Can accomodate a lot of data.)

The calculations are all done externally to the SVG. It has not been tested thoroughly for calculation errors.

The Pie chart form is a simple flat pie chart program that also uses SVG to display. Up to a total of 16 pies can be created. I have not animate the pies to extend out when the mouse is over it.

Screen Reader Program

An ambitious attempt to write program handiling speech synthesis.

The program begins with the idea to create a speech synthesizer program that can read to user the text that was highlighted. It was then extended to enable it as a screen reader that could read mouse over icons names. This is very good for a blind person to read the screen.

Since the program is going to be free, I uses free program resources to do the programming.

First, I used BCX as a programming tool. BCX is a Basic to C compiler by Kevin Diggins. It is available at http://bcx-basic.sourceforge.net.

Next I use the Mbrola Speech API from http://tcts.fpms.ac.be/synthesis/mbrola.html. It is a diphone speech synthesizer engine. It is free for non commercial use.

I wrote the speech translation based on the Carnegie Mellon University speech dictionary at http://www.speech.cs.cmu.edu/cgi-bin/cmudict.

I also add a simple translation program based on adaptations from a number of programmers. The name of which I am unable to recall. My apologies.

The programming stops at the stage where I used window hooks to capture keystroke and pronounce the key typed. I am stuck with the accessible function where the window menus and icons properties can be retrieved.

Currently it worked as a standalone program with the main speech interface and my own translation interface as a dll. The reason being windows hook requires the call back function to be in a dll. I have used another windows hook (which does not need to be residing in a dll) since then but is too lazy to transform it back to the main exe.

Note. The souce code and the compiled program is not uploaded yet. i felt it useless to upload the program till the basic screen reader interface is completed. Thought of parking the program at sourceforge.

To do list.

1. integrate the screen reader function with the speech synthesizer. Currently, it can only read the text when user highlights it and press SHIFT-CTRL-Z.

2. Add windows menu and icon property reading.

3. speed up the reading translation interface. Currently it is quite slow with noticeable long pause between sentences (I break the reading of text by sentences so that long text can be pronounced without the full translation being completed.)

4. Adapt it to other OS. Currently it works on Win98 only. I tried XP and it hangged after a while.

ColdFusion Programming -cfquery with javascript

Sample of using CFQUERY with javascript. The code allows the creation of an array of the CFQUERY. This array can then be manupulated by other javascript functions for searching a value in the array. Example of its use is doing a search for existing user name, if user enter a name that already exists, it will not post the submission. This will cut down the number of postings between server and client.

The following is the code sample.

<CFQUERY datasource = loanersrc" name = "loan">
select ID,MODEL_NO,SERIAL_NO,DESCRIPT from loanerwhere status = 'Available' ) ORDER BY MODEL_NO,SERIAL_NO
</cfquery><script language="javascript">
<cfset EQcount = 1>
myarray = new Array()
<cfloop query="loan">
myarray[#EQcount#]= new Array()
myarray[#EQcount#][1] = "#ID#"
myarray[#EQcount#][2] = "#REPLACE(ucase(MODEL_NO),CHR(34),"","ALL")#"
myarray[#EQcount#][3] = "#ucase(SERIAL_NO)#"
<cfset EQcount = EQcount + 1>
</cfloop>