I have a DBISAM table with two fields; name and password.

How do I perform a name and password search at the same time?

I created a dataset query with a query text of

select record from mnpsmain where name={name='name'} and password={password='password'}

and the preview shows the correct record.

However, when I call the dataset query like the following:

  qMnpsmain.Params.Clear;
  qMnpsmain.Params.Add('name='+quotedstr(edtName.Text));
  qMnpsmain.Params.Add('password='+quotedstr(edtPassword.Text));
  Database.Loadrows(qMnpsmain);
  showmessage(IntToStr(qMnpsmain.RowCount));

the rowcount is 0.

Is it possible to perform a table find as well?

P.S.  I noticed that there is no dataset.locate() command. Is it missing for some reason?

Frederick

Frederick,

<< However, when I call the dataset query like the following:

  qMnpsmain.Params.Clear;
  qMnpsmain.Params.Add('name='+quotedstr(edtName.Text));
  qMnpsmain.Params.Add('password='+quotedstr(edtPassword.Text));
  Database.Loadrows(qMnpsmain);
  showmessage(IntToStr(qMnpsmain.RowCount));

the rowcount is 0. >>

This works fine here.  Are you sure that you've got the correct case for what you're searching for ?

<< Is it possible to perform a table find as well? >>

On the client side, or the server side ?

<< P.S.  I noticed that there is no dataset.locate() command. Is it missing for some reason? >>

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=Searching_Sorting_DataSets

under "Searching for a Row".

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

/*
This works fine here.  Are you sure that you've got the correct case for what you're searching for ?
*/

I'll double check but I am sure that it is a correct case since it is only one letter for both the name and password.

/*
On the client side, or the server side ?
*/

On the client side but you can tell me what the syntax is for the server side as well. (For the server, I thought since I am using DBISAM for the database, I should not have to worry how the web server handles the multi-field searches?)

/*
http://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=Searching_Sorting_DataSets

under "Searching for a Row".
*/

I have read it tons of times but it is nowhere as elegant as the Delphi's syntax for LOCATE. It needs more typing then necessary and I even had to put it into a function. However, I don't know how to handle multi-field finds; hence the above question.

I would also like to ask if it is necessary to perform the sort before the find each time the table is searched. I am afraid that the performance will take a serious hit if it has to be carried out for a large table. I tried performing the sort when the table is loaded in the OnShow event of the main form but when I performed the search later, EWB did not respond to the search.

As an additional question, will EWB's data sources for queries (and tables) use the indexes defined for DBISAM tables to improve performance? I have a client with a 100,000 product item table and the application is to be used over the Internet.

Frederick

Frederick,

<< On the client side >>

The client side is the Find method that I linked to.

<< but you can tell me what the syntax is for the server side as well. >>

There is no server-side syntax with EWB, apart from whatever you may use in terms of custom code in a EWB Web Server module.

<< I have read it tons of times but it is nowhere as elegant as the Delphi's syntax for LOCATE. It needs more typing then necessary and I even had to put it into a function. >>

EWB does not have variant support, hence no Locate.

<< However, I don't know how to handle multi-field finds; hence the above question. >>

You simply assign whatever columns you wish to search on, and any modified columns will be used in the search.

<< I would also like to ask if it is necessary to perform the sort before the find each time the table is searched. >>

No.

<< I am afraid that the performance will take a serious hit if it has to be carried out for a large table. I tried performing the sort when the table is loaded in the OnShow event of the main form but when I performed the search later, EWB did not respond to the search. >>

What do you mean by "it didn't respond" ?  Do you mean it returned an incorrect result, or that it pegged the CPU for the browser thread and didn't relinquish it ?  How many rows are you searching on, and are you searching on the same columns that are sorted, with the same Find/Sort modifiers (case-sensitivity, etc.) ?

<< As an additional question, will EWB's data sources for queries (and tables) use the indexes defined for DBISAM tables to improve performance? I have a client with a 100,000 product item table and the application is to be used over the Internet. >>

Do not try to load a 100,000 product item table in an EWB application.  It may work, but it's a bad idea because it's just too much data to load in one shot and performance will suffer.  Instead, use a constrained query (query with a WHERE clause) on the server-side and allow the user to specify the parameters about what products they wish to view, etc.

Tim Young
Elevate Software
www.elevatesoft.com

<Frederick Chin> wrote:
> I have a DBISAM table with two fields; name and password.
>
> How do I perform a name and password search at the same time?


