Source language: Translate to:

MySQL Errors

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL Errors

Postby smartmedia » Sat Jun 23, 2007 10:07 am

Hi...

I use MySQL 4.1 standard. I have create a table with:
Code: Select all
dbpCreateTable "Rapidshare" "Data" "id AutoInc Key;Title Memo;
Info Memo;Category String(35);SubCategory String(30);Links Memo;
Pass String(20);DateInserted Date;Size String(6);Status String(1);
Username String(20);Lang String(20)"


Now i insert a record with:
Code: Select all
dbpExecSQL "Rapidshare" "INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang)|VALUES ('[Insert.Data.Title]','[Insert.Data.Info]','[Insert.Data.Category]','[Insert.Data.SubCategory]',
'[Insert.Data.Links]','[Insert.Data.Pass]',CURRENT_DATE,'[Insert.Data.SizeCombined]','2',
'[LoginUserName]','[Insert.Data.Lang]');||"


So far so good...
Now can somebody explain to me why when the field Info has the following text:

Code: Select all
Why PC Utilities? Like a car, PCs need maintenance and repair. Running PC diagnostics and utilities on your computer is the equivalent of taking your car in for a tune-up. Fix-It Utilities 7 Professional optimizes system performance and our computer diagnostics finds and prevents problems before they occur. In addition, have easy, scheduled maintenance to keep your PC in top shape

Fix-It Utilities™ Professional Features:
* Anti Spyware! Our updated Anti-Spyware engine provides continuous scanning or run on-demand operation finds and removes spyware, adware, Joke programs, Hack tools, remote access tools, password cracking applications and more. Also powered by Trend Micro to deliver world-class protection.
* Anti-Virus with Cleaner! Anti-Virus protection prevents your system from being infected with harmful viruses. Scan automatically or on-demand and remove found infections instantly.
* PC Diagnostics! Test audio, video, motherboard, memory, DVD, network card, and much more with the extra-powerful System Diagnostics!
* One-step Wizards for Easy Maintenance! Four easy-to-use FixWizards allows you to fix, clean, optimize your system or perform an all-in-one system tune-up automatically!
* Speed Up Program Launch Time! JETDeFrag™ optimizes your disk and helps to open programs and files faster!
* Emergency Response Manual! An extensive resource to help in the event of an emergency. Step-by-step instructions detail how to restore your system if it will not boot and how to recover critical data that's been lost.
* SMARTDiskCheck Early Warning System! SMARTDiskCheck detects when your hard drive is having problems so that you can take steps to protect your valuable data before it is lost.
* Ultimate Registry Tools! Speed performance even more by defragmenting the registry and fixing broken links.
* Recover Lost Data! Extra insurance in the case of an accidental file deletion or an attack from a virus. FileUndeleter can restore deleted files for you. Recovery Commander can offload files, even if your system won't boot!
* Clock Synchronizer! Keep your PC clock on time! Synchronize your PC with a known, reliable, accurate Internet time source. You can even schedule this to happen automatically!
* Windows Error Message Tracker! ErrorTracker collects and logs all Windows error messages to aid you in trouble-shooting system problems.
* Bootable Rescue Disk! The Fix-It Utilities 7 Professional CD is bootable and can help you get your system back, fix problems, perform basic partitioning actions such as create, format, delete, and more. Includes full support for all Windows file systems including NTFS.

New Features:
- VirusScanner™ Pro Improved! - VirusScanner Pro includes an updated Anti-Virus engine and cleaner that locates and removes more viruses than ever! Powered by Trend Micro to give you one of the best Anti-Virus protection available.
- Anti-Spyware New! - Our updated Anti-Spyware engine provides continuous scanning or run on-demand operation finds and removes spyware, adware, Joke programs, Hack tools, remote access tools, password cracking applications and more. Also powered by Trend Micro to deliver world-class protection.
- PCDiagnostics Improved! - New diagnostics covers USB, Firewire, DVD drives and Network cards. Other changes for improved analysis under Windows XP.
- Quick Status Screen New! - Now it's quicker and easier than ever before to get and use the extensive tools and information to monitor the status of your PC. One-click Protect and One-Click Optimize takes care of the details in one step, or select specific functions identified as problems. Event status summarizes files scanned, and viruses and Spyware detected since Fix-It was installed.
- Startup Optimizer and Manager™ New! - Identifies and lets you remove start up programs that slowdown and may even harm your system. Includes an extensive database of programs and services to help identify what many items do, their importance and security risk.
- Easy Update Improved! - New differential updates for Anti-Virus and Anti-Spyware pattern files are far smaller and faster to download.


