• Welcome, Guest. Please login.
 
September 22, 2019, 02:12:43 pm

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - cj

706
1) Invoice structure suggestions?   Looking for a good create statement.
    Currently do this with a variable length structure which is very efficient:

    Clientnum&+ Patient& + Datenum& + Invoice&+TotalAmt& +TotalTax& + blob*
    *blob is repeating transactions:  blob =blob + (code$+date&+qty&+amt&+tax&+otherstuff)

    key1 = client& + patient& + inv&   (unique)
    key2 = inv& + client& + patient&   (unique)
    key3 = date& + inv&                     (not unique, may be multiple patients per invoice) 

    Have a structure you like?  It doesn't have to be as efficient nor use blobs..
    Can't simply view data in a blob like other columns.  Would be nice.

2) Should pictures be in another database to speed up processing SELECT * statements?
3) Should money be stored as REAL instead of INTEGER (get rid of home grown routines?)
4) Should I stop using Julian dates and use SQL dates? 
5)
Is it a good idea to make all column names unique by prefixing or suffixed them with a table name?

Client_ClientNumber    or   ClientNumber_Client
Patient_ClientNumber  or   ClientNumber_Patient
Invoice_ClientNumber  or   ClientNumber_Invoice

6) Does anyone use basic file processing on remote connections using slGetFile and slPutFile?
Have not looked into how to add my own procedures, yet.  Basic I/O on remote files?


707
Like the program a lot!

These are only suggestions:
Ability to use keyboard to navigate to first,last,previous,next.
First and last records are blank instead of showing first and last records with data.
Display dates like 11-10-2009 instead of 20091110
Some masked edit for fields (totally optional, fine as is.)
Ability to search on any column
Ability to sort on any combination of columns ascending or descending
Activate print button.
Print preview
Add first, next, previous and next buttons or menu items.
ListView at bottom might be changed to read only

With over 600 views and 178 downloads, I'm the only one to make a comment?
708
slGetStatus does not keep connections alive
All 4 connections were dropped at 04:37:49

These 4 connections were "pinged" every 300 seconds (5-minutes)

Reconnect, re-open worked fine when "No" was returned
I will keep these connections running to see results of the next drop
Could the drop at 04:37:49 been prevented?

111028 013215 Conn #22 SK 440 192.168.1.6  internet
111028 013926 Conn #23 SK 440 192.168.1.6  internet
111028 014740 Conn #24 SK 440 192.168.1.6  intranet
111028 014842 Conn #25 SK 440 192.168.1.6  intranet
111028 043749 Dcon #25 Dropped                    intranet
111028 043749 Dcon #24 Dropped                    intranet
111028 043749 Dcon #22 Dropped                    internet
111028 043749 Dcon #23 Dropped                    internet
111028 043913 Conn #26 SK 440 192.168.1.6
111028 043928 Conn #27 SK 440 192.168.1.6
111028 044216 Conn #28 SK 440 192.168.1.6
111028 044241 Conn #29 SK 440 192.168.1.6


It is now 11:34 AM CST and all four application still work
The sqliteningserver.log now looks like this:

111028013215 Conn #22 SK 440 CJRIGHT192.168.1.6
111028013926 Conn #23 SK 440 CJRIGHT192.168.1.6
111028014740 Conn #24 SK 440 CJRIGHT192.168.1.6
111028014842 Conn #25 SK 440 CJRIGHT192.168.1.6
111028043749 Dcon #25 Dropped
111028043749 Dcon #24 Dropped
111028043749 Dcon #22 Dropped
111028043749 Dcon #23 Dropped
111028043913 Conn #26 SK 440 CJRIGHT192.168.1.6
111028043928 Conn #27 SK 440 CJRIGHT192.168.1.6
111028044216 Conn #28 SK 440 CJRIGHT192.168.1.6
111028044241 Conn #29 SK 440 CJRIGHT192.168.1.6
111028112346 Conn #30 SK 440 CJRIGHT192.168.1.7
111028112430 Conn #31 SK 440 CJRIGHT192.168.1.7
111028112525 Dcon #27 WentAway
111028112612 Dcon #28 WentAway
111028112637 Dcon #29 WentAway
111028112651 Dcon #26 WentAway
111028112718 Conn #32 SK 440 CJRIGHT192.168.1.7
111028112743 Conn #33 SK 440 CJRIGHT192.168.1.7