I realise I may be wrong here, given the little information, but please be
sure you are not storing passwords "as-is" in text. My password lookup code
finds the name, and then reads he "salt" to apply to the provided password
encryption and then compares with the stored encrypted data. Anything less
is not safe nowadays.


--
Matthew Jones

Matthew Jones wrote:

/*
I realise I may be wrong here, given the little information, but please be
sure you are not storing passwords "as-is" in text. My password lookup code
finds the name, and then reads he "salt" to apply to the provided password
encryption and then compares with the stored encrypted data. Anything less
is not safe nowadays.
*/

Thanks for the information. What do you use with EWB to encrypt and decrypt data? I am currently relying on DBISAM's encrypted tables but I am not sure if the data will be decrypted by EWB's web server before it is sent across the wire or will it be decrypted only when EWB receives the data.

Frederick

Tim,

I did a bit more testing and the query returns a rowcount of 0 the first time the call is made but returns a value of 1 the second time around. The value of rowcount returned seems to be delayed.

The following code is in the OnExit event of an edit box:-

  qMnpsmain.Params.Clear;
  qMnpsmain.Params.Add('name='+quotedstr(edtName.Text));
  qMnpsmain.Params.Add('password='+quotedstr(edtPassword.Text));
  Database.Loadrows(qMnpsmain);
  showmessage(IntToStr(qMnpsmain.RowCount));

Frederick

Tim,

/*
You simply assign whatever columns you wish to search on, and any modified columns will be used in the search.
*/

After much testing, I appear to have used the correct syntax for the search.

Note: I wish to suggest that more examples be shown in the manual.

/*
<< I would also like to ask if it is necessary to perform the sort before the find each time the table is searched. >>

No.
*/

My DBISAM table has two fields of NAME and PASSWORD and an index is on the NAME field.

I did not perform a sort but EWB was able to find the name field and name and password field combination. Both checks are in the OnExit event of each edit box:-

Name's EditBox:-

  with Mnpsmain do begin
       Columns['password'].SortDirection:=sdNone;
       InitFind;
       Columns['name'].AsString:=TEdit(Sender).Text;
       lOK:=Find(False,True);  // lOK is True if the correct name is entered, False otherwise
  end;

Password's EditBox:-

  with Mnpsmain do begin
       Columns['password'].SortDirection:=sdAscending;
       InitFind;
       Columns['name'].AsString:=edtName.Text;
       Columns['password'].AsString:=TEdit(Sender).Text;
       lOK:=Find(False,True);  // lOK is True if the correct name and password is entered, False otherwise
  end;

Questions:-

1.   Is EWB using the name field's index from DBISAM to search for the name?
2.   If yes, why does the password test work even when no index is present for this field?

/*
What do you mean by "it didn't respond" ?  Do you mean it returned an incorrect result, or that it pegged the CPU for the browser thread and didn't relinquish it ?  How many rows are you searching on, and are you searching on the same columns that are sorted, with the same Find/Sort modifiers (case-sensitivity, etc.) ?
*/

I think that my syntax was wrong and that's why it failed.

/*
Do not try to load a 100,000 product item table in an EWB application.  It may work, but it's a bad idea because it's just too much data to load in one shot and performance will suffer.  Instead, use a constrained query (query with a WHERE clause) on the server-side and allow the user to specify the parameters about what products they wish to view, etc.
*/

If my product table has an index for the code field and I do not perform a sort from EWB, will my query take advantage of the code index defined in the DBISAM table?

Frederick

Frederick,

> I did a bit more testing and the query returns a rowcount of 0 the
first time the call is made but returns a value of 1 the second time
around. The value of rowcount returned seems to be delayed.
>
> The following code is in the OnExit event of an edit box:-
>
>     qMnpsmain.Params.Clear;
>     qMnpsmain.Params.Add('name='+quotedstr(edtName.Text));
>     qMnpsmain.Params.Add('password='+quotedstr(edtPassword.Text));
>     Database.Loadrows(qMnpsmain);
>     showmessage(IntToStr(qMnpsmain.RowCount));

There should be no problem with the syntax of your code. But you have to
know that everything in JS is asynchronous. That's why you should move
this line

showmessage(IntToStr(qMnpsmain.RowCount));

in the OnLoad event of the dataset. Doing so you make sure that the
dataset has been loaded *before* you check the rowcount.

Most probably that's your problem.

Uli

Frederick Chin wrote:

Tim,

<<After much testing, I appear to have used the correct syntax for the search.

Note: I wish to suggest that more examples be shown in the manual.>>

For new users especially, some code examples in the manual would be a great help