Problems with Updating database through Lazarus
lazarus / 16-Databases

----------------------------------------------------------------------
JackHatton · 2014-02-10 18:55

Hiya,

First off, I'm a rookie programmer so try to be as simple with me as
you can!

So I'm trying to get a database working in my Lazarus program. My
program is console based and not form based so I find it harder to
find the solution to my problems as lots of people tend to be using
form based programming if they're using a database.

The code below works fine.

db_UpdateDatabase('INSERT Into UserDetails VALUES("4", "External2",
"auditor2", "4", "0")');

But I'm trying to use my variables and it's not working (I've tried
many syntax's, but not quite sure what I should really be using):

db_UpdateDatabase('Insert INTO UserDetails
VALUES("'+IntToStr(NoOfUsers)+'", "'+NewUsersUsername+'",
"'+NewUsersPassword+'", "'+IntToStr(NewUsersAccessLevel)+'", "0")');

The program runs but when it gets to the point where this happens it
will just sort of close (lots of text comes up but it's so fast you
can't tell anything it says)

SQLite is my external database thing i'm using.

I've uploaded all relevant files for my project in a rar file here:
https://www.mediafire.com/?o07yidktp635od1

If anyone could help me out with this, it'd be a great help. Trying to
get my project finished by Friday so.

Thanks in advance,
Jack Hatton

----------------------------------------------------------------------
engkin · 2014-02-10 20:13

Quote from: JackHatton on February 10, 2014, 07:55:07 pmThe program
runs but when it gets to the point where this happens it will just
sort of close (lots of text comes up but it's so fast you can't tell
anything it says)
It worked here.   I just needed to change one thing,
db_Load('n:/AccountsDatabase.db'); to db_Load('AccountsDatabase.db');

----------------------------------------------------------------------
JackHatton · 2014-02-10 22:31

QuoteIt worked here.   I just needed to change one thing,
db_Load('n:/AccountsDatabase.db'); to db_Load('AccountsDatabase.db');

Did you actually get the add Add User Account procedure working?

Login > Admin Settings > Add User Account

I tried what you said but it still didn't work for me   - the actual
program does RUN but when it comes to this Add User procedure it
doesn't work.

----------------------------------------------------------------------
JackHatton · 2014-02-10 22:52

QuoteIt worked here.   I just needed to change one thing,
db_Load('n:/AccountsDatabase.db'); to db_Load('AccountsDatabase.db');

Did you actually get the add Add User Account procedure working?

Login > Admin Settings > Add User Account

I tried what you said but it still didn't work for me   - the actual
program does RUN but when it comes to this Add User procedure it
doesn't work.
[/quote]

----------------------------------------------------------------------
engkin · 2014-02-10 23:28

Quote from: JackHatton on February 10, 2014, 11:52:20 pmDid you
actually get the add Add User Account procedure working?

Login > Admin Settings > Add User Account
Check the attachment.

----------------------------------------------------------------------
engkin · 2014-02-10 23:52

Quote from: JackHatton on February 10, 2014, 11:52:20 pm...the actual
program does RUN but when it comes to this Add User procedure it
doesn't work.
That's when you should use the debugger. Go to that line and press F5
then run inside Lazarus (press F9).

----------------------------------------------------------------------
JackHatton · 2014-02-11 00:26

Just tried doing this. It didn't change anything. I think it's suppose
to pause the program or something? (not really sure) but yeah the same
thing happened.

----------------------------------------------------------------------
engkin · 2014-02-11 00:36

If it did not pause there then it did NOT reach that point. Place
another one somewhere else like on db_Load('AccountsDatabase.db'); for
instance. If it pauses then move using F8  (step over) or F7  (step
into).

----------------------------------------------------------------------
mangakissa · 2014-02-11 09:14

After changing the path to the database, I managed to add a new
account. I could even login with the new created user. But creating
another user causes a primary key error. Your variable 'NoOfUsers' has
a wrong number.
Debugging is working normally (but crashes even more on console
application).

----------------------------------------------------------------------
JackHatton · 2014-02-11 16:59

Thanks for all the replies so far.

Okay. I found the reason why it wasn't working for me.

Basically, in my Tools > Options > Debugger I have the debugger type
and path set to none. If I have it as normal then it works but when it
is set to none it doesn't. However, I have a Try and Except statement
and I was told that I needed to do this for my Try and Except
statement to work. So at the moment I can only have one working or the
other. Any suggestions as to how I can get them both working at the
same time?

----------------------------------------------------------------------
taazz · 2014-02-11 17:41

Quote from: JackHatton on February 11, 2014, 05:59:47 pmThanks for all
the replies so far.

Okay. I found the reason why it wasn't working for me.

Basically, in my Tools > Options > Debugger I have the debugger type
and path set to none. If I have it as normal then it works but when it
is set to none it doesn't. However, I have a Try and Except statement
and I was told that I needed to do this for my Try and Except
statement to work. So at the moment I can only have one working or the
other. Any suggestions as to how I can get them both working at the
same time?

Well you either misunderstood or who ever told you that didn't
understand something. In any case the bold is a false statement you
having a debugger or not has nothing to do with your try...except
block working, it never had.

----------------------------------------------------------------------
engkin · 2014-02-11 18:03

As Taazz has explained, the debugger works regardless of try and
except blocks. So just go ahead and set your debugger and try it.

----------------------------------------------------------------------
JackHatton · 2014-02-11 19:30

Okay, so I tried turning my debugger back on and the try and except
thing wouldn't work. So I added the 'EConvertError' to the exceptions
like this:

http://wiki.lazarus.freepascal.org/images/4/44/Debugger_options_langua
ge_exceptions.png

I think that was what I was supposed to do? But anyway it seems to
have worked and now I seem to be having problems with the add
procedure again...

I tried turning off the debugger again to see if it was fixed. Even
though I thought this was what was the problem, it didn't work. So I
don't know what the problem is but I had it working when I was at
Sixth form when the debugger was on and then when i turned it off it
didn't work. So I thought that was the reason but I am confused now.
The error I get now is:


EDatabaseError: Attempt to write a readonly database

Guessing something is different here at home than at my school. Any
suggestions?

----------------------------------------------------------------------
engkin · 2014-02-11 20:01

Go to your project folder. Right-click on your database file
(AccountsDatabase.db). Remove the check mark next to Read-only
attribute.

Try again.

Edit:
You copied your files from a read-only source (like a CD/DVD?)

----------------------------------------------------------------------
JackHatton · 2014-02-11 20:52

The read-only attribute wasn't checked. I made the database file
through SQLite and have just been sending it through emails.

----------------------------------------------------------------------
engkin · 2014-02-12 01:07

I tried to guess, and when I made the database read-only I receive a
similar error message. Check the attachment.

----------------------------------------------------------------------
mangakissa · 2014-02-12 08:40

- Look if you database file is set to readonly? 
- The drive your working is only readable?

If the debugger is not working, then you can use writeln to show your
query and other stuff.

----------------------------------------------------------------------
JZS · 2014-02-12 11:50

Really impressive work. Even you could improve it a bit more.

As mangakissa has pointed out:
>   'NoOfUsers' has a wrong number.

It's critical to work with variables to hold the current number of
users as constant, better query the DB for it.
You used constant at the program starting point to specify how many
users you have. Then you updated the number of users at the program
life BUT that number has reverted to "3" once it was restarted,
regardless of the previously added users, and hence you got Primary
Key uniqueness violated.
You need to update the number of users every time you add user even
after restart:

// So eliminate this line:
NoOfUsersx := 3;  // At program Main block

What you can do:
// at ConsoleDatabases you can add function that returns the
RecordCount of UserDetails Table
Code: [Select]function db_getRecCount():Integer; begin
  if db_Loaded = true then
  begin
    db_getRecCount:=Source.DataSet.RecordCount;
  end;
end;
Alternatively you can make use of the "QueryDatabase" functions you
have already:
Code: [Select]// Ot main program "AccountsProject":
function HowManyUsers:integer;
var
   Str:String;
begin
  //enclose in Try-Except block to handle unsuccessful queries
  //and validate the returned result
  db_QueryDatabase('SELECT Count(UsernameDB) AS UsersCount FROM
UserDetails');
  Str:=db_GetField('UsersCount');
  if str <> '' then
    HowManyUsers := StrToInt(str)
  else
    HowManyUsers := 0;
end;
So "AddNewAccount" function becomes:
Code: [Select]Procedure AddUserAccounts;
begin
  clrscr;
  writeln('Adding a new user account');
  writeln('');
  NoOfUsers := HowManyUsers + 1;
  writeln('Please enter the username of the new account you would like
to create:');
  readln(NewUsersUsername);
  writeln(''); 
...
  // if you might have more than ten users make "UserAccountsArray"
dynamic array instead
  UserAccountsArray[NoOfUsers].UserNo := NoOfUsers;
  UserAccountsArray[NoOfUsers].Username := NewUsersUsername;
  UserAccountsArray[NoOfUsers].Password := NewUsersPassword;
  UserAccountsArray[NoOfUsers].AccessLevel := NewUsersAccessLevel;
  UserAccountsArray[NoOfUsers].UnreadMessages := 0;

  writeln('The user account has been successfully created.');
  readln;
end;
And at the program main block:
Code: [Select]begin
  db_Load('n:/AccountsDatabase.db'); //n:/AccountsDatabase.db// 
 //C:/Users/Jack/Documents/Lazarus/test.db//
  textcolor(15);
  NoOfBooks := 0;
  MessageNo := 0;
  CloseProgram := false;

  NoOfUsers := HowManyUsers;     // NoOfUsersx := 3;
// This is just an example but you better query the DB for all
//  users (select * from UserDetails) and use "while not EOF do" and
load the Array
  For x := 1 to NoOfUsers do   
    begin
      db_QueryDatabase('SELECT * FROM UserDetails where UserNoDB =
'+IntToStr(x));

      UserAccountsArray[x].UserNo :=
StrToInt(db_GetField('UserNoDB'));
      UserAccountsArray[x].Username := db_GetField('UserNameDB');
      UserAccountsArray[x].Password := db_GetField('PasswordDB');
      UserAccountsArray[x].AccessLevel :=
StrToInt(db_GetField('AccesslevelDB'));
      UserAccountsArray[x].UnreadMessages :=
StrToInt(db_GetField('UnreadmessagesDB'));
    end;

  Repeat
    Login
  until(CloseProgram=true);
end.

That's all I had to do to get "Add User Account" Working. As well as
fixing the "Modify Users Account" listing issue.

PS: You need to validate the query results to handle unexpected
errors, for instance, empty string when converted to integer will give
error.