gives me the response:
The table "a" could not be opened.
And of course the new record is not accepted by SQL...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Neosoft Support » Mon Jun 25, 2007 4:30 pm

The block of text you're inserting to the info field contains at least one apostrophe (') character, which your SQL code uses to delineate text. When the variables in your SQL script are populated, the extra apostrophe causes a syntax error which is probably confusing MySQL's parser.

You could try using double quotes instead. For example:

Code: Select all
dbpExecSQL "Rapidshare" "INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang)|VALUES ([b][#34][/b][Insert.Data.Title][b][#34][/b],...
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby smartmedia » Tue Jun 26, 2007 12:10 am

Hi...

Thanks Dave,
Is this something that MySQL do or the way NB works because if i know correctly MySQL uses apostrophe for alphanumeric strings...
Any way i try it the way you told me...

Later on:
I have found a section that explain.

9.1.1. Strings
A string is a sequence of bytes or characters, enclosed within either single quote (‘'’) or double quote (‘"’) characters. Examples:

'a string'
"another string"

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotes because a string quoted within double quotes is interpreted as an identifier.

As of MySQL 4.1.1, a binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.

Also as of MySQL 4.1.1, string literals may have an optional character set introducer and COLLATE clause:

[_charset_name]'string' [COLLATE collation_name]

Examples:

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (‘\’), known as the escape character. MySQL recognizes the following escape sequences:

\0 An ASCII 0 (NUL) character.
\' A single quote (‘'’) character.
\" A double quote (‘"’) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control-Z). See note following the table.
\\ A backslash (‘\’) character.
\% A ‘%’ character. See note following the table.
\_ A ‘_’ character. See note following the table.

For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, ‘\x’ is just ‘x’.

These sequences are case sensitive. For example, ‘\b’ is interpreted as a backspace, but ‘\B’ is interpreted as ‘B’.

The ASCII 26 character can be encoded as ‘\Z’ to enable you to work around the problem that ASCII 26 stands for END-OF-FILE on Windows. ASCII 26 within a file causes problems if you try to use mysql db_name < file_name.

Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed in Section 10.3.5, “Character String Literal Character Set and Collation”.

The ‘\%’ and ‘\_’ sequences are used to search for literal instances of ‘%’ and ‘_’ in pattern-matching contexts where they would otherwise be interpreted as wildcard characters. See the description of the LIKE operator in Section 12.4.1, “String Comparison Functions”. If you use ‘\%’ or ‘\_’ in non-pattern-matching contexts, they evaluate to the strings ‘\%’ and ‘\_’, not to ‘%’ and ‘_’.

There are several ways to include quote characters within a string:

A ‘'’ inside a string quoted with ‘'’ may be written as ‘''’.

A ‘"’ inside a string quoted with ‘"’ may be written as ‘""’.

Precede the quote character by an escape character (‘\’).

A ‘'’ inside a string quoted with ‘"’ needs no special treatment and need not be doubled or escaped. In the same way, ‘"’ inside a string quoted with ‘'’ needs no special treatment.

The following SELECT statements demonstrate how quoting and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+

If you want to insert binary data into a string column (such as a BLOB column), the following characters must be represented by escape sequences:

NUL NUL byte (ASCII 0). Represent this character by ‘\0’ (a backslash followed by an ASCII ‘0’ character).
\ Backslash (ASCII 92). Represent this character by ‘\\’.
' Single quote (ASCII 39). Represent this character by ‘\'’.
" Double quote (ASCII 34). Represent this character by ‘\"’.

When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:

Process the string with a function that escapes the special characters. In a C program, you can use the mysql_real_escape_string() C API function to escape characters. See Section 17.2.3.51, “mysql_real_escape_string()”. The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences. See Section 17.4, “MySQL Perl API”. Other language interfaces may provide a similar capability.

As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.



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

Postby Neosoft Support » Tue Jun 26, 2007 10:28 am

Is this something that MySQL do or the way NB works because if i know correctly MySQL uses apostrophe for alphanumeric strings...


It's not something unique to NeoBook. DBPro just passes whatever you give it directly to mySQL.

I found the following in the docs you posted:

There are several ways to include quote characters within a string:

A ‘'’ inside a string quoted with ‘'’ may be written as ‘''’.
A ‘"’ inside a string quoted with ‘"’ may be written as ‘""’.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby smartmedia » Mon Jul 02, 2007 3:09 am

