|
Home > Archive > Programming with dBASE > October 2005 > An SQL question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Claus Mygind 2005-10-17, 1:26 pm |
| I have posted the following in the internet group, as the apps I am
developing are web based. But as the question deals specifically with how
to use SQL syntax, I thought maybe I could get some additional eyes to look
at it from here.
I know that the following does work in dBASE
Here is the SQL statment:
--Database:MyDataBase
SELECT COUNT(*)-100
FROM ourTable OurTable
WHERE ID <= 'ourKeyValue'
ORDER BY ID
How do I plug that into my query below and retrieve the result?
q = new QUERY()
q.database = db
q.sql = [Select count(*) from ourTable OurTable where id<='ourKeyValue'
ORDER
BY ID]
q.requestLive = false
q.active = true
Basic info:
1. we are working with a MySQL database and tables.
2. the user has a record displayed on an html page (window A)
3. the current record is the record key used for the lookup of nearby
records.
4. we want to display selected information about 100 records (49 before, the
current record and 50 after) in a separate window (window B) that the user
can select from and return a new detail record in window A .
In MySQL syntax we execute the following two queries
1. select count(*)-49 from ourTable where ourKeyField <= 'theCurrentKey'
order by ourKey
We now have an offset to start returning values from
2. select * from ourTable order by ourKey limit resultOfQuery1, 100
As no rows are fetched in query 1 and query 2 only returns 100 rows the
execution is very quick.
My question is, can this be expressed through a dBASE app?
How do I get the value from query 1?
Can I use the clause "limit" in an SQL expression through dBASE?
| |
| *Lysander* 2005-10-24, 8:24 pm |
| In article <OPxCAyy0FHA.1232@news-server>, cmygind@tsccorp.com says...
Moin!
What is it that does NOT work with your example?
Because it looks fine to me.
> Can I use the clause "limit" in an SQL expression through dBASE?
Should be possible.
In Firebird, this is equivalent to "select first n from...." and works=20
fine for me.
For this, in your BDE-Alias, set "SQLQueryMode =3D server", and _AVOID_=20
using the joker (*) in your queries. List all requested fields instead.
Like: "select field_1, field_2, field_3 from table...".
--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| Claus Mygind 2005-10-24, 8:24 pm |
| André
Thanks for your input. It made me go back and look at my code. BDE was
already set to "server". No problem with the joker (*). I was relying on
rowset.rowCount( ) which was returning a -1. Just needed to add
rowset.first( ) and it solved my problems. Always good to have a second
pair of eyes when learning something new.
Thanks again
Claus
"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1dc6a124f9e067ec9899
0d@news.dbase.com...
In article <OPxCAyy0FHA.1232@news-server>, cmygind@tsccorp.com says...
Moin!
What is it that does NOT work with your example?
Because it looks fine to me.
> Can I use the clause "limit" in an SQL expression through dBASE?
Should be possible.
In Firebird, this is equivalent to "select first n from...." and works
fine for me.
For this, in your BDE-Alias, set "SQLQueryMode = server", and _AVOID_
using the joker (*) in your queries. List all requested fields instead.
Like: "select field_1, field_2, field_3 from table...".
--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| *Lysander* 2005-10-24, 8:24 pm |
| In article <IhGgjqK2FHA.1236@news-server>, cmygind@tsccorp.com says...
Glad that it works.
The exclusion of using the "*" in SQL-queries was meant for the use of=20
"serverspecific SQL", like in your case the "limit"-clause.
Using * in an SQL-statement forces the BDE to use it's own "LOCAL SQL"=20
instead of the server-specific SQL, no matter what is in the property=20
"SQLQueryMode" of the BDE-alias.
So, as a conclusion, you CAN use serverspecific SQL, but not together=20
with using "*" in a query.
ciao,
Andr=E9
> Andr=E9
>=20
> Thanks for your input. It made me go back and look at my code. BDE was=
=20
> already set to "server". No problem with the joker (*). I was relying o=
n=20
> rowset.rowCount( ) which was returning a -1. Just needed to add=20
> rowset.first( ) and it solved my problems. Always good to have a second=
=20
> pair of eyes when learning something new.
>=20
> Thanks again
> Claus
>=20
>=20
> "*Lysander*" <nobody@nowhere.com> wrote in message=20
> news:MPG. 1dc6a124f9e067ec9899
0d@news.dbase.com...
> In article <OPxCAyy0FHA.1232@news-server>, cmygind@tsccorp.com says...
>=20
> Moin!
> What is it that does NOT work with your example?
> Because it looks fine to me.
>=20
>=20
>=20
> Should be possible.
> In Firebird, this is equivalent to "select first n from...." and works
> fine for me.
>=20
> For this, in your BDE-Alias, set "SQLQueryMode =3D server", and _AVOID_
> using the joker (*) in your queries. List all requested fields instead.
> Like: "select field_1, field_2, field_3 from table...".
>=20
>=20
>=20
>=20
--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| Claus Mygind 2005-10-27, 7:28 am |
| Aah!! Thanks. I have printed this email for future reference. This is good
stuff!!
"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1dc71ca1544f8c169899
10@news.dbase.com...
In article <IhGgjqK2FHA.1236@news-server>, cmygind@tsccorp.com says...
Glad that it works.
The exclusion of using the "*" in SQL-queries was meant for the use of
"serverspecific SQL", like in your case the "limit"-clause.
Using * in an SQL-statement forces the BDE to use it's own "LOCAL SQL"
instead of the server-specific SQL, no matter what is in the property
"SQLQueryMode" of the BDE-alias.
So, as a conclusion, you CAN use serverspecific SQL, but not together
with using "*" in a query.
ciao,
André
> André
>
> Thanks for your input. It made me go back and look at my code. BDE was
> already set to "server". No problem with the joker (*). I was relying on
> rowset.rowCount( ) which was returning a -1. Just needed to add
> rowset.first( ) and it solved my problems. Always good to have a second
> pair of eyes when learning something new.
>
> Thanks again
> Claus
>
>
> "*Lysander*" <nobody@nowhere.com> wrote in message
> news:MPG. 1dc6a124f9e067ec9899
0d@news.dbase.com...
> In article <OPxCAyy0FHA.1232@news-server>, cmygind@tsccorp.com says...
>
> Moin!
> What is it that does NOT work with your example?
> Because it looks fine to me.
>
>
>
> Should be possible.
> In Firebird, this is equivalent to "select first n from...." and works
> fine for me.
>
> For this, in your BDE-Alias, set "SQLQueryMode = server", and _AVOID_
> using the joker (*) in your queries. List all requested fields instead.
> Like: "select field_1, field_2, field_3 from table...".
>
>
>
>
--
ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| Ivar B. Jessen 2005-10-27, 7:28 am |
| On Mon, 24 Oct 2005 17:30:58 +0200, *Lysander* <nobody@nowhere.com> wrote:
>In article <IhGgjqK2FHA.1236@news-server>, cmygind@tsccorp.com says...
>
>Glad that it works.
>The exclusion of using the "*" in SQL-queries was meant for the use of
>"serverspecific SQL", like in your case the "limit"-clause.
>
>Using * in an SQL-statement forces the BDE to use it's own "LOCAL SQL"
>instead of the server-specific SQL, no matter what is in the property
>"SQLQueryMode" of the BDE-alias.
>
>So, as a conclusion, you CAN use serverspecific SQL, but not together
>with using "*" in a query.
Hmm, this works with a MySQL table, shouldn't it do that?
q.sql ='select * from newFish order by Name limit ' + nStart + ', ' + nstop
Ivar B. Jessen
| |
| *Lysander* 2005-10-27, 7:28 am |
| In article < u44tl11rrh8s51onkni8
5bik83vka55t7o@4ax.com>,=20
bergishagen@it.notthis.dk says...
> Hmm, this works with a MySQL table, shouldn't it do that?
>=20
> q.sql =3D'select * from newFish order by Name limit ' + nStart + ', ' + n=
stop
No,
according to my experience it shouldn't. I tested with Interbase,=20
Firebird and Postgres, but not with MySQL because I don't have it=20
running anywhere....
Whenever it tried to access serverspecific SQL in one of them it works=20
pretty good as long as I don't use '*' in the query.
I would be deeply interested in further testing, because in any case=20
some undocumented BDE-behaviour seems to be the reason for that.
You have Firebird, I assume.
If so, and if you find the time, please test this for example with the=20
commands
"select * from newfish order by 1" (does not work)
and
"select field1, field2, from newfish order by 1" (works good)
Serverspecific here would be to give a column-number instead of a=20
column-identifier.
You can also try with=20
"select first 5 field1, field2 from newfish"
against
"select first 5 * from newfish"
I can not remember with which other commands I tried, but I was trying a=20
good bunch of commands supported by the servers an not by the BDE's=20
LOCAL SQL. The result always was the same. Without "*" it works, with=20
"*" it does not.
--=20
ciao,
Andr=E9
| |
| Ivar B. Jessen 2005-10-27, 7:28 am |
| On Wed, 26 Oct 2005 09:28:54 +0200, *Lysander* <nobody@nowhere.com> wrote:
>In article < u44tl11rrh8s51onkni8
5bik83vka55t7o@4ax.com>,
>bergishagen@it.notthis.dk says...
>
>
>No,
>according to my experience it shouldn't. I tested with Interbase,
>Firebird and Postgres, but not with MySQL because I don't have it
>running anywhere....
>
>Whenever it tried to access serverspecific SQL in one of them it works
>pretty good as long as I don't use '*' in the query.
>
>I would be deeply interested in further testing, because in any case
>some undocumented BDE-behaviour seems to be the reason for that.
>You have Firebird, I assume.
With Firebird this works.
q.sql = 'select first 5 skip 2 * from FISH'
q.requestlive = false
q.active = true
With MySQL this works,
q.sql ='select * from newFish order by Name limit 5, 2'
q.requestLive = false
q.active = true
>If so, and if you find the time, please test this for example with the
>commands
> "select * from newfish order by 1" (does not work)
>and
> "select field1, field2, from newfish order by 1" (works good)
With Firebird this does not work, maybe because the sql is _not_ server
specific, see comment below on ORDER BY clause,
q.sql = 'select * from FISH order by 1'
q.requestlive = false/q.requestlive = true
q.active = true
With Firebird this works,
q.sql = 'select ID, Name from newFish order by 1'
q.active = true
With MySQL this works,
q.sql = 'select ID, Name from newFish order by 1'
q.active = true
With MySQL this works, although the sql is not server specific,
q.sql = 'select * from newFish order by 1'
q.requestLive = false (required)
q.active = true
>Serverspecific here would be to give a column-number instead of a
>column-identifier.
Not quite, see localsql.hlp on ORDER BY,
"A column may be specified in an ORDER BY clause using a number
representing the relative position of the column in the SELECT of the
statement".
Your rule about using '*' with SQL databases is not without exception :-)
Ivar B. Jessen
| |
| *Lysander* 2005-10-27, 7:28 am |
| In article < cdeul1lu8k9n5al4agmm
l83oe3d287kdp4@4ax.com>,=20
bergishagen@it.notthis.dk says...
> Not quite, see localsql.hlp on ORDER BY,
wouldn't be the first time that an entry in the .hlp is not 100%=20
correct.. but I really have to do some more tests on this.
> Your rule about using '*' with SQL databases is not without exception=20
:-)
Definitely.
In general, this was never too interesting for me, because I am usually=20
not using the "*" with SQL-Servers. It's taking away the speed and the=20
fun from the results :)
I was looking into it a bit more detailed when preparing dokuments for=20
dBKon 2005. But now I cannot so quickly find my tests that I made.
Be sure, that I will continue this thread later, because the complete=20
thing is weird. And I am sure that "select first 5 * from fish" would=20
produce an error, where "select first 5 field1, field2 from fish" would=20
not.
did you also try with "live" rowsets?
Ah...
And just now I remember that when using "*" the rowset.findkey() method=20
would work, but setting rowset.indexname would not....
--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| Ivar B. Jessen 2005-10-27, 7:28 am |
| On Wed, 26 Oct 2005 13:55:57 +0200, *Lysander* <nobody@nowhere.com> wrote:
>In article < cdeul1lu8k9n5al4agmm
l83oe3d287kdp4@4ax.com>,
>bergishagen@it.notthis.dk says...
>
>
>wouldn't be the first time that an entry in the .hlp is not 100%
>correct.. but I really have to do some more tests on this.
According to localSQL.hlp,
"What is local SQL?
Local SQL is the subset of the SQL-92 specification used to access dBASE,
Paradox, and FoxPro tables. On receving local SQL statements from front-end
applications, the Borland Database Engine (BDE) translates the statements
into BDE API functions".
The sql below works on the Fish.dbf table in the samples directory, so the
local.hlp appears to be correct in this case.
q.sql = 'select * from Fish order by 2'
q.active = true
>:-)
>
>Definitely.
>In general, this was never too interesting for me, because I am usually
>not using the "*" with SQL-Servers. It's taking away the speed and the
>fun from the results :)
>
>I was looking into it a bit more detailed when preparing dokuments for
>dBKon 2005. But now I cannot so quickly find my tests that I made.
>
>Be sure, that I will continue this thread later, because the complete
>thing is weird. And I am sure that "select first 5 * from fish" would
>produce an error, where "select first 5 field1, field2 from fish" would
>not.
>
>did you also try with "live" rowsets?
Do you mean the following? (Firebird table)
q.sql = 'select first 5 skip 2 * from FISH'
q.requestlive = true // <-----
q.active = true
It results in error: Database Engine Error: Invalid use of keyword: 5
That is why I need the requestLive = false ;-)
>
>Ah...
>And just now I remember that when using "*" the rowset.findkey() method
>would work, but setting rowset.indexname would not....
Hmm, another exception?
Ivar B. Jessen
| |
| *Lysander* 2005-10-27, 7:28 am |
| In article < r4fvl1p4vjivnu3ff9o7
ubn09u6geupkrq@4ax.com>,=20
bergishagen@it.notthis.dk says...
> On Wed, 26 Oct 2005 13:55:57 +0200, *Lysander* <nobody@nowhere.com> wrote=
:
>=20
>=20
> According to localSQL.hlp,
>=20
> "What is local SQL?
>=20
> Local SQL is the subset of the SQL-92 specification used to access dBASE,
> Paradox, and FoxPro tables. On receving local SQL statements from front-e=
nd
> applications, the Borland Database Engine (BDE) translates the statements
> into BDE API functions".
>=20
> The sql below works on the Fish.dbf table in the samples directory, so th=
e
> local.hlp appears to be correct in this case.
>=20
> q.sql =3D 'select * from Fish order by 2'
> q.active =3D true
>=20
=20[color=darkred]
=20[color=darkred]
=20[color=darkred]
=20[color=darkred]
=20[color=darkred]
>=20
> Do you mean the following? (Firebird table)
>=20
> q.sql =3D 'select first 5 skip 2 * from FISH'
> q.requestlive =3D true // <-----
> q.active =3D true
>=20
> It results in error: Database Engine Error: Invalid use of keyword: 5=
=20
>=20
> That is why I need the requestLive =3D false ;-)
But now try again with requestlive =3D true and withOUT using the "*".
Like "select first 5 ID, Name from fish".
I made the experience that this works. And I don't have a good=20
explanation for this... only a guideline... do not use "*" with SQL-
Servers :)
--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
| |
| Ivar B. Jessen 2005-10-27, 7:28 am |
| On Thu, 27 Oct 2005 08:35:56 +0200, *Lysander* <nobody@nowhere.com> wrote:
>
>But now try again with requestlive = true and withOUT using the "*".
>Like "select first 5 ID, Name from fish".
>I made the experience that this works. And I don't have a good
>explanation for this... only a guideline... do not use "*" with SQL-
>Servers :)
It results in the same error as before: Invalid use of keyword: 5
Confirmed on a similar select on a table in a different Firebird database.
The table had one column and there was a primary index on that column. The
select statement failed with requestLive = true and worked with requestLive
= false, for both '*' and 'ColumnName'.
Ivar B. Jessen
| |
| *Lysander* 2005-10-27, 7:28 am |
| In article < dd61m1tesivv0875ijnt
84h701fh828usa@4ax.com>,=20
bergishagen@it.notthis.dk says...
> Confirmed on a similar select on a table in a different Firebird database=
..
> The table had one column and there was a primary index on that column. Th=
e
> select statement failed with requestLive =3D true and worked with request=
Live
> =3D false, for both '*' and 'ColumnName'.
There seems to be much more in it than what I noticed on the surface...
"first m skip n" works with and without "*" but only in requestlive =3D=20
false
"order by 1" (column as number) works with requestlive =3D true, but only=
=20
withOUT "*"
"findkey()" together with "order by" and without indexname works only=20
without "*"...
I guess there is a lot more to find out.=20
But most of those things do not affect my work at all because I replaced=20
those commands with sql-server-friendly equivalents, so I will from time=20
to time test a bit more...
Thanks for your help.
--=20
ciao,
Andr=E9
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~
Deutsche dBase-Konferenz dBKon 2005 ::: 11. bis 13. November 2005
Deutschsprachige Konferenz zu dBase und anderen Datenbanken
Info: www.dbase-konferenz.de
|
|
|
|
|