4 connections  have Dropped and WentAway, so far.

             Dcon -- followed by # and the TCP file number and one of the following:
                     Disconnect = Client issued slDisconnect.
                     Dropped    = Client process ended and the client OS correctly notified the server of that event.
                     WentAway   = Client process ended but the client OS did not notify the server of that event.
                     TimeOut    = Client is inactive (no real messages) longer than the ConnectionTimeOut value in the config file.
                     Killed     = Connection was ended via SQLiteningServerAdmin.
                     Error      = Unrecoverable error occurred.


111028 013215 Conn #22 SK 440 CJRIGHT192.168.1.6
111028 013926 Conn #23 SK 440 CJRIGHT192.168.1.6
111028 014740 Conn #24 SK 440 CJRIGHT192.168.1.6
111028 014842 Conn #25 SK 440 CJRIGHT192.168.1.6
111028 043749 Dcon #25 Dropped
111028 043749 Dcon #24 Dropped
111028 043749 Dcon #22 Dropped
111028 043749 Dcon #23 Dropped
111028 043913 Conn #26 SK 440 CJRIGHT192.168.1.6
111028 043928 Conn #27 SK 440 CJRIGHT192.168.1.6
111028 044216 Conn #28 SK 440 CJRIGHT192.168.1.6
111028 044241 Conn #29 SK 440 CJRIGHT192.168.1.6
111028 112346 Conn #30 SK 440 CJRIGHT192.168.1.7
111028 112430 Conn #31 SK 440 CJRIGHT192.168.1.7
111028 112525 Dcon #27 WentAway
111028 112612 Dcon #28 WentAway
111028 112637 Dcon #29 WentAway
111028 112651 Dcon #26 WentAway
111028 112718 Conn #32 SK 440 CJRIGHT192.168.1.7
111028 112743 Conn #33 SK 440 CJRIGHT192.168.1.7
-------------------------------------------------

Ping counter now at 189.  slGetStatus is sent every 5-minutes.
:)

Another drop occurred at: 16:37:49 and autoreconnect still working
111028145659 Dcon #44 Disconnected
111028145816 Conn #45 SK 440 CJRIGHT192.168.1.7
111028145930 Dcon #45 Disconnected
111028150252 Conn #46 SK 440 CJRIGHT192.168.1.7
111028151740 Dcon #46 Disconnected
111028151812 Conn #47 SK 440 CJRIGHT192.168.1.7
111028163749 Dcon #31 Dropped
111028163749 Dcon #32 Dropped
111028163749 Dcon #47 Dropped
111028163749 Dcon #30 Dropped
111028163749 Dcon #33 Dropped
111028163819 Conn #48 SK 440 CJRIGHT192.168.1.7
111028163848 Conn #49 SK 440 CJRIGHT192.168.1.7
111028163932 Conn #50 SK 440 CJRIGHT192.168.1.7
111028164220 Conn #51 SK 440 CJRIGHT192.168.1.7
111028164245 Conn #52 SK 440 CJRIGHT192.168.1.7
111028165721 Dcon #48 Disconnected

Ping counter now at 373.  slGetStatus is sent every 5-minutes.
:)

Another drop occurred at: 04:37:50 and autoreconnect still working
Accidently closed one of the over the internet connections.  Now 3 active.

111029043750 Dcon #52 Dropped
111029043750 Dcon #49 Dropped
111029043750 Dcon #51 Dropped
111029043750 Dcon #50 Dropped
111029043851 Conn #61 SK 440 CJRIGHT192.168.1.7
111029043935 Conn #62 SK 440 CJRIGHT192.168.1.7
111029044224 Conn #63 SK 440 CJRIGHT192.168.1.7
111029044248 Conn #64 SK 440 CJRIGHT192.168.1.7
709
Testing to see if slGetStatus(4) keeps connections alive overnight.
Have 2 connections running over the internet and 2 running between machines..

The only time that I've seen lock-ups is when waiting to connect and clickng
on a button or attempting to close the dialog.  If this is addressed it will
be very hard to lock up a client.



SUB Ping 'only called by TIMER1
  LOCAL s AS STRING
  INCR gCounter 'not needed, just seeing how many pings are issued
  s = slGetStatus(4)
  SELECT CASE s
    CASE ""
      UpdateStatusBar "(" + FORMAT$(gCounter) + ") Ping: Returned nothing"
      tConnected = 0
    CASE "Yes"
      tConnected = 1
      UpdateStatusBar "(" + FORMAT$(gCounter) + ") Ping: Connected to " + gsIP
    CASE "No"
      tConnected = 0
      UpdateStatusBar "("+FORMAT$(gCounter) + ") Ping: Not connected"
      Connect_OpenDataBase
    CASE ELSE
      UpdateStatusBar s
    END SELECT
