Source language: Translate to:

Cursorlocation = server

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Cursorlocation = server

Postby fieldhopper » Thu Apr 07, 2016 5:24 am

Hey, I have test the following script:

Code: Select all
debugbreakpoint ""
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=table;User=ich;Password=password;Option=3;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
dbpSetGridProperties "LEP" "lehrer" ""
dbpShowGrid "LEP" "lehrer" "Rectangle1"


The result is (produced by hte last row)

The dataset does not support bookmarks, which are required for multi-data record controls.


How is it possible to use the parameter
CursorLocation=Server
, cause without the program is running.
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Thu Apr 07, 2016 5:53 am

fieldhopper:

How is it possible to use the parameter CursorLocation=Server

From the Help file ...
With a client-side cursor (the default) all data is copied to the local machine and processed there. This provides access to features not normally supported by servers such as sorting and indexing. When using a SQL query, only the data returned is copied to the local machine. A server-side cursor doesn't provide as much flexibility, but is often more appropriate for large databases, and may be required when the size of a database exceeds the available memory and disk space available on a local machine.

I have not tried using CursorLocation=Server, but it looks like you need to do a dbpQuery command (before the dbpShowGrid command) so at least one record is downloaded to the client side.
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Fri Apr 08, 2016 12:25 am

Hey,
that is the new code

Code: Select all
debugbreakpoint ""
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=table;User=user;Password=password;Option=3;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
dbpSetGridProperties "LEP" "lehrer" ""
dbpQuery "LEP" "lehrer" "l_pnr > '00000'"
dbpFirst "LEP" "lehrer"
dbpShowGrid "LEP" "lehrer" "Rectangle1"


i have tried, but nothing happened. Only the same message as above :(.
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Fri Apr 08, 2016 8:58 am

fieldhopper:

Try these suggestions ...

a) remove extraneous commands ..

debugbreakpoint ""
dbpSetGridProperties "LEP" "lehrer" ""

b) According to the Help file, when you use > in dbpQuery filters ...
The contents of the field is greater than the search string. This is most often used with numeric fields. For example: Salary > 50000

If l_pnr is a numeric field, specify ... dbpQuery "LEP" "lehrer" "l_pnr > 0"

If l_pnr is a text field, again according to the Help file ...
If the field being searched is a String, Char or Memo type then the search string must be surrounded by quotes which, in NeoBook, are specified using the special code: [#34]. For example: "City = [#34]Pittsburgh[#34]"

... specify some thing like... dbpQuery "LEP" "lehrer" "NOT l_pnr = [#34]00000[#34]"

c) after the dbpQuery command, insert ...

AlertBox "QueryCount" "[LEP.lehrer.$RecCount]'

... to confirm that at least one record was matched.


Code: Select all
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=table;User=user;Password=password;Option=3;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
dbpQuery "LEP" "lehrer" "{see comments in (b) above}"
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"
dbpFirst "LEP" "lehrer"
dbpShowGrid "LEP" "lehrer" "Rectangle1"
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Thu Apr 14, 2016 12:50 am

Late but not to late. It seems to be running.
Thanks for your help. :)
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby fieldhopper » Thu Apr 14, 2016 2:37 am

Sorry it does not run. The same alert as above. Reccount is -1 :( . I have not place the Cursorlocation as parameter. When i placed it the mistake happened.
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Thu Apr 14, 2016 6:02 am

fieldhopper:
Reccount is -1. I have not place the Cursorlocation as parameter. When i placed it the mistake happened.

Looks like, immediately after the dbpQuery command, no records are fetched (downloaded) ... suggestions ...

1) Try using option=2

According to these web pages ...

http://stackoverflow.com/questions/4407 ... ion-string
http://p2p.wrox.com/classic-asp-databas ... tring.html

... option=3 is the sum of option=1 and option=2 ... and option=1 is no lomger supported.

2) To see if there is a bug in the dbpQuery command when used in this non-mainstream manner (or a timing issue), see if you can use dbpExecSQL instead ...

Code: Select all
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=table;User=user;Password=password;Option=2;CursorLocation=Server"
dbpExecSQL "LEP" "SELECT * FROM lehrer WHERE {your query goes here} LIMIT 5" "Temp1"
dbpOpenTable "LEP" "Temp1" ""
AlertBox "QueryCount" "[LEP.Temp1.$RecCount]"


3) if that too does not work, try and replace table "lehrer" with another (say "lehrertest") which contains only 3 or 4 text fields of a small column width, and populate it with three or four records (making sure not to use any non-English accented characters etc.)

Also, please post exact details of the "query" you are using.

P.S. Hopefully, you have dbpShowErrors "Yes".
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Tue Apr 19, 2016 1:43 am

ok, i have tried with the following code:

Code: Select all
.SetVar "[WindowState]" "maximized"
debugbreakpoint ""
dbpShowErrors "Yes"
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 5.3 ANSI Driver};Server=server;Database=schulsystem;User=user;Password=password;Option=2;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
dbpOpenTable "LEP" "schulen" ""
.dbpSort "LEP" "schulen" "SCH_NAME=ASC"
dbpFirst "LEP" "schulen"
Loop "1" "[lep.schulen.$RecCount]" "[result]"
ListBoxAddItem "ListBox1" "0" "[LEP.schulen.sch_id]-[LEP.schulen.sch_Name]"
dbpNext "LEP" "schulen"
endloop
dbpSetGridProperties "LEP" "schulen" ""
.dbpQuery "LEP" "lehrer" "l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL)"
.dbpExecSQL "LEP" "select * from lehrer where l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL) limit 5" ""
dbpExecSQL "LEP" "select * from schulen limit 5" ""
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"
.dbpFirst "LEP" "lehrer"
dbpShowGrid "LEP" "lehrer" "Rectangle1"


I have tried both driver, mysql 3.51 and mysql 5.3 ANSI. The same result. Querycount -1 und the alert as above. :cry: .
Have someone success with openening a database with htis parameters ?
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Tue Apr 19, 2016 7:13 am

fieldhopper:

You are not doing yourself any favours by including all sorts of extraneous code that may (or may not) be the cause of the errors you are encountering) e.g. this code ...
Code: Select all
dbpOpenTable "LEP" "schulen" ""
.dbpSort "LEP" "schulen" "SCH_NAME=ASC"
dbpFirst "LEP" "schulen"
Loop "1" "[lep.schulen.$RecCount]" "[result]"

... is likely to be problematic because, with CursorLocation=Server specified, there are no records available on the client side after a simple dbpOpenTable ... so there is no dbpFirst and $RecCount will be zero ... so the Loop command will likely balk !!! ... as an aside, it is not a good idea to use the database variable (like [lep.schulen.$RecCount]) in the Loop command ... as its value can change during the iterations of the loop depending on actions within the Loop/EndLoop block ... resulting in unexpected iterations of the loop.

Also, I am not familiar with debugbreakpoint "" ... if it is not part of the native NeoBook command set, remove it for the purpose of the test.


Did you try the code suggested in my last post ? ... if so, what was the result ?
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Wed Apr 27, 2016 4:45 am

Hey,
i have tested your code:
Code: Select all
.SetVar "[WindowState]" "maximized"
.debugbreakpoint ""
dbpShowErrors "Yes"
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=database;User=user;Password=password;Option=2;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"
.dbpOpenTable "LEP" "schulen" ""
.dbpSort "LEP" "schulen" "SCH_NAME=ASC"
.dbpFirst "LEP" "schulen"
.Loop "1" "[lep.schulen.$RecCount]" "[result]"
.ListBoxAddItem "ListBox1" "0" "[LEP.schulen.sch_id]-[LEP.schulen.sch_Name]"
.dbpNext "LEP" "schulen"
.endloop
.dbpSetGridProperties "LEP" "schulen" ""
.dbpQuery "LEP" "lehrer" "l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL)"
dbpExecSQL "LEP" "select * from lehrer where l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL) limit 5" ""
.dbpExecSQL "LEP" "select * from schulen limit 5" ""
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"
.dbpFirst "LEP" "lehrer"
.dbpShowGrid "LEP" "lehrer" "Rectangle1"


