WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

SQL Syntax Tool

Required file: [dbsql_adt.zip] 

;Winbatch - 2007c - Display SQL syntax for common tasks
;                   for different Database Engines
;
;very simple script, displays information in a multi-edit
;box. Database is xml persisted as .adt
;
;Stan Littlefield - April 27, 2007
;///////////////////////////////////////////////////////////////////////////////////////////////////////
GoSub udfs
IntControl(73,1,0,0,0)
cXML = DirScript():"dbsql.adt"
If ! FileExist(cXML) Then Exit
cConn = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="%cMDB%"'
oRS=CreateObject("ADODB.Recordset")
cL="SQLITE,DB2,ACCESS,POSTGRES,ORACLE,SQL SERVER,MYSQL,MIMER,SYBASE"
cC=StrReplace(cL,",",@TAB)
cC2 = ItemExtract(1,cC,@TAB)
cD=crprcs()
cD2 = ItemExtract(1,cD,@TAB)
oRS.Open(cXML,"Provider=MsPersist",1,4,256)
cInfo=""

:start
cC1=cC
cD1=cD
SQLFormat=`WWWDLGED,6.1`

SQLCaption=`SQL Syntax for selected Databases`
SQLX=9999
SQLY=9999
SQLWidth=352
SQLHeight=167
SQLNumControls=006
SQLProcedure=`DEFAULT`
SQLFont=`Microsoft Sans Serif|6144|70|34`
SQLTextColor=`0|0|0`
SQLBackground=`DEFAULT,DEFAULT`
SQLConfig=0

SQL001=`093,147,036,012,PUSHBUTTON,DEFAULT,"Get Info",1,1,32,DEFAULT,DEFAULT,DEFAULT`
SQL002=`223,147,036,012,PUSHBUTTON,DEFAULT,"Cancel",2,2,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SQL003=`003,005,338,012,STATICTEXT,DEFAULT,"Select a Database Engine and a Process from drop-downs then click Get Info",DEFAULT,3,512,DEFAULT,DEFAULT,DEFAULT`
SQL004=`005,025,064,068,DROPLISTBOX,cC1,"%cC2%",DEFAULT,4,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SQL005=`075,025,266,132,DROPLISTBOX,cD1,"%cD2%",DEFAULT,5,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SQL006=`007,047,334,092,MULTILINEBOX,cInfo,DEFAULT,DEFAULT,6,DEFAULT,DEFAULT,DEFAULT,DEFAULT`

BP=Dialog("SQL")

If BP==2 Then Goto End

cC2=cC1
cD2=cD1
cInfo = "No Information for those choices."
oRS.MoveFirst()
;oRS.Open("SELECT info FROM dbsql WHERE process='%cD1%' AND db='%cC1%'",cConn,1,3,1)
oRS.Filter = "process='%cD1%' AND db='%cC1%'"
If ! oRS.eof() Then cInfo=oRS.collect("info")
oRS.Filter = ""
Goto start

:End
If oRS.state<>0 Then oRS.Close()
oRS=0
Exit

:WBERRORHANDLER
If oRS.state<>0 Then oRS.Close()
oRS=0
ErrorProcessing(1,1,0,0)
Exit
;///////////////////////////////////////////////////////////////////////////////////////////////////////

:udfs
#DefineSubRoutine crprcs()
cvar=""
cvar = cvar:"Add a column to a table":@TAB
cvar = cvar:"Add a constraint to a table.":@TAB
cvar = cvar:"Components of a date: such as the year or the month":@TAB
cvar = cvar:"CREATE a new table":@TAB
cvar = cvar:"CREATE a table with an autonumber / sequence / identity / autoincrement":@TAB
cvar = cvar:"CREATE a VIEW.":@TAB
cvar = cvar:"CREATE TABLE problems: Foreign key references.":@TAB
cvar = cvar:"CREATE TABLE problems: Insufficient privileges.":@TAB
cvar = cvar:"CREATE TABLE problems: Invalid column name.":@TAB
cvar = cvar:"CREATE TABLE problems: Table already exists.":@TAB
cvar = cvar:"CREATE TABLE with a composite primary key":@TAB
cvar = cvar:"CREATE TABLE with a foreign key":@TAB
cvar = cvar:"Date and time types.":@TAB
cvar = cvar:"Delete a column from a table":@TAB
cvar = cvar:"DROP an unwanted table":@TAB
cvar = cvar:"DROP TABLE problems: Foreign key references.":@TAB
cvar = cvar:"Format a date and time.":@TAB
cvar = cvar:"Full text search with SQL? such as i want to find a specific word in any colum of the table":@TAB
cvar = cvar:"Group by day of the week (using arithmetic)":@TAB
cvar = cvar:"Group by day of the week (using date functions)":@TAB
cvar = cvar:"How can I display a column name for an aggregate function?":@TAB
cvar = cvar:"How can I select the oldest person in the table PERSON by birthday?":@TAB
cvar = cvar:"how do I use 'like' in a sql select statement":@TAB
cvar = cvar:"How do you use Equi Join to join two tables with the same name?":@TAB
cvar = cvar:"How to build a statement on a word with an Apostrophe such as WHERE name='Tom's Book'":@TAB
cvar = cvar:"How to format a date in the like yyyymmdd":@TAB
cvar = cvar:"Intervals of time.":@TAB
cvar = cvar:"make union between different tables to build one single view or request?":@TAB
cvar = cvar:"Match a range of dates":@TAB
cvar = cvar:"Put two fields together (concatenate)":@TAB
cvar = cvar:"Rename column":@TAB
cvar = cvar:"SELECT a column whose name contains spaces?":@TAB
cvar = cvar:"Select a record with the latest date":@TAB
cvar = cvar:"Specify a date (a date literal).":@TAB
cvar = cvar:"Subtract dates"
Return(cvar)
#EndSubRoutine

#DefineSubRoutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If VarType(Err_Array) ==256
   WbError = Err_Array[0]
   wberrorhandlerline = Err_Array[1]
   wberrorhandleroffset = Err_Array[2]
   wberrorhandlerassignment = Err_Array[3]
   wberrorhandlerfile = Err_Array[4]
   wberrortextstring = Err_Array[5]
   wberroradditionalinfo = Err_Array[6]
   wberrorinsegment = Err_Array[7]
Else
   WbError = LastError()
EndIf
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
If deleteIni
   FileDelete(WbErrorFile)
   IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
EndIf
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(DirScript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
   Display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
   If showerr
      WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
      Message("An Error Was Encountered",WbErrorMsgText)
   EndIf
EndIf
Return(1)
#EndSubRoutine

Return
;///////////////////////////////////////////////////////////////////////////////////////////////////////


Article ID:   W17199

File Created: 2019:08:14:09:27:30
Last Updated: 2019:08:14:09:27:30