SQLite Handling

[Norman_P]

@322543:@Dave S

was not aware that feature was even there

Its been present for well over a decade

[DaveS]

@322561:@Norman Palardy

Its been present for well over a decade

not disputing that at all… just saying it was something I was not aware of

[Emile_Schwarz]

Application downloaded five minutes ago.

In Date fields, sometimes I have the Dates displayed as 2017-03-24, sometimes as 24-mars-2017 (in blue), etc.

Also, when I asked to display a Table with… First Name, Family Name, Photo (images as png), etc.: infinite loop ? The application stop responding.

I stopped watching there.

[Emile_Schwarz]

@322563:@Dave S

it was something I was not aware of

I too did not saw that earlier too (and to be honest, I am not sure I really understand the ins/outs).

But, thank you for highlighting that.

Edit: typos.

[Norman_P]

@322561:@Norman Palardy

Its been present for well over a decade

I should say “Its been present like this in OS X for over a decade and was predated by the implementation in OS 9”

@322563:@Dave S

not disputing that at all… just saying it was something I was not aware of

Odd but … its handy as heck esp from the IDE
You can quickly navigate to the folder that the project is from without having to recall where it was
Cmd - Click and select the containing dir and there you are
Handy as heck

[Emile_Schwarz]

@322573:@Norman Palardy

"Its been present like this in OS X

BTW: I knew that feature, I do not know how to implement it in my Xojo project(s) / Application(s).

I wrongly understand what was saying above, sorry.

A very nice feature, I use it daily (with and without Xojo).

[Emile_Schwarz]

I just found how to use it

I put the code below in the window’s Open Event:

Declare Sub setTitleWithRepresentedFilename Lib "Cocoa" Selector "setTitleWithRepresentedFilename:" _ (NSWindow As Ptr, filePath As CFStringRef) setTitleWithRepresentedFilename(Ptr(Handle), App.gCaritasFI.NativePath)

App.gCaritasFI is a reference to the loaded data (in that window).

Once run, as Norman (and others) wrote earlier, a simple command-click in the window’s name allows to navigate thru the passed NativePath and display each folder from that path.

Very nice !!!

Now the question is: is there’s the same Declare for Linux and Windows ?

PS: do not forgot to enclose the Declare into a #If TargetCocoa block !

[Simon_Berridge]

Version 1.7.2 is now available. This version now uses the Database filename as the Window Title as mentioned above.

@322569:@Emile Schwarz

In Date fields, sometimes I have the Dates displayed as 2017-03-24, sometimes as 24-mars-2017 (in blue), etc.

When you see the date as 2017-03-24 then that field was defined as a text field. If it displays as 24-mars-2017 then it was defined as a date field. So, check the table tab on the offending table to see how the field was defined.

@322569:@Emile Schwarz

when I asked to display a Table with… First Name, Family Name, Photo (images as png), etc.: infinite loop ? The application stop responding.

This is because of the way in which I am loading blobs. I will fix this in the next version.

[Norman_P]

@322577:@Emile Schwarz

Now the question is: is there’s the same Declare for Linux and Windows ?

AFAIK there is nothing like this on Linux or Windows
Windows has a use for an icon in the title bar but it serves a different purpose
See https://msdn.microsoft.com/en-us/library/windows/desktop/dn742501(v=vs.85).aspx

[Simon_Berridge]

Version 1.8.0 is now ready.

This version recognises blobs and shows [BLOB] in the field rather than holding up the application.

[Norman_P]

@322584:@Simon Berridge

Version 1.8.0 is now ready.

Because the forums hide older posts you might post the download link each time you release a new version.
That will keep it simpler for folks (like me) trying to keep up with the flurry of updates

EDIT : Oh yeah - thanks

[Emile_Schwarz]

@322580:@Simon Berridge

When you see the date as 2017-03-24 then that field was defined as a text field. If it displays as 24-mars-2017 then it was defined as a date field. So, check the table tab on the offending table to see how the field was defined.

I may we wrong, but here’s the definition:

Date_Birth DATE NOT NULL, Date_In DATE NOT NULL, Date_Out DATE NOT NULL

