26 Reply by brian.zaballa

(14 replies, posted in General)

sparrow wrote:

You are trying to show me that MYSQL does not support 0 or 'now' in date and time. This is known.
But this is not a drawback of MYSQL, but a feature of SQLITE.
For SQLITE, there is no strict correspondence between a given column type and its contents in a cell,
and this is described in the SQLite documents.
As for the default date value, you can assign it in the form in which you define this value.
For example, by writing it in a script.

Zero (0) was my solution and working for me, check on my video. I'm aware of the capabilities of sqlite and mysql. I'm addressing that video to tcoton.

Go to forum: General

27 Reply by brian.zaballa

(14 replies, posted in General)

tcoton wrote:

@Sparrow

Yes it does but then, if you set a date column not null, you must set a default value and the default value 'now' that works in SQLite does is not accepted by MysQL/MariaDB, there is an error and the table cannot be created,  hence my answer with the correct format above your comment.

Another hiccup when connecting to a fresh database with no tables in MySQL/MariaDB from a MVD project, it tries to create twice the table "_user", throws a duplicate error and creates a useless table "_mvd_roles". This never happens when connecting to an empty database from a compiled project. I tried it multiples times when testing my Delete - Import routine. You can also, as I mentioned before, connect the application to an already filled up database containing the exact structure required with no problem at all.

The subject of this post was really about the default value of a Date column when not null is forced in MySQL.

You mention again here the default value being 'now', changing it to zero fixes the problem for me. If possible, maybe you can send here the tables.ini you are working with so that we can test on it.

Go to forum: General

28 Reply by brian.zaballa

(14 replies, posted in General)

Well, here's mine, no problem at all.
https://1drv.ms/v/s!AlhiHP7f0ZGDh6Qba-O … w?e=SoEhmh

Go to forum: General

29 Reply by brian.zaballa

(14 replies, posted in General)

tcoton wrote:

Yes, I tried zero and I get the same error saying that it is a wrong default value for a date. However, removing the non null constraint, then modifying the table with the right parameters within MySQL does the trick. It does work also to create the table directly in MySQL before connecting the app, it is just more cumbersome.

Maybe something about mysql version. I'm using 5.6.51.

Go to forum: General

30 Reply by brian.zaballa

(1 replies, posted in General)

tcoton wrote:

Hi all,

I have a curiosity question: When calling a form containing one or many grids with auto queries from a button "show form", the grids are automatically refreshed and display any data requested, why is it not the case when calling the very same form from a script that contains either ShowModal or Show, the form appears but the grids are empty unless we write the scripts with tablegrid.dbupdate?

use ShowRecord instead.

var recid: Integer;
recid := 1; // let's say you want to show record with ID: 1
Form1.ShowRecord('tablename', recid);

Go to forum: General

31 Reply by brian.zaballa

(27 replies, posted in General)

tcoton wrote:
sparrow wrote:

Derek described everything.

It is advisable to simply replace the space character with an unused character. Then there will be no false positives.
Example:

trim(ifnull(info1||'§','')||ifnull(info2||'§',''))

Hi all,

Any idea on how to translate this Sqlite version to MySQL? I get a ton of errors while trying to tweak it to fit MySQL and I am not far from the headache.

try

trim(concat(ifnull(CONCAT(info1,'§'),''),ifnull(CONCAT(info2,'§'),'')))

Go to forum: General

32 Reply by brian.zaballa

(6 replies, posted in General)

jayvallejera wrote:

Good day everyone!

I'd like to ask if MVD has a logging feature that records all the transactions in the system. Like who or which user that made the changes in the database from login to CRUD transactions. Thank you.

I think there is none, you need to create your own logic for this.

Go to forum: General

33 Reply by brian.zaballa

(14 replies, posted in General)

tcoton wrote:

Hi all,

I am trying to migrate an application that always was working with Sqlite which contains 2 Date columns that should not be null. While it was working fine with Sqlite which stores dates as text, the creation of these columns is problematic when connecting to a blank MySql database when Myvisualdatabase tries to create the tables. It trows the infamous "Invalid default value for 'Date_Column'.

I have tried to change the default value with "CURRENT_DATE" or "CURRENT_TIMESTAMP" in MyVisualDatabase with no luck, I still get the same error when creating the tables in MySQL. Any experience on anyone side?

Have you tried setting the default value to zero (0)? basically datetime in mysql is just a float

Go to forum: General

34 Reply by brian.zaballa

(8 replies, posted in General)

Good day everyone. bumped to this post when I'm looking for something.
Reopening the application helps me a lot, especially when restoring sqlite database from backup, You can replace the database by simply closing the main form then reopen the application. I just run into some problem with specifying the exe name in OpenFile. Well, my mistake by simply reusing code or renaming the application, then not changing this line of code. To address this, maybe someone will encounter that problem, you can change it by Application.ExeName. this will get the absolute path of the executable and you can reuse the code without worrying,

// close the form
Form0.Close

// Replace database here

// Reopen file
// openfile('roles.exe'); 
OpenFile(Application.ExeName);

Go to forum: General

35 Reply by brian.zaballa

(9 replies, posted in Database applications)

tcoton wrote:
brian.zaballa wrote:

Here's an updated link of the project.

https://www.dropbox.com/s/1dnv5mcv3sqgp … r.zip?dl=1

It would be useful if we could see what the icon is going to look like depending on the theme chosen and if we could actually add more themes. smile

this is interesting, best way to do this is to add an image field on database tho that image will be a different file from the DLL., or maybe someone here knows how to extract the icon/image from that DLL?

If the source I've read is correct that the Resource Hacker was made using Delphi, If I got some spare time, then I would love to dig on this one and create a tool that can extract that icon.