Hi...

Well i have replace in fields all ' with [#34] but still i get errors. So reading the above i tried to replace all ' and "" with \' and \"" but i still get error. Any idea...

Here is the text which respond error...

Code: Select all
The Fastest P2P File Sharing Program on the Planet, running on the Gnutella Network. It is open standard software running on an open protocol, free for the public to use. LimeWire allows you to share any file such as.mp3s, .avis, jpgs, tiffs, etc., allows you to search for multiple files at the same time, available in several different languages, and is most famous for its ease-of-use and cross-platform compatibility. Limewire is written in Java, and will run on Windows, Macintosh, Linux, Solaris, and other computing platforms.

1. NO BUNDLED SOFTWARE OF ANY KIND! No spyware. No adware. Guaranteed.
2. Firewall to Firewall Transfers. Since about 60% of users are currently firewalled, this feature greatly increases the amount of content on the network.
3. Faster network connections. Using new "UDP Host Caches", LimeWire starts up and connects faster then ever before!
4. Universal Plug 'N Play. UPnP support allows LimeWire to find more search results and have faster downloads.
5. iTunes Integration. Windows users can now take advantage of LimeWire's iTunes integration.
6. Creative Commons Integration. LimeWire now recognizes OGGs and MP3s licensed under a Creative Commons License.
7. "What's New?" feature. Users can browse the network for the most recent content additions.
8. Search drill down results. Searches in LimeWire now immediately display the artists, albums and other information that fully describes files.
9. Proxy support. Users can now use web proxies to route their to protect their identity.
10. Support for International searches and International groups. Users can now search in any language, and LimeWire ensures that a user will be connected to other users with their own language to aide international users to receive search results in their native language and to find content from sources that are close to home.

Features Of LimeWire:
- Ease of use - just install, run, and search
- Ability to search by artist, title, genre, or other metainformation
- Elegant multiple search tabbed interface
- "Swarm" downloads from multiple hosts help you get files faster
- iTunes integration for Mac and Windows users
- Unique "ultrapeer" technology reduces bandwidth requirements for most users
- Integrated chat
- Directly connect to a computer
- Browse host feature--even works through firewalls
- Added Bitzi metadata lookup
- International versions: Now available in many new languages
- Connects to the network using GWebCache, a distributed connection system
- Automatic local network searches for lightning-fast downloads
- Support for MAGNET links that allow you to click on web page links that access Gnutella


The actual error says:
Parameter object is improperly defined. Inconsistent or incomplete information was provided.


It start to get on my nerves.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Mon Jul 02, 2007 5:30 am

smartmedia:

Just from reading the section 9.1.1, it could be a number of things that are causing the error ... e.g. carriage return/line feed characters, tab characters, whether ANSI_QUOTES SQL mode is enabled etc. ... so your first objective should be to identify the exact character(s) where the plug-in returns an error.

Why don't you do this via a series of trials ... start with the variable Info containing ...
Code: Select all
Why PC Utilities? Like a car, PCs need maintenance and repair. Running PC diagnostics and utilities on your computer is the equivalent of taking your car in for a tune-up.

... i.e. just a short text string with alpha-numeric and punctuation characters only ... and if that works, add some more of the text e.g.
Code: Select all
Why PC Utilities? Like a car, PCs need maintenance and repair. Running PC diagnostics and utilities on your computer is the equivalent of taking your car in for a tune-up. Fix-It Utilities 7 Professional optimizes system performance and our computer diagnostics finds and prevents problems before they occur. In addition, have easy, scheduled maintenance to keep your PC in top shape

Fix-It Utilities™ Professional Features:
* Anti Spyware! Our updated Anti-Spyware engine provides continuous scanning or run on-demand operation finds and removes spyware, adware, Joke programs, Hack tools, remote access tools, password

... etc. etc. until you reach a point where the plug-in returns an error ... once you know what the offending text (or text length) is, you can go about finding a work around.
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby dpayer » Mon Jul 02, 2007 9:12 am

smartmedia wrote:Hi...


The actual error says:
Parameter object is improperly defined. Inconsistent or incomplete information was provided.


It start to get on my nerves.


It appears apostrophes are not permitted, you need to convert them to the ascii code.

See:
http://p2p.wrox.com/topic.asp?TOPIC_ID=321

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

Postby smartmedia » Mon Jul 02, 2007 1:04 pm

Hi...

Thanks Gaev and David.