And I save the data as SQLDate (2017-03-25 for example).

[Simon_Berridge]

@322589:@Norman Palardy

Because the forums hide older posts you might post the download link each time you release a new version.

I did not do that as the app has an auto installer embedded (Kaju). I assumed (I know that is wrong!) that people would just leave the app in place until next version. When opening the app checks for a newer version and offers to install it.

As that was not clear then the link is here:
SQLite Control

[Simon_Berridge]

[quote=322629:@Emile Schwarz]I may we wrong, but here’s the definition:

Date_Birth DATE NOT NULL, Date_In DATE NOT NULL, Date_Out DATE NOT NULL

And I save the data as SQLDate (2017-03-25 for example).[/quote]
Please look at the tables tab and click on the offending table. Look to the right and the list of fields. In there it will state the field type. If it is DATE, DATETIME, TIME or TIMESTAMP then the display will be 24-mars-2017 (or 24-mars-2017 01:02:03 if hours, mins, secs not zero).

I can only assume that the SQLite field type is coming back in your query as a Text field. If SQLite has a definition of DATE (as per your advice above) then the correct formatting should be applied.

I have tested this with ALL my databases (about 80 at current count) and none of them are displaying this behaviour. If you are still having problems then, if possible, can you PM me with a copy of a database that is giving you the trouble and I will have a look at it.

Sorry.

[Simon_Berridge]

Version 1.8.1 is ready now.

After fixing the Blob issue I found a further bug. Now fixed.

SQLite Control

[Norman_P]

@322644:@Simon Berridge

I did not do that as the app has an auto installer embedded (Kaju). I assumed (I know that is wrong!) that people would just leave the app in place until next version. When opening the app checks for a newer version and offers to install it.

Sure - anyone who already has it wont need the URL but any new readers of this thread will

[Peter_Fargo]

First off thank you for sharing Simon. Any plans to include CubeSQL support? That would be my #1 request

[Emile_Schwarz]

@322645:@Simon Berridge

I can only assume that the SQLite field type is coming back in your query as a Text field. If SQLite has a definition of DATE (as per your advice above) then the correct formatting should be applied.

I do not know or tell if I am right, but it do exactly what I want: I store and get back a SQLDate “String”

Extract of the code that write one date:

OneRecord = OneRecord + "Date_Birth='" +  PM_Birth_Year.Text + "-" + Format(PM_Birth_Month.ListIndex + 1,"00") + "-" +  Format(PM_Birth_Day.ListIndex + 1,"00") + "', "

It appears that I build the SQLDate String and pass it to the right Column.

The Three PM objects are PopupMenus that allows the user to set a date (à la française: day #, month name, year #). Before I do that *, I get all kinds of wrong (false) dates/

Now, you may be right.

BTW: I recall getting errors while displaying the dates (but I forgot what I’ve done to get the error nor to squash it)

• That application is in use for 4 years: I had to add many kinds of enforcements like TRIM, remove doubles (or more) spaces in First Name, Family Name, etc. I even set a PopupMenu with Country names (to avoid typos or errors like CCCP !)
  It is a bit incredible how people entering data from the keyboard are error prone (!)

[Simon_Berridge]

@322663:@Peter Fargo

First off thank you for sharing Simon. Any plans to include CubeSQL support? That would be my #1 request

I am a registered CubeSQL user so I have the plugin available to me. I will think about this.

In the meantime, just copy the database in the CubeSQL database folder to somewhere accessible and access it there. This is what I am doing when I need to add to/modify the database. I know that is not ideal…

Emile:
I stand by my offer to look at a database that you have that is giving the issue to you. Please send to me privately something that is displaying these date issues and I will see what the problem is.

[Simon_Berridge]

@322513:@Dave S

You might want to also consider adding an “attach” feature to allow multiple databases at the same time

Version 1.9.0 now has the Attach Database feature. This is a new tab that allows you to attach many databases to the views. The attached databases are available in the SQL tab following attaching so you can access all fields within your SQL query.

The link is here: SQLite Control