You can add DLLs you modified using Resource Hacker on the folder as stated in the procedure.

Go to forum: Database applications

36 Reply by brian.zaballa

(3 replies, posted in General)

you can try setting InputPass as empty string.
as to saving the password, there's a couple of way to do it in my perspective.
you can save it in database. or in an ini file. but make sure to encrypt it.

Go to forum: General

37 Reply by brian.zaballa

(6 replies, posted in Script)

I made this dynamic menu as a function.

Go to forum: Script

38 Reply by brian.zaballa

(3 replies, posted in General)

You can play with script. I'm just wondering to what scenario in real world will it be used.

Go to forum: General

39 Reply by brian.zaballa

(4 replies, posted in General)

Destiny wrote:

Hello, I know how to avoid duplicates in a table but how to modify an existing record and replace it.

this is vague. are you perhaps looking for script? sql query?
if sql query, then you can use UPDATE or INSERT OR REPLACE query.

Something like...

INSERT OR REPLACE INTO `yourtable`(id, yourfield) VALUES (1, 'test2');

or 

UPDATE `yourtable` SET `yourfield`='test2' WHERE id=1;

Go to forum: General

40 Reply by brian.zaballa

(1 replies, posted in General)

if you know how sqlite works then attaching database will be your best option.

https://myvisualdatabase.com/forum/view … 553#p46553

Go to forum: General

41 Reply by brian.zaballa

(27 replies, posted in General)

don't show the calculated field on the grid, you can always show the fields(ones that is on the database) e.g. on the example, the info1 and info2. then edit them. calc fields only work as a view.

Go to forum: General

42 Reply by brian.zaballa

(27 replies, posted in General)

tcoton wrote:

Beware, if you are using 2 calculated fields using a trim on different fields, you will be able to filter the grids with a text field but you won't be able to edit due to a SQL error telling you that one of the column does not exist. IT looks like the calculated fields are all charged in memory during the application initialization and there can be some mix up. I had to rethink some of my design where I could make a lot of scripts disappear ... but had to redo it.

when you say editing, are you perhaps put the calc field on a component(e.g. TEdit) in a form and add it on the save or make it editable on a grid? That won't work. If you are familiar with SQL, then that calculated field is only an alias, so it is really not in database. It's like SELECT trim(ifnull(info1||'§','')||ifnull(info2||'§','')) AS calcfield FROM yourtable. calcfield is not in database but is only in the result of your query

Go to forum: General

43 Reply by brian.zaballa

(27 replies, posted in General)

derek wrote:

Hi Both,
I was thinking along the lines of concatenated fields in a calculated field and not using a script at all (see attached).
Be careful with the script option as it could be case sensitive.
Regards,
Derek.

This one rocks. Derek have unlimited imagination.

Go to forum: General

44 Reply by brian.zaballa

(27 replies, posted in General)

tcoton wrote:

OK. Looks like I have no other choice but to keep it in the script...  The one in attachment is working as intended.

Good to know, but if you really hate it to have query on the dcu, then embed it on the form.xml. imagination is the key. It'll be visible also but i think it'll be hard.

Go to forum: General

45 Reply by brian.zaballa

(27 replies, posted in General)

That's one of MVD's limitation I think. Just do some script and do make use advantage of the Tablegrid dbFilter.

Go to forum: General

46 Reply by brian.zaballa

(8 replies, posted in General)

here's another example of script to force the checked row as selected before executing the delete function

Go to forum: General

47 Reply by brian.zaballa

(8 replies, posted in General)

jean.brezhonek wrote:

Hello pozidis

The check mark allows you to visually select the lines of a grid on which you want to apply an action (move, delete, edit on a state, etc.).

It does the same job as the Multi-Select option but without its visual aspect.
JB

the default delete function of the button doesn't really delete the checked ones with this #checkbox, so it needs to be done via script unfortunately.

Go to forum: General

48 Reply by brian.zaballa

(8 replies, posted in General)

Care to elaborate on what you really want to do? for multi-delete, you really don't need that checkbox actually, Just checking grid property Additional > Options > goMultiSelect, you can have your multi-delete. using the button. you can simply select by clicking while holding your keyboard's Shift(select all from the selected to the next row to select) or Ctrl(individually selecting row). You need to do some scripting for other checkings you want to do before deleting. You can also click one record from the grid and hit Ctrl+A to select all rows.

Go to forum: General

49 Reply by brian.zaballa

(6 replies, posted in General)

it is a constant. stores value(in that case, an integer) such as integer, string, real, etc...
it is accessible all through out your script in any function or procedure.

i want storing such value in the constant so that later on, i can only set it in the top.

4 is the column index on the grid. so say for example you change the arrangement of the grid, like adding a column before the current index 4, all you have to do is change the constant value, then all is well.

check that i used the constant ID_IDX in different procedures

Form1_Button5_OnClick
Form1_TableGrid1_OnChange
Form1_Button4_OnClick
Form1_Button6_OnClick

I could just set it to its corresponding value(4) like for example

SQLExecute('DELETE FROM Names WHERE id='+Form1.TableGrid1.Cell[4,i].AsString);

and in the other procedure but, then, when you decided to re-arrange your grid (like adding a column), what will happen is you will have to look on all the procedure with that index 4 and a big chance(this happens a lot) to omit some value that needs updating and sometimes it'll not throw an error, but will give an unexpected or unnoticeable output and will eat your time to just looking for that code/s.

Check on the Settings of the TableGrid1, notice that I added Names.id right after Names.tsek  it is at 5th column (index:4)

Go to forum: General

50 Reply by brian.zaballa

(6 replies, posted in General)

here's one.

Go to forum: General