Source language: Translate to:

Find/Query in a record

Questions about NeoBook PlugIns

Moderator: Neosoft Support

Find/Query in a record

Postby Leos » Sat Apr 14, 2007 6:37 am

Hi,

It will be very usefull if the actual NobookDB plugin include an option to find and/or query in a record from a database (not only in a field).
I know that i can do it with a lot of code, but it is not the right solution.

Thank you
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

example?

Postby dpayer » Sat Apr 14, 2007 8:31 am

Could you write an exampe of what you wish you could do?

Is it solveable with SQL statements?

David P.
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby Leos » Sat Apr 14, 2007 2:15 pm

ok!

an example:

dbfQuery "Example.dbf" "field1==[#34]the name that i want[#34]"

.//it gives to me all "the name that i want" that exists in field1

.//but if i want for example all the records that it have (Example.dbf) "the name that i want", don´t matter if it is in fiel1, or field2 ..., it will be nice to have a command that do the job, something like this:

dbfshowstring "Example.dbf" "the name that i want"
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Postby Gaev » Sat Apr 14, 2007 2:50 pm

Leonel:
.//but if i want for example all the records that it have (Example.dbf) "the name that i want", don´t matter if it is in fiel1, or field2 ...
a) Are you looking for such a "find in all fields" facility where "a specified string" matches "text in all fields" ? ... or just "in all fields of the type String" ?

b) Are you also looking for the inverse ? ... i.e. does NOT match specified string

c) What about numeric values and numeric fields ... and date values/fields ... and boolean ... etc.

Some of what you want to do can be achieved more easily without "long long typing" if you use a couple of generic Functions that would be built once ... and Call'ed whenever required ... the only possible obstacle I see is that "(any) limit on the length of the parameter" for dbfQuery might be exceeded if the query string or number of fields to query is excessive.

A possible way around this would be to have a spare/dummy field (call it "YesNo,str,1") ... which would be initialed to 0 ... and successive dbfQuery commands (one for each query against a field) would be followed by commands to set this field to 1 ... finally, doing a query for value 1 in the YesNo field would give you the collection ... depending on size of database, this might take noticeably longer ... but is more fool proof.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Leos » Mon Apr 16, 2007 4:50 am

Hi, Gaev

a) Are you looking for such a "find in all fields" facility where "a specified string" matches "text in all fields" ? ... or just "in all fields of the type String" ?

For this time, my wish is just "in a one field (don't matter what field, or what type of field) of the record that match the input string"
This command will be nice to have find and query option.
example:

database example.dbf

record 1= field1 "my name is" | field 2 "my house is"
record 2= field1 "my example" | field 2 "the name of the example"
record 3= field1 "my car is tractor" | field 2 "my fellow"

dbfspecialquery "example.dbf" "name"

will return the result:

record 1, record 2

b) Are you also looking for the inverse ? ... i.e. does NOT match specified string

this option will be excelent!!! (the result is the inverse of the query) :idea:
c) What about numeric values and numeric fields ... and date values/fields ... and boolean ... etc.

From my point of view, in this case the type of the field don't matter.
Just if the field don´t contain the "find" or "query" input data (name in the example) don't will be include in the result.
A possible way around this would be to have a spare/dummy field

is a nice way to solve the problem, but you can see it require a lot of work... and my wish is just to have to resolve that (in a pub where you have a lot of different databases it is not easy to work around it)

For now i use a different way to resolve that:

in small databases i have a additional database with two fiels: one field is the primary key and the second is a memo field. I export all the records of the main databases (dbfRecordToVar "example" "field2"), so the memo fiel contains all tha data of the fields of the main database. I do a query to the additional database (in the memo field) and my primary key returns the value that i wish (obviously the value of the primary key is the same in the two databases (field1 of main database==field1 of additional database...field2 of main database==field2 of additional database.../...)

I hope to be explicit. I don´t know if this is really usefful for others users, but in my oppinion this is a really important miss (in the actual database plugin, and the wish future plugin).

Thank you for your interest
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal

Postby Gaev » Mon Apr 16, 2007 6:08 am

Leonel:
For this time, my wish is just "in a one field (don't matter what field, or what type of field) of the record that match the input string"

This command will be nice to have find and query option.
dbfspecialquery "example.dbf" "name"

You might consider the development of a custom NeoBook Function that you would Call ... e.g.
Code: Select all
Call "dbfspecialquery" "example.dbf" "name"

This function would ...

a) Extract the names of all "string fields" in database specified by [%1] e.g. say example.dbf had the following string fields ...

FirstName,LastName,Address1,Address2,City


b) construct the "query string" e.g. ...

FirstName=[%2] OR LastName=[%2] OR ... etc. etc. ... OR City=[%2]

... where [%2] contains the values of the desired "search text"


c) invoke the regular "dbfQuery" command i.e. ...

dbfQuery "[%1]" "[GeneratedQuery]"


... where [%1] contains the filename of the database to be searched ... and [GeneratedQuery] is from step (b).


The only possible problem I see is if your database has many many string fields ... and your search stext is also very long ... which might make the value of [GeneratedQuery] bigger than allowed.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Leos » Tue Apr 17, 2007 6:51 am

Once more time i appreciate your help Gaev. Thank you.

But, i still thinking that it be useful if the plugin can include one similar command.

There are a lot of databases where you can apply this command/function.
User avatar
Leos
 
Posts: 178
Joined: Mon Apr 04, 2005 11:13 am
Location: Coimbra, Portugal


Return to PlugIn Discussions

Who is online

Users browsing this forum: No registered users and 3 guests

cron