After long play with this i found that if you have inside the text a single apostrophe or a single quote is not working. Now if apostrophe or quotes are on pears, which means 'bla.bla.bla' or "bla.bla.bla" everything is fine.
Really strange behavior because i change all apostrophes and quotes with stringReplace command like this.

Before ' "
After \' \"

The only solution i see is:
a) delete all apostrophes and quotes from string
b) Count apostrophes and quotes to be in pears, if not add 1 or delete 1.

Any one to have better solution or 1other approach.

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

Postby smartmedia » Mon Jul 02, 2007 2:21 pm

Hi...

One other strange think for me is that if i use dbpExecSQL and insert into command i get the error. If i just use a text entry field with the database variable it accepts everything without any error...
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Mon Jul 02, 2007 3:38 pm

smartmedia:
a) If you are using this syntax ...
Code: Select all
dbpExecSQL "Rapidshare" "INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang)|VALUES ('[Insert.Data.Title]','[Insert.Data.Info]','[Insert.Data.Category]','[Insert.Data.SubCategory]',
'[Insert.Data.Links]','[Insert.Data.Pass]',CURRENT_DATE,'[Insert.Data.SizeCombined]','2',
'[LoginUserName]','[Insert.Data.Lang]');||"

... Dave (NeoBookDBPro) would pass to the ODBC driver ...
Code: Select all
INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang)|VALUES ('[Insert.Data.Title]','[Insert.Data.Info]','[Insert.Data.Category]','[Insert.Data.SubCategory]',
'[Insert.Data.Links]','[Insert.Data.Pass]',CURRENT_DATE,'[Insert.Data.SizeCombined]','2',
'[LoginUserName]','[Insert.Data.Lang]');||
... and if ...

[Insert.Data.Title] contained the value abc
and
[Insert.Data.Info] contained the value smart'media

... it would pass something like ...
Code: Select all
INSERT INTO Data (Title,Info,Category,SubCategory,Links,Pass,DateInserted,Size,Status,Username,Lang)|VALUES ('abc','smart'media',' ... etc.

... which might confuse the receiving code if it was looking purely for comma separated apostrophed strings ... hence the need to replace all ' characters with escape codes \'.

b) you said in your first post that I use MySQL 4.1 standard ... and the reference documentation is for MySQL 4.1.1 ... could it be that the older version does NOT support specification of \' escape code methods ?

c)
The only solution i see is:
a) delete all apostrophes and quotes from string
b) Count apostrophes and quotes to be in pears, if not add 1 or delete 1.

Any one to have better solution or 1other approach.

How about ...

c) If you are using apostrophes around the variables, replace all instances of apostrophes within the variables with two of the same ...
Code: Select all
StrReplace "[Insert.Data.Info]" "[#39]" "[#39][#39]" "[Insert.Data.Info]"
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby smartmedia » Tue Jul 03, 2007 1:13 am

Hi
Gaev
b) you said in your first post that I use MySQL 4.1 standard ... and the reference documentation is for MySQL 4.1.1 ... could it be that the older version does NOT support specification of \' escape code methods ?


This is the only i can think of, because i test again and seems that the \ is not taken under consideration by SQL... In the offical site they say that is supported by version 4.0

Some times i get crazy with all this stupid situation, those people who making this are trying to make our life better or worse...???
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby smartmedia » Sat Sep 08, 2007 4:08 am

Hi...

I get the following error, when i execute an dbpExecSQL with INSERT INTO or with UPDATE and i put a result table, any idea why...???

CommandText does not return a result set


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

Postby Neosoft Support » Mon Sep 10, 2007 11:19 am

Please post the exact SQL code you're using.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby smartmedia » Mon Sep 10, 2007 8:19 pm

Hi...

This is the commands i use.

dbpExecSQL "Rap" "UPDATE Users SET OnLine = 1 WHERE Username = '[LoginUserName]';" "Users"


dbpExecSQL "Rap" "INSERT INTO Report (id,Username,Type,Status,Reason)|VALUES ('[Data.id]','[Users.Username]','[ComboBox10]',2,'[TextEntry19]');" "Report"

if there is a table like the above example i get the message.
Thanks
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Neosoft Support » Tue Sep 11, 2007 10:03 am

The error is correct because those commands don't return any results so there is nothing to put into the results table. You should only supply a results table when you expect data to be returned. Remove the results table and the error will go away. For example:

dbpExecSQL "Rap" "UPDATE Users SET OnLine = 1 WHERE Username = '[LoginUserName]';" ""
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests