?
Solved

Delphi/MyQL EAccessViolation exception on INSERT/UPDATE

Posted on 2005-04-15
14
Medium Priority
?
1,439 Views
Last Modified: 2008-01-09
I'm accessing a local MySQL db with a TADOQuery connected via a TADOConnection
some of my code:

sql := 'insert onto car set reg_plate="123-456x", make="merc", model="kompressor";';
        query.SQL.clear();
        query.SQL.add(sql);
        rows:=query.ExecSQL;
NB:
query is my TADOQuery object, connected to the DB via TADOConnection.

On Run, SELECT works fine, & DELETE but, Insert/update result in this error message:
"project ciimsfp.exe raised exception class EAccessViolation with message 'Access violation at address 1F453E7E in module 'msado15.dll'. Write of address 00BFA004'. Process stopped..."

then fails to insert into DB. The same code actually inserts if I use query.open() instead of query.ExecSQL() although it raises an error that SQL does not return a result.
NB: the SQL is valid for my DB! I'm using MyODBC 3.51 & its BDE config seems OK.

How do insert/update into the DB without raising an exception?

more2chance
0
Comment
Question by:more2chance
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 13788808
1) about executing
Query.Open should be used when your SQL statements returns result - it mean just for SELECT statements
Query.ExecSQL is used for statements, that doesn't return result - INSERT, DELETE, UPDATE

2) your query is wrong
There is no INSERT INTO .... SET statement. You are messing up the SQL
  - INSERT INTO table_name (column1, column2) VALUES (value1, value2)
    or
    INSERT INTO table_name VALUES (value1, value2, ..., valueN) <- here you should supply values for all columns
  - the other statement is
    UPDATE table_name
    SET column1 = value1_new
    WHERE column1 = value1

3)
something else - use parameterized queries, example:
SQL := 'INSERT INTO CAR (REG_PLATE, MAKE, MODEL) VALUES (:reg_plate, :make, :model)'
Query.Params.ParamByName('make').Value := 'Mercedes';
...
Query.ExecSQL;
This way the query itself is responsible for parsing the parameters.
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 13795624
>>NB: the SQL is valid for my DB! I'm using MyODBC 3.51 & its BDE config seems OK.

but you are not using BDE, you are using ADO... can you show your ADO Connection string?
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 13796625
I don't think "INSERT INTO ... SET" is a valid statement...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:more2chance
ID: 13796663
Black TigerX
My connection string must be fine coz I built it using the dialog that appears at design time. I will send it later. Also, the fact that SELECT works suggests that the connection string is fine.

Ivanov G
My form of insert/update actually changes DB contents. Maybe it works but results in an error?

I will try the suggested syntax:-) although the parameterized appears quite wordy - too much typing!

ILBBL8R!
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 13796817

Hi

Others are correct the Insert statement is impossible......

Set is only ever used in an Update statement.......

Voodooman
0
 

Author Comment

by:more2chance
ID: 13823207
OK, yes the INSERT was wrong, I've corrected it but the error message still occurs.
Here's my connection string:
adqDBquery.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Extended Properties="DSN=fparlour;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=fparlour;SERVER=localhost;UID=admin;PASSWORD=;PORT=3306;OPTION=3;STMT=;";Initial Catalog=fparlour'

NB: I ain't using a password. I think it's to do with object create/destroy because I'm passing the adqDBquery component from another form unit to this class unit. I tried creating and connecting every time I want to change DB components. But I want to create, connect once & continue manupulating my DB without seeing this ugly message.

I say it's nothing to do with SQL or even the DB coz u'll see the same message if you try to access a declared object before calling it constructor.

well???

more2chance
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 13823690

Passing objects around seems a bit doubtful with Delphi.  I have been looking at issues around this recently.

You should put your connection and recsets in a Data Module which works fine.

If you still have trouble download and install Data Access Objects 2.8 from Microsoft and get the tool from the website for checking your data components.

After, unisnstall and re-install the drivers for MYSQL.  Try your app on another PC if possible and see if the erro re-occurrs.

Voodoo
0
 

Author Comment

by:more2chance
ID: 13831712
Thanx Voodoo, but;
What's a Data Module?
Can DAO 2.8 give me an SQL connection to MySQL?
About passing objects around, I can pass my custom objects fine.

more2chance
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 13838876

In Delphi

File|New|DataModule

For DAO 2.8 read MDAC 2.8.

To use ADO you must have the right file set of ADO components - these are in the MDACType.exe from Microsoft.


Voodooman
0
 

Author Comment

by:more2chance
ID: 13903635
I had problems downloading the MDACtyp.exe from microsoft. Or can you send to my e-mail(n001326c@students.nust.ac.zw - but change the file extension to .TXT coz my server removes all executable attachments).

I often get this "too many connections error message"(that's why I've increased the points for this question.
0
 
LVL 5

Accepted Solution

by:
Voodooman earned 1500 total points
ID: 13903985

Too many connections means exactly what it says.

Most Servers can be setup for how many connections to a database server it can accept.

This is because each connection takes xx mb of memory to keep alive. In SQLServer 7 it took (I think) 256kb per connection. 100 connections would thus mean 25mb of memory consumed.  In those days we were running servers with 64mb of ram with 100 seats.

As you can see it was necessary to restrict the number of connections to prevent the memory resources being consumed.

Now, it is possible for every dataset to establish its own connection.  So, if you have a master detail display and a couple of lookup combo's on the same form you can straight away have 4 connections open. With a Popup edit form you could soon have 10 connections open.  Your server will require 10 x xxmb to maintain all these connections.

This is why you should be setting up all your datasets on a connection or session object. (Session and connection pretty much the same  thing).  If you setup 20 x datasets on 1 connection (or session) only 1 x xxmb is required to maintain the connection on your server, thus conserving your servers resources.

In the configuration of your server you will be able to set the maximum number of permissable connections.  You need to read the documentation to do this.

Make sure you are opening all your datasets on one connection is your first job.

Please do not now ask the question 'how do I open all my datasets on the same connection'.

It is unfair to keep asking more and more questions within the same question, it is an abuse of  Experts Exchange.

Experts answer questions for the fun of points and to help others.  The points are absolutely useless to me as they are not money!

Please now award the points and close the question - fair is fair.

Voodooman


0
 

Author Comment

by:more2chance
ID: 13949751
Thanx,
sorry...I was gonna ask - how do I 'close' each TADOQuery connection. I'm not using any data-aware controls.
0
 

Author Comment

by:more2chance
ID: 13949753
Don't worry yu've got the points already - just answer the last one.
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 13950271

Hi

Don't use TADOQueryConnection myself so can't be specific.

Usually its by setting the .active property to false.

i.e. TADOQuery.active:=false;

If you are only using the TADOConnection object, set .connected:=false;

Closing your application should close all connections automatically as the objects are destroyed.

I don't use ADO myself with Delphi.  The reason I use Delphi is for better database support.  My database of choice is DBISAM from Elevate Software - best product available in my opinion!

Voodooman


0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question