1. Matt - EuSQL stuff
Matt,
Sorry it took so long but I finally got around to working with EuSQL
some more and here's what I have found thus far:
- I have started working on a version of SQL4Less using EuSQL. I tried
implementing EuSQL into SQL4Less but you had some constants with the
same names in both includes so I opted to leave the ODBC version alone
and come up with a standalone EuSQL version.
- In my testing I have discovered that the sequence nesting of the
returned data results are different if you use 'select *' versus 'select
field_name...'. In my EuSQL4Less I issued a 'select field_name, ...'
and the logic worked fine (although I still have some formatting issues
to deal with). When I issued a 'select * against the same file my
program crashes because the sequence nesting is different.
- I tried doing a select field_name where field_name like 'jo*' and a
crash happened in eusql.e. I also tried an update statement with the
same results. Is there something specific to doing these statements
with EuSQL?
- I noticed that issuing a 'select *' returned * as the field name list.
In ODBC I believe a 'select *' returns all the field names.
So here's the first stab at EuSQL4Less:
without warning
with trace
include Win32Lib.ew
include eusql.e
global constant Main = create( Window, "EuSQL for Less", 0, Default,
Default, 590, 437, { WS_DLGFRAME, WS_SYSMENU} )
global constant SQLStatusBar = create( StatusBar, "", Main, 0, 0, 0, 0,
0 )
global constant ConnButton = create( PushButton, "Connect", Main, 488,
4, 90, 30, 0 )
global constant DiscButton = create( PushButton, "Disconnect", Main,
488, 36, 90, 30, 0 )
global constant SQLEdit = create( RichEdit, "", Main, 0, 4, 484, 136,
or_all({ES_NOHIDESEL}) )
global constant ExecSQLButton = create( PushButton, "Execute", Main,
488, 72, 90, 30, 0 )
global constant ClrHistButton = create( PushButton, "Clear History",
Main, 488, 145, 90, 30, 0 )
global constant SQLHistList = create( DropDownList, "", Main, 0, 145,
484, 112, 0 )
global constant ResultsLV = create( ListView, {""}, Main, 0, 170, 484,
220,or_all({LVS_REPORT,LVS_SHOWSELALWAYS}))
atom sv_col_count, last_rec, hstmt
sequence sv_status, sql_stmts, log_file_path
sv_col_count = 1
last_rec = 0
sv_status = {}
sql_stmts = {}
function Format(object obj)
sequence rtn_seq
rtn_seq = {}
if integer(obj) then
rtn_seq = sprintf("%d", obj)
elsif atom(obj) then
rtn_seq = sprintf("%f", obj)
else
rtn_seq = obj
end if
return rtn_seq
end function
procedure ExecuteEDSSQL()
sequence wrk_data, sql
object data, eds_rtn_val
atom rtn_code
integer ok
sql = {}
wrk_data = {}
last_rec = 0
eraseItems(ResultsLV)
if sv_col_count > 0 then
for i = 1 to sv_col_count do
rtn_code = sendMessage(ResultsLV, LVM_DELETECOLUMN, 0, i-1)
end for
end if
sql = getRichText( SQLEdit, -1 )
sv_status = getText(SQLStatusBar)
setText(SQLStatusBar, "Executing SQL statment...please wait")
repaintWindow(SQLStatusBar)
eds_rtn_val = parse_sql(sql)
if atom(eds_rtn_val) then
ok = message_box(get_sql_err(eds_rtn_val), "EDS SQL Error",
MB_OK)
return
end if
data = run_query(eds_rtn_val)
if sequence( data ) then
for i = 1 to length(data[1]) do
insertListViewColumn(ResultsLV, i, 0, 0, 60, data[1][i], 0)
end for
wrk_data = {}
for x = 1 to length(data[2]) do
wrk_data = {}
for y = 1 to length(data[2][x]) do
wrk_data = append(wrk_data, Format(data[2][x][y]))
end for
ok = addLVItem( ResultsLV, 0, wrk_data)
end for
sv_col_count = length(data[2][1])
else
sv_col_count = 0
end if
if find(sql, sql_stmts) = 0 then
addItem(SQLHistList, sql)
sql_stmts = append(sql_stmts, sql)
end if
setText(SQLStatusBar, sv_status[1])
end procedure
procedure Main_onClose ()
integer log_file
log_file = open(log_file_path, "w")
if log_file >= 0 then
for i = 1 to getCount(SQLHistList) do
puts(log_file, getItem(SQLHistList, i))
puts(log_file, '\n')
end for
close(log_file)
end if
end procedure
onClose[Main] = routine_id("Main_onClose")
procedure Main_onOpen ()
sequence dsn
integer ok,log_file
object line
setEnable(DiscButton, False)
setEnable(ExecSQLButton, False)
log_file_path = current_dir() & "\\sqllog.txt"
log_file = open(log_file_path, "r")
if log_file >= 0 then
while 1 do
line = gets(log_file)
if atom(line) then
exit
end if
line = line[1..(length(line) - 1)]
addItem(SQLHistList, line)
sql_stmts = append(sql_stmts, line)
end while
close(log_file)
end if
setText(SQLStatusBar, "Click Connect to open an EDS database")
end procedure
onOpen[Main] = routine_id("Main_onOpen")
procedure ConnButton_onClick ()
sequence user, auth, eds_file
integer ok, rtn_code
user = {}
auth = {}
eds_file = {}
eds_file = getOpenFileName(Main, "", {"EDB Files", "*.edb"})
if length(eds_file) > 0 then
rtn_code = db_open(eds_file, DB_LOCK_NO)
if rtn_code != DB_OK then
ok = message_box("Could not open file!", "File error",
MB_OK)
setText(SQLStatusBar, sv_status[1])
return
end if
rtn_code = db_select_table("TABLEDEF")
if rtn_code != DB_OK then
ok = message_box("Required TABLEDEF not found!",
"Table Error", MB_OK)
setText(SQLStatusBar, sv_status[1])
return
end if
setText(SQLStatusBar, "Connected to file: " & eds_file)
setEnable(ConnButton, False)
setEnable(ExecSQLButton, True)
setEnable(DiscButton, True)
end if
end procedure
onClick[ConnButton] = routine_id("ConnButton_onClick")
procedure DiscButton_onClick ()
integer ok
db_close()
setEnable(ConnButton, True)
setEnable(ExecSQLButton, False)
setEnable(DiscButton, False)
setText(SQLStatusBar, "Click Connect to open an EDS database")
end procedure
onClick[DiscButton] = routine_id("DiscButton_onClick")
procedure SQLEdit_onGotFocus ()
sv_status = getText(SQLStatusBar)
setText(SQLStatusBar, "Type and SQL statement and click Execute")
end procedure
onGotFocus[SQLEdit] = routine_id("SQLEdit_onGotFocus")
procedure SQLEdit_onLostFocus ()
setText(SQLStatusBar, sv_status[1])
end procedure
onLostFocus[SQLEdit] = routine_id("SQLEdit_onLostFocus")
procedure ExecSQLButton_onClick ()
ExecuteEDSSQL()
end procedure
onClick[ExecSQLButton] = routine_id("ExecSQLButton_onClick")
procedure ClrHistButton_onClick ()
eraseItems(SQLHistList)
sql_stmts = {}
last_rec = 0
end procedure
onClick[ClrHistButton] = routine_id("ClrHistButton_onClick")
procedure SQLHistList_onChange ()
sequence sql_stmt
sql_stmt = {}
setText(SQLEdit, getItem(SQLHistList, getIndex(SQLHistList)))
end procedure
onChange[SQLHistList] = routine_id("SQLHistList_onChange")
WinMain( Main, Normal )
Later...