END SUB

SUB UpdateStatusBar(sText AS STRING)
  LOCAL zText AS ASCIIZ * 80
  zText = TIME$ + " " + sText
  CONTROL SEND ghDlg, %STATUSBAR, %SB_SETTEXT, 0, VARPTR(zText)
END SUB
                     
710
Good information, thank you.
I am doing these tests because many people leave machines on overnight and curiousity.

Wired connection to client dropped in 9h, 39m 57s or 25,797s
10-26-2011  18:57:51  Conn #29 SK 440 
10-27-2011  04:37:48  Dcon #29 Dropped     
Verified the mapped drive from the client to the server remained.

Now testing with router from client 192.168.1.7 to server 192.168.1.2
10-27-11 06:36:04 Conn #30 SK 440 192.168.1.7


711
This connection (over the internet) lasted almost 7 hours.
The connection was wireless.
10-26-11 09:58:32 Conn #25 SK 440   192.168.1.4
10-26-11 16:37:47 Dcon #25 Dropped
The connection to the mapped drive was also lost.  This was NOT a sqlitening issue.
-----------------------------------------------------------------------------------------------------------------
Rebooted client and now testing without a wireless adapter.  Server is rock solid.
111026185751 Conn #29 SK 440 192.168.1.7 
712
Thank you, Fred!

Here are some more things to consider:

1) A wireless adapter may connect when you expect a wired connection.
2) The power management property "Allow this computer to turn off this device to save power" might be 
checked on any network adapter and the server will later correctly drop the connection.

This "SQLiteningServer.log" shows the last connection last night was wireless on "192.168.1.6".
The connection was expected on the wired connection "192.168.1.4".

11 1025 024044 Conn #8 SK 440   192.168.1.6  connect 02:40:44 AM
11 1025 043746 Dcon #8 Dropped               dropped 04:37:46 AM

I am unchecking the save power option on all adapters.

A  wireless adapter for connecting to a wireless printer can connect to SQLitening.
How would others configure to not allow a possible connection to SQLitening?


























713
Great catch and thanks Fred for the 1.54 update..

12:33:20 - 03:26:07 = 9 hours, 7 minutes, 13 seconds or 32833 seconds until disconnect.

111023 032607 Conn #16977 SK 448
111023 123320 Dcon #16977 Dropped

Note:
I need to modify client programs because the first thing I did was a select statement and
after receiving an error 18, end the program to receive a crash.  Need to add some
logic to not attempt to perform actions on the server when the connection has been lost.
This is not a big thing, but I can see where clients would call if it occurred.

714
Quote
Are you saying the default value of 30 means 30 minutes? Can we trap error -18 to prevent it from crashing the program anytime it comes up?


1) Yes, the documentation says values are in minutes and the default is 30.

2) SQLitening issues an ExiitProcess if an error occurs unless told to return errors.
I use slSetProcessMods("E0") or slSetProcessMods("E1") to return errors and process them.
In the case of errror -18 (a disconnected program ) a  slConnect then slOpen would be reissued before continuing.
Others must have a way to handle errors when Sqlitening is allowed to ExitProcess.
You mentioned your program "crashes".  It is probably being terminated by Sqlitening, right?


I have a llink to  \SqlItening\Doc\SQLitening.Chm on my desktop


slSetProcessMods (rsModChars String)

Sets the process mod flags, modes, and/or values as controlled by the passed ModChars. Some of the mods are global while some are for the open database. No error can occur.


ModChars:
715
Per manual:
ConnectionTimeOut = Number Minutes --- Control the number of minutes the server will wait to receive a message from an active connection.  Default is 30. Set to -1 if you want to never timeout.

It is in minutes, not seconds. 
1 = 1 minute (which will force users to send something to the server every 59 seconds or get timed out.)
716
Thank you for that information.
I may be abandoning some recorsets without knowing it..

This and more complex SELECT statements work fine.
SELECT * FROM CLIENT;

Should the following return a recordset?
Also,  without the transaction, I am not getting a recordset.


BEGIN;
DROP TABLE IF EXISTS CLIENT;
CREATE TABLE IF NOT EXISTS CLIENT (F1);
INSERT INTO CLIENT VALUES("HOW");
INSERT INTO CLIENT VALUES("NOW");
INSERT INTO CLIENT VALUES("BROWN");
INSERT INTO CLIENT VALUES("COW");
SELECT * FROM CLIENT;
END;


This works:
SELECT COUNT(*) FROM CLIENT;

This doesn't return anything?
BEGIN;
SELECT COUNT(*) FROM CLIENT;
END;
717
Fred or someone else may have to answer this.
When a client ends the server terminates the connection.
My answer is no problem..

718
Refreshing the FACT does not disconnect clients (which is very cool.) :D
Tried several times while changing timeout.
719
SQLiteningServer.log
111022102117 Dcon #16317 TimeOut

If nothing is sent to server within timeout period error 18's appear (as they should) on client.

720
I have a program for processing statements that works great, but wondered
if I am dropping recordsets which mix statements with SELECT statements.
All calls are using remote server.


BEGIN;
DROP TABLE IF EXISTS CLIENT;
CREATE TABLE IF NOT EXISTS CLIENT (NUMBER);
INSERT INTO CLIENT VALUES("HOW");
INSERT INTO CLIENT VALUES("NOW");
INSERT INTO CLIENT VALUES("BROWN");
INSERT INTO CLIENT VALUES("COW");
SELECT * FROM CLIENT;
END;


If the above code does return a recordset, I'm not processing it.
Perhaps more code is needed in my function executor below.
If a recordset is not processed, what happens the next time a recordset is returned?


FUNCTION Execute(OPTIONAL sTemp AS STRING) AS LONG
  LOCAL COL, columns, result AS LONG,s, sRow AS STRING

  IF VARPTR(sTemp) THEN 'passing something so skip reading from dialog
     s = LTRIM$(sTemp,ANY CHR$(0 TO 32))
  ELSE
    CONTROL GET TEXT ghDlg, %TEXTBOX1 TO s 'get text
    s = LTRIM$(s,ANY CHR$(0 TO 32))
    'CONTROL SET TEXT ghDlg, %TEXTBOX1, s  'put stripped string back into textbox

    IF LEN(s) = 0 THEN
      'nothing in dialog so add a select statement
      CONTROL GET TEXT ghDlg, %TEXTBOX2 TO s
      CONTROL SET TEXT ghDlg, %TEXTBOX1, "SELECT * FROM " + s
      EXIT FUNCTION
    END IF
  END IF

  IF UCASE$(LEFT$(s,7))= "SELECT " THEN
    IF slSel(s) THEN
      DisplayError
      FUNCTION = slGetErrorNumber
      EXIT FUNCTION  'select error
    END IF
  ELSEIF slEXE(s) THEN
      DisplayError
      FUNCTION = slGetErrorNumber
      EXIT FUNCTION 'other statment error
  END IF

  'LISTBOX RESET ghDlg, %LISTBOX
  LISTBOX ADD ghDlg, %LISTBOX, STRING$(60,"-")
  IF ISFALSE slGetRow THEN
    LISTBOX ADD ghDlg,%LISTBOX,"Success, but nothing to return"
    EXIT FUNCTION
  END IF

  columns = SlGetColumnCount
  IF columns=0 THEN
    ? "No columns"
    FUNCTION = slGetErrorNumber
    EXIT FUNCTION
  END IF

  DIM rowcount AS LONG
  DO
    INCR rowcount
    sRow= FORMAT$(rowcount) + "   "
    FOR COL = 1 TO columns
      sRow = sRow + slF(COL)+ $Delimiter
    NEXT
    LISTBOX ADD ghDlg, %LISTBOX, LEFT$(sRow,LEN(sRow)-1)
  LOOP WHILE slGetRow

  REM Listbox1.ListIndex = Listbox1.ListCount - 1  'VB6 way
  LOCAL ListboxCount AS LONG
  LISTBOX GET COUNT ghDlg, %LISTBOX TO ListboxCount
  LISTBOX SELECT    ghDlg, %LISTBOX,   ListboxCount
  LISTBOX UNSELECT  ghDlg, %LISTBOX,   ListboxCount
  'CONTROL HANDLE  ghDlg, %LISTBOX TO hList
  'ListboxCount = SendMessage(hList, %LB_GETCOUNT,  0, 0) 'Ask listbox for items count
  'SendMessage hList, %lb_setcaretindex, ListboxCount+1, 0  'yay!!
END FUNCTION