• Welcome, Guest. Please login.
 
August 19, 2019, 07:28:33 am

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

691
Just a thought for perhaps some later date.
An option to create a temp file without returning a record set.
The client would browse the temp file using slGet.



See post below. 
Thanks for examples, Fred!
692
Thank you!
The  answers really helped.
It looks like the blob for transactions is  just another column with variable length data in it.
There are so many transactions it  tremendously reduces the number of index entries.

The pictures will go into another table and I won't create another database (good news.)
It is great being able to prototype so easily without a single input form.

I'm  considering dynamic html input forms which would make everything dynamic and give
the ability to  modify the system on the fly remotely by uploading html code.
Html forms are dynamic and allow so much more than dialogs.

The dropped connections test in another thread shows that it is very simple to just reconnect, reopen
and reissue  SQL statements from a single function if an error occurs.
No need to stay connected.






693
Yes, I agree with all comments, thank you.

I was trying to write a program that handles all the Tsunami op codes.
Maybe better off just thinking in SQLite.


694
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?


695
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?
696
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
697
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
                     
698
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


699
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 
700
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?


























701
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.

702
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:
703
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.)
704
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;
705
Fred or someone else may have to answer this.
When a client ends the server terminates the connection.
My answer is no problem..