SQLExplorer: Error parsing time

SQLite

I could not find any mention of this error with SQL Explorer (http://eclipsesql.sourceforge.net/)
but in some circumstances when using a DATETIME field - you can end up with an "Error parsing date" like this:

And it is not an issue with the SQLite driver (https://bitbucket.org/xerial/sqlite-jdbc/downloads/)

Assuming you have a table TEST:

CREATE TABLE TEST(TEST_ID INTEGER PRIMARY KEY AUTOINCREMENT,TEST_TIME DATETIME default "datetime('now')",TEST_LOG TEXT) ;

where you can add records like these:

insert into TEST (TEST_LOG) values ("TEST DATE ERROR PARSING")

If you attempt to do a simple select - you will get the error "error parsing date":

select TEST_ID, TEST_TIME TEST_DATE, TEST_LOG from TEST;

This command is perfectly valid under SQLite, or dBeaver, or another SQL client front end - but SQL Explorer (on Windows) may return this error parsing date.

Workaround

Now the workarounds:

select TEST_ID, strftime("%Y-%m-%d %H:%M:%S", TEST_TIME) TEST_DATE, TEST_LOG from TEST;

Another option is to use:

select TEST_ID, datetime(TEST_TIME) TEST_DATE , TEST_LOG from TEST;

SQLite: Loop simplified

As I was discussing this last tip using a simile loop in SQLite to populate data entry http://blogmymix.blogspot.com/2017/06/sqlite-using-table-to-loop.html

A comment was made that the tRange table was not absolutely necessary
as the following command would suffice:

insert into tTarget(i)  select ('My test without tRange: ' || tIndex.id)  from tIndex where tIndex.id <=10000 ;

or with proper formatting

insert into tTarget(i)  
  select ('My test without tRange: ' || tIndex.id)  
  from tIndex where tIndex.id <=10000 ;

SQLite: Using a table to loop

Loop

You want to create a table with lots of records for testing purposes - in SQL you could make a loop using something like this:

while (@i <= 10000)
 begin
  insert into table1 values ( 'field1' + cast (@i as char).
 'field2'+ cast (@i as char).
...
)
set @i+=1
end
go

Unfortunately in SQlite loops are not implemented - but you could use this trick instead
It will use a tIndex table, and a tRange table to generate some records in a tTarget table.

The tTarget table  is for the result:
create table target (i text);

This table will receive the result as text

tRange table

create table tRange (start, finish);
insert into tRange (1, 100) ;

And then afterward you can change the start and finish parameters using:
update tRange set start = 3 ;
update tRange set finish = 90 ;

tIndex table

This one is tricky because it has to contain as many records as possible - you will have to create this table via a Java program (see previous post) - by default there will be 10,000 records.

Now the Loop trick 

insert into tTarget(i)
select ('Record number '||tIndex.id)
from tIndex join tRange
on (tIndex.id >= tRange.start and tIndex.id <= tRange.finish) ;

This will generate Record Number + start ...  up to Record Number+ finish

Java: Creating a table with 10,000 records in SQLite

Linux: xfce panel setings (advanced)

Windows Buttons panel settings

Nothing special here

Separator settings

This particular settings on the separator will prevent the "windows buttons" and the next notifications settings on the panel bar to move around while the other one expand - (since the Windows buttons item will inevitably grow during the session activity)

By ticking "expand" on the separator you are making sure - the "windows Button" item behavior will not impact the rest of the items on the panel

Indicator settings

Just standard - nothing special either

Linux: xfce panel settings

VMware: How to merge vmdk files

Multiple vmdk file

You might end up in situation where you will find xxx-000001.vmdk , xxx-000002.vmdk and so on (these are quite big files) in your virtual machine (vm) folder, they are usually accompanied with .vmsd or Snapshot1.vmsn files

Something like this:
28/04/2017  16:31           210,055 vmware-0.log
27/04/2017  17:02           201,055 vmware-1.log
27/04/2017  12:20           205,308 vmware-2.log
11/05/2017  10:41           192,162 vmware.log
06/02/2017  10:52             4,380 vprintproxy-0.log
02/02/2017  17:06             4,380 vprintproxy-1.log
02/02/2017  16:19             4,383 vprintproxy-2.log
06/02/2017  11:44             4,380 vprintproxy.log
11/05/2017  10:41    14,624,489,472 virtual_machine-000002.vmdk
02/03/2016  16:11        43,488,006 virtual_machine-Snapshot1.vmsn
11/05/2017  10:41             8,684 virtual_machine.nvram
02/03/2016  16:09    14,404,091,904 virtual_machine.vmdk
04/03/2016  13:39               471 virtual_machine.vmsd
11/05/2017  10:41             4,121 virtual_machine.vmx
04/03/2016  13:39             3,300 virtual_machine.vmxf

Merging the vmdk 

First thing you need to do is backup the entire vm folder!

There is a tool to merge these vmdk - but it belongs to VMware Workstation, so if you only have VMware player you will have to download this tool from Vmware at this link

https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1023856

You will have to move this file into c:\Program Files (x86)\VMware\VMware Player\vmware-vdiskmanager.exe

otherwise it will dump with this error:

SSLLoadSharedLibrary: Failed to load library libeay32.dll:126

Win32 object usage: GDI 4, USER 1

After you will have to move into the vm folder and launch the utility as:

C:\Users\user1\Documents\Virtual Machines\vm11>"c:\Program Files (x86)\VMware\VMware Player\vmware-vdiskmanager.exe" -r "virtual_machine-000002.vmdk" -t 0 singleDiskFile.vmdk

Creating disk 'singleDiskFile.vmdk'

  Convert: 1% done.

The conversion will take some time to write the new vmdk file, when it is finished you can delete all the previous vmdk and snapshot file and rename it as the previous name.

the result will be something like this:

11/05/2017  12:52    21,381,054,464 virtual_machine-000002.vmdk

11/05/2017  12:52             8,684 virtual_machine.nvram

11/05/2017  12:47                 0 virtual_machine.vmsd

11/05/2017  12:52             4,202 virtual_machine.vmx

11/05/2017  12:47               274 virtual_machine.vmxf

I also removed the log files during the process - but that is totally optional

SQLite: showing a random record from a table

Random record

If many occasion developer want to get a random record(s) from a table, because they are programming a quizz, or a game - it turns out there is no native instructions for that in SQLite
but you can use this workaround:

SELECT
*, random() as R
FROM Table
ORDER BY R
LIMIT 1 ;

Why it works

It insert a random number (R) for each record and then using the order by there is going to be a selection of the smaller number (R)  - you can even change the LIMIT to get more than one record - let say 3

SELECT *, random() as R FROM Table ORDER BY R LIMIT 3 ;

Seamonkey: How to watch Youtube video the old fashion way

Youtube is now using html5

This is great obviously, because it means by default the internet browser doesn't need any plugin.
But let say you want to read your video the old fashion way using adobe Flash
well you actually can ... just by adding the url &nohtml5=1

for example:
https://www.youtube.com/watch?v=4h_OSURFicY&nohtml5=1

This will open the video and use adobe flash (instead of html5)
if you are not sure - just right click on the video and you should see this:

Seamonkey: Enabling full-screen for Youtube video

Seamonkey default settings

For some reasons the internet browser Seamonkey decided to prevent people to open full screen YouTube video - maybe they were tired of complaints of user who couldn't not exit the video (by simply pressing Escape?!)

Anyway now by default the full-screen button on the lower-right is disabled.

Overriding the setting

in the url path type: about:config then confirm that you know what you are doing

then look for full-screen-api.enabled and set this to true

That is it

Sqlite: Using auto_increment and current date and time

The idea

As a good tip to get the most of the table entries - you want the user to focus on their important data - but you don't want to overwhelm them with extra typing that can be done automatically by database system.

Here is way to have an auto increment index and a default date and time of the entry done by the operator

Embedded during the table creation: 

sqlite> create table testauto (autoid integer primary key autoincrement, autodate default (datetime('now')), notes text ) ;

Operator just have to enter the notes column: 

sqlite> insert into testauto (notes) values ('this is a test of the auto increment and default date' ) ;
sqlite> insert into testauto (notes) values ('this is a second test of the auto increment and default date' ) ;
sqlite> insert into testauto (notes) values ('this is a third test of the auto increment and default date' ) ;

and as you see the index and the date are automatically filled

sqlite> select * from testauto ;
1|2017-03-15 15:34:05|this is a test of the auto increment and default date
2|2017-03-15 15:34:14|this is a second test of the auto increment and default date
3|2017-03-15 15:34:24|this is a third test of the auto increment and default date
sqlite>

Sqlite: how to generate a random number within range 0..99

Random()

The Sqlite random() function will returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

abs() will returns the absolute value of the numeric argument

floor() or round() will return a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, it is assumed to be 0.

NB:floor() will only work within SQLExplorer, Dbeaver, ...

Result

select floor(abs(random()/92233720368547758.07));
will generate within 0..99

and
select round(abs(random()/92233720368547758.07),0);
will generate within 1..100

Sqlite: how to count words in a field

Assuming a table like this:

create table test (id integer primary key autoincrement, name text);

and values like:

insert into test values (null, "test") ;
insert into test values (null, "word1 word2 word3")  ;
insert into test values (null, "word4 word5 word6")  ;
insert into test values (null, "word7 word8 word9 word10 word11 word11")  ;

here is the result:

select id, name from test

1;test
2;word1 word2 word3
3;word4 word5 word6
4;word7 word8 word9 word10 word11 word12

Counting

Now you want to count the word(s) in the field "name" - here is how to proceed:
select (length(name)-length(replace(name," ","")))+1 as wordcount, name from test

it works because the query is looking for the number of space - a space means a new word is in the field and that is why you need to add 1 to the total.

Sqlite is awesome