Source language: Translate to:

MySQL questions

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL questions

Postby smartmedia » Sun Jul 22, 2007 6:59 am

Hi...

I am trying to get the last 100 records of a table... How i will do that...???
The table has Auto increase field.

Right now i use this

dbpExecSQL "Rap" "SELECT COUNT(*) from Data;" ""
SetVar "[Count]" "[Rap.Data.COUNT(*)]"
Math "[Count]-100" "" "[Count]"
dbpExecSQL "Rap" "SELECT * from Data LIMIT [Count],100;" ""

Any other solution more appropriate.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Neosoft Support » Mon Jul 23, 2007 10:12 pm

What about:

Code: Select all
SetVar "[Count]" "[Rap.Data.$RecCount]-100"
dbpExecSQL "Rap" "SELECT * from Data LIMIT [Count],100;" ""
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby smartmedia » Tue Jul 31, 2007 7:49 am

Hi...

Finaly i got it...

Code: Select all
dbpExecSQL "Rap" "SELECT Title from Data ORDER BY id DESC LIMIT 0,100;" ""
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby smartmedia » Sun Sep 23, 2007 8:36 pm

Hi,

I receive the following error, does anybody knows why...???

Cannot perform this operation on a closed dataset.

Thanks
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Mon Sep 24, 2007 5:05 am

smartmedia:

On Jul/31 you indicated that you got the specific dbpExecSQL command to work ... is the error now coming from the same place ? ... if so, what has changed since then ?

I did some Google searches ... sql error closed dataset ... and found a number of matching web pages ... including ...

http://www.dbforums.com/archive/index.php/t-950326.html

http://crm.ittoolbox.com/groups/technic ... =expanded#


... basically, it looks like the Database or Table are "Closed" (i.e. not "Open") ... this could be due to ...


- they were never opened ... either because the commands were never issued ... or because they failed due to security or other reasons

- they were closed ... explicitly via one of your script commands ... or implicityly due to other circumstances beyond your control

Place an AlertBox immediately before your dbpExecSQL command ... to show the values in [Rap.$Status] and [Rap.Data.$State].


P.S. In future, it will help to narrow down the possible culprits in a problem if you post detailed information about the circumstances surrounding your error situation ... including the database type, physical location ... whether this is a new error or an error that didn't exist before (and what changes caused it) ... and perhaps what investigative actions/responses you got before posting here ... this suggestion applies to anyone posting error problems in the NeoBookDBPro section of the forum.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Mon Sep 24, 2007 6:17 am

Hi...

Thanks Gaev for you comments, i hope to understand that is enough difficult for me to write in english the way you do.

The funny thing is that this procedure was working 100% and suddenly gives me that error. The code i use is a simple Insert into and after a Select. Here is the code:

Code: Select all
dbpExecSQL "Rapidshare" "INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang,ImageLink,www)|VALUES ('[Insert.Data.Title1]','[Insert.Data.Info1]','[Insert.Data.Category]','[Insert.Data.SubCategory]','[Insert.Data.Links1]','[Insert.Data.Pass]',CURRENT_TIMESTAMP(),'[Insert.Data.SizeCombined]',1,'[Users.Username]','[Insert.Data.Lang]','[Insert.Data.ImageLink]','[Insert.Data.www]');||" ""
dbpExecSQL "Rapidshare" "UPDATE Users SET Points=Points+20 WHERE Username='[Users.Username]';|" ""
dbpExecSQL "Rapidshare" "SELECT id,Title from Data where DateInserted >= TIMESTAMP(CURRENT_DATE) AND Status = 1 ORDER BY Title;" "Data1"


One other think that worry's me is that some times the \' & \" work and other dont...

Before i run the above querys i do that:

Code: Select all
StrReplace "[Insert.Data.Title]" "[#34]" "\[#34]" "[Insert.Data.Title1]"
StrReplace "[Insert.Data.Info]" "[#34]" "\[#34]" "[Insert.Data.Info1]"
StrReplace "[Insert.Data.Links]" "[#34]" "\[#34]" "[Insert.Data.Links1]"

StrReplace "[Insert.Data.Title1]" "'" "\'" "[Insert.Data.Title1]"
StrReplace "[Insert.Data.Info1]" "'" "\'" "[Insert.Data.Info1]"
StrReplace "[Insert.Data.Links1]" "'" "\'" "[Insert.Data.Links1]"


One other thing is: when i execute:
Code: Select all
dbpExecSQL "Rapidshare" "SELECT COUNT(*) from Users where OnLine = 1;" "Users"
SetVar "[UsersOnLine]" "[Rapidshare.Users.COUNT(*)]"


the variable [UsersOnLine] returns empty, Of cource the var is filled but for unknown reason return empty, if i run second time the query then it works...

The database i use is MySQL 4.1 standard.