the result was alert as -1. i have chance the parameter option to 3 and it was the same result -1.
:?:
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Wed Apr 27, 2016 7:35 am

fieldhopper:

After I remove all the commented lines, I am left with ...
Code: Select all
dbpShowErrors "Yes"
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=server;Database=database;User=user;Password=password;Option=2;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"
dbpExecSQL "LEP" "select * from lehrer where l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL) limit 5" ""
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"

... if you remove the "CursorLocation=Server" (and change nothing else), does the second AlertBox return a different value ?
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Thu Apr 28, 2016 1:53 am

Have tried it. There are different value. The last alert shows 5 . That's correct, cause the sqlexec is limited to 5.
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Thu Apr 28, 2016 6:46 am

fieldhopper:

After establishing that ... the real (originating) problem is "an invalid record count when using CursorLocation=Server" ... which in turn causes other error messages when trying to show the contents of the returned records in a Grid ... I did some Google searches and found that there are several reports of "unreliable (including -1) count of records when specifying "CursorLocation=Server" ... some as early as 2006.

The only work around I saw (other than using "CursorLocation=Client") was a suggestion to follow your "SQL Query" with navigation commands to move to the Last record ... and then return to the First record ... in NeoBookDBPro, that would be dbpLast and dbpFirst ... perhaps you can try this and post your findings.
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Cursorlocation = server

Postby fieldhopper » Fri Apr 29, 2016 12:52 am

ok, next step.

this code i tried.

Code: Select all
debugbreakpoint ""
dbpShowErrors "Yes"
dbpOpenDatabase "LEP" "Provider=MSDASQL.1;Driver={MySQL ODBC 5.3 ANSI Driver};Server=server;Database=database;User=user;Password=password;Option=2;CursorLocation=Server"
dbpOpenTable "LEP" "lehrer" ""
dbpExecSQL "LEP" "select * from lehrer where l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL)" ""
dbpLast "LEP" "lehrer"
dbpFirst "LEP" "lehrer"
AlertBox "QueryCount" "[LEP.lehrer.$RecCount]"


The result: -1

Going away from your description, i hope i understand it right, i see only the way to use the standardoption CursoLocation=client. The other doesn't work. Is this right ?
Regards fieldhopper

____________________________________________________
Es sind schon viele erfroren, aber noch keiner ist ermieft.
____________________________________________________
User avatar
fieldhopper
 
Posts: 121
Joined: Sun Mar 09, 2008 11:39 am
Location: Germany

Re: Cursorlocation = server

Postby Gaev » Fri Apr 29, 2016 6:07 am

fieldhopper:

The result: -1

:( :( :( :(

Going away from your description, i hope i understand it right, i see only the way to use the standardoption CursoLocation=client. The other doesn't work. Is this right ?

It seems so ... of course, if your database is very large, CursorLocation=Client is not an option (as it will attempt to download the entire record set when you open the database).

There might be one more thing you could try.

Since my conclusion is that it is the "incorrect record count" that is the "root of all evil" ... and since you did not report any errors with dbpLast/dbpFirst commands ... it could be that the matching records are downloaded to the client side even if the count does not show.

Therefore, you might be able to access the contents of individual records by using dbpFirst and then dbpNext in a Loop/EndLoop ... for the Loop counter, you can get the record count from the database using another dbpExecSQL command ... something like ...
Code: Select all
dbpExecSQL "LEP" "select count(someFieldNameHere) as matchingRecordsCount from lehrer where l_pnr > '00000' and (l_adatum >= 'date(now())' or l_adatum is NULL)" ""


Perhaps other users who have deployed CusrorLocation=Server option (with this database/driver or others) can chime in with their experiences.

I am all out of other suggestions.
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests