+2** An essential component of all of my Software **O
+4Do not delete, alter or Reverse Translate this file!O
CONTROLK%
SHIFTK%
MODK%
SLIDEHEIGHT
RECPOINT%
POPHEIGHT%
HIGHLIGHTPOS%
HIGHLIGHTBORDER%
BUTTONUP%
BUTTONDOWN%
BUTTONUPOFF%
BUTTONDOWNOFF%
HGAP%
RESOK%
RECPOS%
RECCOUNT%
INIT%
COUNT%
FONT&
FONTN&
HEIGHT%
SYSCTRL%
SYSCTRL$
PATH$
WINPOP%
WINPOPSCROLL%
WINSLIDER%
PBUFFADDR&
PLEN&
PTEXT&
TEMP1$
TEMP2$
BYTECOUNT&
FILEHANDLE%
BYTE%
MPPATHMBM$
MPVERSION
CLIPBOARDID%
_CHECKMBM
MPPATHMBM$
OZ4[f
:\system\opm\K+
MyProcs.mbmKW
:\system\opm\K+
MyProcs.mbmK
OZ<[S
+/MyProcs.mbm
by Ivo Woltring is not installed!+
Please reinstall MyProcsW8
_POKEDATA
_SAVEFILE%
PBUFFADDR&
PLEN&
PTEXT&
BYTECOUNT&
FILEHANDLE%
TEMP1$
TEMP2$
O$hD[
Save and ExitOe+
ExitO
ExitO
Oe@[!
TEMP1$
PTEXT&
BYTECOUNT&
PLEN&
PBUFFADDR&
PTEXT&
FILEHANDLE%
TEMP2$
BYTE%
Saving File...
MPPATHSPLIT$
O!h@`[
O h@[
MPHANDLEERROR
_CREATESLIDER
_PRINTLIST
_ACTIVEEVENT
RECCOUNT%
POPHEIGHT%
HIGHLIGHTBORDER%
HGAP%
HIGHLIGHTPOS%
RESOK%
SYSCTRL%
RECPOINT%
FONT&
FONTN&
HEIGHT%
WINPOP%
BUTTONUP%
MPPATHMBM$
BUTTONDOWN%
BUTTONUPOFF%
BUTTONDOWNOFF%
WINPOPSCROLL%
RECPOS%
WINSLIDER%
MODK%
SHIFTK%
CONTROLK%
SYSCTRL$
ITEM$
_Error_
ITEM$#
_SysCommand_W
_SysFocusLost_
{E\[
ITEM$#
_PENEVENT
_PAGEDN
_UPDATEBTNS
_PAGEUP
_RECORDDN
_RECORDUP
_ENDRESULT
_HOMERESULT
_SYSTEMKEY
WINPOP%
RESOK%
SYSCTRL%
SYSCTRL$
SHIFTK%
CONTROLK%
{A\[
'{A`[
+ _SysMENU_
_ENDRESULT
_UPDATESCROLL
_RECORDDN
_HOMERESULT
_RECORDUP
_CHECKPOPUP
_UPDATENOTCHES
_PAGEUP
_PAGEDN
_UPDATEBTNS
WINSLIDER%
WINPOPSCROLL%
INIT%
POPHEIGHT%
SLIDEHEIGHT
HIGHLIGHTBORDER%
WINPOP%
RECPOS%
BUTTONUP%
RECCOUNT%
BUTTONDOWN%
BUTTONDOWNOFF%
BUTTONUPOFF%
FONT&
RESOK%
NO0|NJ>[
NO0|NJ2[)
W0W1O
W0W1O
L{1\[
L{1[%
W/O4L{9
L{1\[>
W/O/L
L{9[?
W/O2L{1[?
NO/|NyO
L{1\[
W/O/L
W/O/L
RESOK%
SYSCTRL%
SYSCTRL$
{E\[4
+ _SysCtrl_O
O`{IxW
{E\[1
_SysSHIFT_O
{E\[:
_SysCtrl+SHIFT_O
O`{IxW
O {AO
{E\[.
_SysFN_O
O"{AO
{E\[4
_SysSHIFT+FN_O
O${AO
{E\[7
_SysCtrl+FN_O
O`{IxW
O&{AO
{E\[:
_SysCtrl+SHIFT+FN_O
O`{IxW
WINPOP%
RECPOINT%
HIGHLIGHTBORDER%
HGAP%
RESOK%
_UPDATENOTCHES
_UPDATESCROLL
_UPDATEBTNS
SLIDEHEIGHT
POPHEIGHT%
RECCOUNT%
COUNT%
WINPOP%
WINSLIDER%
LO2L|
NO0|N2[
NO0|NV*
?JWBx
WINSLIDER%
SLIDEHEIGHT
WINPOPSCROLL%
COUNT%
WINSLIDER%
POPHEIGHT%
SLIDEHEIGHT
NO0|NJ:[
NO0|NJy
WINPOPSCROLL%
RECPOS%
RECCOUNT%
BUTTONDOWN%
BUTTONDOWNOFF%
BUTTONUP%
BUTTONUPOFF%
W/O/L
W/O/L
WINPOP%
POPHEIGHT%
FONTN&
ITEM$#
_UPDATESCROLL
_PRINTLIST
_HOMERESULT
WINPOP%
RECPOS%
HIGHLIGHTBORDER%
HIGHLIGHTPOS%
HGAP%
_UPDATESCROLL
_PRINTLIST
_ENDRESULT
WINPOP%
RECPOS%
RECCOUNT%
HIGHLIGHTBORDER%
HGAP%
HIGHLIGHTPOS%
POPHEIGHT%
_UPDATESCROLL
_PRINTLIST
_HOMERESULT
RECPOS%
POPHEIGHT%
WINPOPSCROLL%
WINSLIDER%
HIGHLIGHTBORDER%
HGAP%
HIGHLIGHTPOS%
_UPDATESCROLL
_PRINTLIST
_ENDRESULT
RECPOS%
RECCOUNT%
POPHEIGHT%
WINPOPSCROLL%
WINSLIDER%
SLIDEHEIGHT
HIGHLIGHTBORDER%
HGAP%
HIGHLIGHTPOS%
NO0|NJ2[
_UPDATESCROLL
_UPDATEBTNS
WINPOP%
POPHEIGHT%
HGAP%
RECPOS%
WINPOPSCROLL%
WINSLIDER%
HIGHLIGHTPOS%
HIGHLIGHTBORDER%
ITEM$#
_UPDATESCROLL
_UPDATEBTNS
WINPOP%
POPHEIGHT%
RECCOUNT%
HGAP%
RECPOS%
WINPOPSCROLL%
WINSLIDER%
SLIDEHEIGHT
HIGHLIGHTBORDER%
HIGHLIGHTPOS%
ITEM$#
NO0|NJy
+ An unexpected error has occured!(
* There has been an W
* The error was: W
MPISDIR%
MPDIR
MPHANDLEERROR
H{9`[
{A\[~
MPPRINTXY
MPPATHMBM$
MPPATHMBM$
T|WBx
T|WBx
_WRITELONG
_WRITESTRING
_WRITEBYTE
CLIPBOARDID%
C:\System\Data\Clpboard.cbd(
CLIPBOARDID%
_WRITELONG
CLIPBOARDID%
CLIPBOARDID%
MPHANDLEERROR
C:\System\Data\Clpboard.cbd(
P{MxW
_MAIN+
_MPLOGOm
MPLINKg
_CHECKMBM
MPEDITOR%
_POKEDATAH
_SAVEFILE%
MPPATHSPLIT$[
MPISDIR%
MPPRINTXY
MPPRINTXYBOX5
MPCREATEPOPUP$z
_ACTIVEEVENTw
_PENEVENT
_SYSTEMKEY
_CHECKPOPUP[
_CREATESLIDER
_UPDATENOTCHES
_UPDATESCROLLw!
_UPDATEBTNS~"
_PRINTLIST
_RECORDUP<$
_RECORDDNy%
_PAGEUP
_PAGEDN?(
_HOMERESULT
_ENDRESULT
MPHANDLEERROR
MPISOPEN&
MPDIRu-
MPLOGO
MPDOLPHIN&.2
MPWRITETOCLIPBOARD)3
_WRITEBYTE
_WRITESTRINGZ4
_WRITELONG
MPREADCLIPBOARDBUFFER&15
SYSTEM\
Times New Roman
&Heading 1L
Times New Roman
&Heading 2L
Times New Roman
&Heading 3L
&Standaard
Times New Roman
Sketch
&Paint.app
,Ivo Woltring
1 - 1
Introduction
Sheet2SQL is a small APP that transforms Sheet files to SQL-Insert statements. It's ideal for creating a reusable testset for testing DB2-applications.
Just create your TestData in a easy to use spreadsheet (Sheet) and let my little tool loose on it.
A file will be created with the name of the the database.
Important
The tool needs to bring the Sheet to the foreground in order to perform its stuff. It makes use of the inbuilt clipboard function of the epoc device so it must have exclusive use of it during the processing. No cutting and pasting in other apps while the SQL statements are being made! The tool will fail!
Sheet Layout!
Just click on the Sheet-object to get a good look at a template Sheet.
The A-column is used to define Data-Lines. If a cell in the A-column is left empty the rest of that row will be seen as comment and will be left alone during processing.
>TABLE
Name of Database-Table.
Number of entries: 1
Mandatory
>PROLOGUE
Here you can add whatever you want. The entry must be started in the B-Column. These prologue entries will be processed and put in the outputfile. I mostly use it for a delete statement as to clear table entries before I add them
Number of entries: n
Optional
>NUMCOLS
Number of columns in the table
Mandatory
>COLNAME
The names of the columns in the table. Start definition in the B-Column of the sheet and go left for every new table-column. Make sure that numcols equal the number of names defined.
Number of entries: 0 - 25
Optional, but really not advisable to leave out. This is where the tool was written for.
>TYPE
The type of the entry in the table. If numeric the entry will not get quotes in the insert statement and vice versa. So don't worry if you have a timestamp or something.... just make it type C and it will get quotes.
Values allowed:
- N(umeric) = default
- C(har)
>FORMULA
This one I added later. By default this tool reads the real value of a cell. This can be cumbersome if you make full use of the power of the spreadsheet. Normally if you use formulas to quickly create data for your table this tool would read the formula and not the result of the formula. If you define the column as Y than all entries in that column will be processed so as to get the result of the formula and not the formula itself!
Note that the processing of formulas is a bit slower so if it's not necessary than don't use it.
Values allowed:
- Y(es)
- N(o) = default
- Empty (default value will be taken)
Optional
>VALUE
The real values of the table-fields! These values will be processed and per row an insert statement will be created!
Number of entries: n
Optional, but really not advisable to leave out. This is where the tool was written for.
Defines the end of Processable data. The tool will stop processing and the open(ed) sheet document will be closed.
Optional. If not defined the tool will try 24 more rows after the first empty cell in the A-column and then quit. The sheet file will stay open!
Notes
The first row of the sheet will not be read by my proggy! You can use it as a comment line!
Between Data-lines it is possible to add a maximum of 25 comment lines. If more are added the tool will quit.
Empty cells in the >Value-rows will by default get a NULL value
Tools
Elf proef
Checks if dutch bank account numbers are correct.
Julian date
Returns a julian date (day of year) froom a given date.
History
v2.01
added Tool - Elf proef
added Tool - Julian date
v2.00
Epoc look and feel. Toolbar and stuff.
Option for creating an empty processable sheet
some tuning
v1.01
fixed some bugs
added documentation
wishlist
v1.00
first release as app
Wishes
(Wishes can be mailed to: ivo.psion@hetnet.nl)
Multiple tables in one sheet
Errorhandling does not handle all at this time
create sheet enhancements
Processing of an inactive sheet file
Sketch
&Paint.app
Sheet
Comments may not be started in the A-collumn
A maximum of 25 rows can be used as comment between processable data!
Some DefinitionKeyWord in the A-collumn are optional but if used it must be in the given order!
>TABLE
&TableName
&>PROLOGUE
>DELETE FROM ...
&>PROLOGUE
"WHERE...
&>PROLOGUE
&>PROLOGUE
">NUMCOLS
JNumber of collumns
">COLNAME
>Name of DB2 col
>TYPE
N/C/NULL = Numeric (=default) or Char
">FORMULA
Y/N/NULL = Yes or No (= default)
>VALUE
2DB2 Value...
>VALUE
>VALUE
>For a quick end?
Arial
Sheet1
&Sheet.app
Swiss
"Word.app
Install Sheet2SQL.doc in c:\documents?7
&Sheet2SQL
c:\v2(02)\Sheet2SQL.opl=
SCRWID%
SCRHGHT%
MENUPOS%
TBARLINK
Initiating(
z:\system\opl\Toolbar
LoadMyProcsO
WIDMAIN%
EVENT&
EVTYPE&
HOTKMOD%
EXITHK%
THREADID&
COLLS$
COLTEL%
C_COLS%
DEFINITIONS$
DEFTEL%
C_DEFS%
TYPES$
TYPETEL%
VALUES$
VALUETEL%
FORMULA$
FORMULA%
TABLENAME$
STOP%
FOUTJE%
VERWERK$
C_LOOP%
LOOP%
FILEIN$
MESSAGE$
SQLPATH$
x SQLFNAME$
GETCLIP$
z PREVIOUS&
APPINIT
$v#Mv
MPCHECK
INITTBAR
CHECKINI
WIDMAIN%
HOTKMOD%
EXITHK%
PREVIOUS&
COLTEL%
TYPETEL%
VALUETEL%
STOP%
FOUTJE%
VERWERK$
C_LOOP%
MESSAGE$
DEFINITIONS$
C_DEFS%
c:\system\apps\+ Sheet2SQLK
Finished procesing...
>TABLE
+ >PROLOGUE
>NUMCOLS
>COLNAME
>TYPE
>FORMULA
>VALUE
NEXTEV%
EVTYPE&
EVENT&
FILEIN$
SQLPATH$
MESSAGE$
+&C:\System\apps\sheet2sql\sheet2sql.iniW
+&C:\System\apps\sheet2sql\sheet2sql.ini+$ SELECT InFolder,OutFolder FROM InitK
OUTF$
INF$#
OUTF$#
CheckIni: Something went wrong.
+&C:\System\apps\sheet2sql\sheet2sql.ini
GETNEXTDOWN
LOOP%
GETCLIP$
DEFINITIONS$
DEFTEL%
C_DEFS%
C_LOOP%
STOP%
FOUTJE%
MESSAGE$
VERWERK$
+!Finished: - No >END was defined -
GETNEXTCELL
GETCLIP$
TABLENAME$
NoTableName!
GETNEXTCELL
GETCLIP$
GETNEXTCELL
C_COLS%
GETCLIP$
MESSAGE$
STOP%
+,Program Terminated! >NUMCOLS is not numeric!
GETNEXTCELL
COLTEL%
GETCLIP$
COLLS$
C_COLS%
NoColName!
GETNEXTCELL
COLTEL%
GETCLIP$
TYPES$
C_COLS%
GETNEXTCELL
COLTEL%
GETCLIP$
FORMULA$
C_COLS%
READFORMULA
GETNEXTCELL
COLTEL%
TABLENAME$
COLLS$
C_COLS%
FORMULA$
GETCLIP$
TYPES$
INSERT INTO
VALUES (
NULLC
NULLC
SENDESC
SENDCTRLE
MPISOPEN&
SENDGOTOA1
MPPATHSPLIT$
FILEIN$
SQLPATH$
STOP%
THREADID&
SQLFNAME$
FOUTJE%
MESSAGE$
Open Sheet...
Sheet,Folder,DiskO
OutPut Path...
Folder,DiskO
+&C:\System\apps\sheet2sql\sheet2sql.ini
+&C:\System\apps\sheet2sql\sheet2sql.ini
&+&C:\System\apps\sheet2sql\sheet2sql.ini+" FIELDS InFolder,OutFolder TO InitK
OUTF$
INF$'
OUTF$'
Sheet
.SQLK
+!Alert! - No Sheet was open(ed)...
+$ERROR! - Save of ini file went wrong
FOUTJE%
SQLFNAME$
STOP%
Julian date...
Date:O
Year:
Day: K
MPWRITETOCLIPBOARD
1000000001
ElfProof -
by Ivo Woltring
Elfproef na elke enter...
Rekeningnr.:)
Elfproef teller positie:+
10,9,8,7,6,5,4,3,2,1
O |RJ
Not a valid rekeningnumberO
CopiedO
Rekeningnummer te groot...O
1000000001
SENDESC
SENDRIGHTARROW
READCELL
SENDESC
SENDFNHOME
SENDDOWNARROW
READCELL
SENDESC
SENDRIGHTARROW
SENDCTRLC
SENDCTRLF
SENDCTRLV
SENDSHIFTFNHOME
GETCLIP$
GETCLIP$
MPWRITETOCLIPBOARD
SENDESC
SENDENTER
SENDSHIFTFNHOME
SENDCTRLC
GETCLIP$
GETCLIP$
THREADID&
{O {c
IO {c
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
THREADID&
MPWRITETOCLIPBOARD
SENDESC
SENDCTRLP
SENDCTRLV
SENDENTER
THREADID&
{O {O {_
MPREADCLIPBOARDBUFFER&
MPPATHSPLIT$
TBARINIT
TBARBUTT
TBARSHOW
SCRWID%
SCRHGHT%
EXITHK%
Button1.mbmKO
Button2.mbmKO
Button3.mbmKO
Button3.mbmKO
Button4.mbmKO
Button4.mbmKO
DolphinO
AboutO
ExitO
EXITHK%
TBMENUSYM%
MENUPOS%
HOTKMOD%
File+
Create SQLOc+
Create SheetOCh+
Close
Tools+ Elf ProefOp+
Julian dateOj
View+
Show toolbarOt
About + Sheet2SQLKOa
OchD`[*
Bug: Proc
%:,KW
CMDA%
MPLOGO
Author: Ivo Woltring
Mailto: ivo.psion@hetnet.nl
+)Convert a sheet to SQL Insert statements!
Sheet 2 SQLO
MPPATHSPLIT$
MPWRITETOCLIPBOARD
SENDCTRLV
SENDCTRLSHIFTW
SENDENTER
SENDCTRLT
SENDCTRLSHIFTT
SENDCTRLSHIFTM
SENDDOWNARROW
SENDRIGHTARROW
SENDFNHOME
SENDCTRLE
FILEIN$
THREADID&
DEFINITIONS$
Create Options...
Make file,Folder,DriveO
Table Name:O
Prologue lines:O
Table fields:O
Value Lines:O
Tablename is mandatoryO
File already existsO
Sheet
Copyright
Ivo WoltringO
+,Finished Processing... Edit it with sheet...O
ELFPROEF
INITIALIZE
READFIRST
MPEDITOR%
MPLOGO
STOP%
FOUTJE%
DEFTEL%
VERWERK$
SQLFNAME$
MESSAGE$
Creating SQL inserts...
Sql Insert statements...O
+"Terminated! - Procedure not found!
Sheet 2 SQLO
+)Convert a sheet to SQL Insert statements!O
Author: Ivo WoltringO
EXITHK%
JULIANDATE
TBARHIDE
TBARSHOW
TBVIS%
MPLINK
MyProcs.OPMO
OZ4[A
:\System\OPM\MyProcs.opmK
+,MyProcs
by Ivo Woltring is not installed!W8
SetGlobalsO
MPVERSION
Version
of MyProcs
is installed.K+#Please upgrate MyProcs
to version *=
or higher.KW8
CMDE%
TBAROFFER%
OFFRCMD%
EVENT&
WIDMAIN%
HOTKMOD%
{A\[
{A`[A
LOADTBAR,
SETGLOBALS
APPINIT
CHECKINI
READFIRST+
TABLE
PROLOGUE
NUMCOLST
COLNAME
FORMULA?
VALUE
INITIALIZE
JULIANDATE
ELFPROEF
GETNEXTCELL
GETNEXTDOWNi
READFORMULA
READCELL
SENDSHIFTFNHOME1
SENDCTRLSHIFTM
SENDCTRLSHIFTT
SENDCTRLSHIFTW.
SENDCTRLP
SENDCTRLF
SENDCTRLG
SENDCTRLTf
SENDCTRLV
SENDCTRLE
SENDCTRLCJ
SENDESC
SENDENTER
SENDDOWNARROW-
SENDRIGHTARROWx
SENDGOTOA1
SENDFNHOMEF
GETCLIP$
INITTBAR
OFFRCMD%@
CMDTBDOWNA%
CMDA%:
CMDSC%
CMDP%
CMDC%(%
CMDE%!'
CMDJ%c'
CMDT%
LOADMYPROCS
MPCHECK
NEXTEV%
SYSTEM\
SYSRAM1
SYSTINFO
LICENCE AGREEMENT
YOU SHOULD CAREFULLY READ THE FOLLOWING
LICENCE AGREEMENT BEFORE INSTALLING THIS
SOFTWARE PROGRAM. BY INSTALLING, COPYING,
OR OTHERWISE USING THE SOFTWARE PROGRAM,
YOU AGREE TO BE BOUND BY THE TERMS OF
THIS AGREEMENT. IF YOU DO NOT AGREE TO
THE TERMS OF THIS AGREEMENT, TAP NO.
Ivo Woltring (called "the author" in this agreement) hereby grants
you, and by using the software you accept, a limited licence
subject to the terms and conditions contained hereinto the use
of the software in this package, (called "the Software" in this agreement).
You may install the software on an unlimited number of machines,
no payment is required for its use. Written permission must be
obtained from the author if you wish to distribute the Software
on the Internet, on CD, or by any other means.
You acknowledge the rights to the Software are the sole and
exclusive property of the author. By accepting this agreement,
you do not become the owner of the Software in accordance with
this agreement.
The software including all related program files may not be
altered inany way. Reverse translation is expressly forbidden.
The author cannot and does not accept any liability for the
unlikely event of an error, defect or failure of the software
including any loss of anykind. No guarantee is either offered
or implied by the author.
Use of this software is entirely at your own risk.
(.txt)