Those problems are big for me, i am trying 2 month to find solution without hope...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Mon Sep 24, 2007 7:47 am

smartmedia:

a)
The funny thing is that this procedure was working 100% and suddenly gives me that error. The code i use is a simple Insert into and after a Select.

Did anything change ? ... different location (computer) for the database ? ... new version of plug-in ?

b) I see that your Data table has a field called "Size" ... as discussed before, this is a RESERVED word ... bookmark this page which contains a list of ALL RESERVED WORDS ... http://developer.mimer.com/validator/sq ... -words.tml

c) In your posted "code" I see a number of "pipe characters" (like |) ... can you explain what they are ?

d) Which of the dbpExecSQL commands returns the error message ?

e) What are the commands used to open databases and the tables ?

f)
One other think that worry's me is that some times the \' & \" work and other dont...

Where exactly does it not work ? ... the StrReplace command does not do its job (examine variable content using NeoBook's debug/trace facility) ? ... or is the content of the database messed up afterwards ?

g)
One other thing is: when i execute: ... the variable [UsersOnLine] returns empty, Of cource the var is filled but for unknown reason return empty, if i run second time the query then it works...

Try ...
Code: Select all
dbpExecSQL "Rapidshare" "SELECT COUNT(*) As CountOnlineUsers from Users where OnLine = 1;" "Users"
SetVar "[UsersOnLine]" "[Rapidshare.Users.CountOnlineUsers]"


h)
Those problems are big for me, i am trying 2 month to find solution without hope...

As stated before, this is a "Pro" plugin ... an aeroplane is much more difficult to fly than a tricycle is to ride.

As in life in general ... first you break a big problem down to smaller parts ... and zoom into each part in detail to see what (if any) unexpected behviour/result is happening.

Have a lab.pub where you have just the commands related to a problem ... with buttons to invoke each command separately ... and have TextEntry boxes, AlertBoxes/Text Boxes (to get feedback on key values), and dbpShowGrid as well as NeoBook's debug/trace facility ... and try one change at a time ... so you can tell where exactly it stops doing what you expect it to do.

Remember that unlike dbf files (and other parts of NeoBook), it is very difficult for other forum members to easily replicate your environment ... security, software versions etc. can make something work in one environment and not in another ... welcome to the :o real world of application development.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Mon Sep 24, 2007 10:05 am

Hi...

I don't know how to say thanks...
The think are a little complicated... I will try to enplane...

The error that comes up is because in a field the are the characters and and ' those characters represented from the text entry as " or 'wich is not the same. So when i do string replace those characters remain intact. That's why i get error.
I think this is something that must fixed from NB...

About those Pipes i really don't know how the entered, maybe are from beta release of NBPro, i have remove them.

Query with As seems that is working better... Thanks

The Size field is not on the list of forbidden words...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Mon Sep 24, 2007 10:33 am

smartmedia:
The Size field is not on the list of forbidden words...

According to this (unofficial) page ... http://developer.mimer.com/validator/sq ... -words.tml ... SIZE is a reserved word in SQL-92 and SQL-99 ... but not SQL-2003.

To be on the safe side, why not use something different ... and more descriptive ... e.g. SizeKB or FileSize etc.


Do you know the ASCII for those ( “ and ” )

I copied this to a Hex Editor ... and according to it ... the ascii codes are [#147] and [#148] ... but I see different characters for these codes in Ascii Code Tables ... because codes above [#128] are dependent on the "Code Table" in use ... so use it with caution ... or you might be removing another character ... like the letter o with an umlat and other accents.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Thu Sep 27, 2007 7:37 am

Hi...

Well, is quite strange, some times using dbpExecSQL with ' with \' inside the variable works and other dont. I can't explain it.

Example:
Code: Select all
Let's say tha this is the contains of a variable.


I replace ' with \' so MySQL can accept it but is not working everytime.

Any ideas...???
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Neosoft Support » Thu Sep 27, 2007 9:55 am

I think it would be helpful if you created a very small sample publication that demonstrates the problem you are having. This will help you narrow down the cause of the problem and help us understand what you are trying to do.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Gaev » Thu Sep 27, 2007 9:56 am

smartmedia:
Well, is quite strange, some times using dbpExecSQL with ' with \' inside the variable works and other dont. I can't explain it.
I know you are reluctant to be verbose on this forum because English is not your first language ... but how do you expect to get help if the people reading a comment like this can't figure out the details ... like where does it work ... and where does it not work ... show some NeoBookDBPro commands for each case.

Let's say tha this is the contains of a variable.

I replace ' with \' so MySQL can accept it but is not working everytime.
Are you trying to insert the contents of the variable in a record ? ... or are you trying to match the contents of the variable with records in your database ... and what means are you using to determine the contents of the database record(s) ?
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Thu Sep 27, 2007 10:41 am

Hi...

Both you right, i will try to make a sample application...

I be back...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron