Re: [firebird-support] Re: Handling deadlocks with classic mode
Thanks for the response. We are running Windows. I'm not sure how much information about the fb_inet_server processes I can gather -- other than how long they've been running. Not sure how to get the IP address of the connecting client. I'm going to just start killing the oldest fb_inet_server processes, but I don't know how I can distinguish the listener process. If I kill that one by mistake, I guess I'll have to reboot the whole server because I don't know how to restart just the listener. Is there a way to identify the listener process? If it runs as a Windows service, open SysInternals ProcessExplorer. You should see a fb_inet_server.exe process under the winit.exe/system.exe node. Restarting the listener process is simply restaring the service (if it runs as a Windows service) or by executing fb_inet_server.exe with the -a (as an application) switch. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ vince --- In firebird-support@yahoogroups.com, Steve Wisersteve@... wrote: What OS are you running on the server? For Classic, if you can find the offending process you can just kill that particular process to resolve deadlocks or long running queries. We normally use linux and find the processes using lsof to get the open connection to the db tied back to the IP address of the offending computer. I am not sure how to do that on Windows though. -steve -- Steve Wiser President Specialized Business Software 6325 Cochran Road, Unit 1 Solon, OH 44139 www.specializedbusinesssoftware.com www.docunym.com (440) 542-9145 - fax (440) 542-9143 Toll Free: (866) 328-4936 On Tue, Oct 18, 2011 at 9:44 PM, vincent999xvincent999x@... wrote: ** hello, We recently switched from superserver mode to classic mode (still stuck on Firebird 1.5 unfortunately) to take advantage of the 2nd CPU on the server and to prevent intensive queries from slowing down the whole system. It has worked out well except that we seem to see more deadlocks now. Currently, we have one user who has been unable to update a record for 3 days now (keeps getting a deadlock condition), so it seems that as a last resort I might have to restart Firebird to break this deadlock. Here's my question -- how do I do that? Do I have to kill all of the fb_inet_server processes? The easiest way for me is to reboot the server, but is there a better way? We have a GUI tool (Easy-IP DB manager), but it only kills the Firebird listener process. Two other things I've noticed with classic vs. superserver: 1) I'm having more trouble altering stored procedures. It seems classic mode won't let you update a stored procedure if it's currently being run. I don't recall this problem with superserver mode. 2) In superserver mode, when I abnormally terminated from IBExpert (our SQL query tool) due to the query taking too long -- the system response time improved immediately. However, in classic mode, the fb_inet_server process continues to run even though I abnormally terminated from IBExpert. Thanks in advance for any assistance. I know the long-term solution is to write better code, but for the short-term I need a way to break deadlocks without rebooting the database server constantly. vince This message and any files transmitted with it may contain information that is privileged, confidential, and exempt from disclosure under applicable law. They are intended solely for the use of the intended recipient. If you are not the intended recipient, distributing, copying, disclosing, or reliance on the contents of this communication is strictly prohibited. If this has reached you in error, kindly destroy this message and notify the sender immediately. Thank you for your assistance. We attempt to sweep harmful content (e.g. viruses) from e-mail and attachments, however we cannot guarantee their safety and can accept no liability for any resulting damage. The recipient is responsible to verify the safety of this message and any attachments before accepting them. [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
[firebird-support] Re: Find out how a user has connected to the database
Thomas We would be connected from Delphi and all our clients are on 2.1. But before I get this implemented in code I would like to see it in SQL. I have tried in both IBQuery and isql but whenever I run the following: select * from MON$ATTACHMENT; I always get an error that the table is unknown. Should I be able to run that statement from isql? Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote: Hi Neil, Thanks for the quick response. I have a few further questions... When I try and select from this table I get a message to say that it doesn't exist. I am logged in as sysdba - do I need to do anything else? My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 I assume that the remote process property has to be set as part of the connection string? And finally, I assume that this is available via an api? The following is from the 2.1 release notes. - columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values only if the client library is version 2.1 or higher - column MON$REMOTE_PROCESS can contain a non-pathname value if an application has specified a custom process name via DPB But I know from Jaybird for instance, that one needs to specify specific properties, see: http://tracker.firebirdsql.org/browse/JDBC-118 But I guess this is related to the thin driver not communicating via the client library. What access technology do you use and is your mentioned gds32.dll version the one on the client or do we speak about a local installation here anyway? -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurerts@ wrote: Neil, I know that it is possible to see who is currently connected to a database but is it possible to see how they have connected? By which I mean is it possible to see whether they are connected from, say, IBConsole, IBQuery or an application? MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 of the client libraries. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
Re: [firebird-support] Re: Find out how a user has connected to the database
Write it as select * from MON$ATTACHMENTS; You were missing the last S Alex - Original Message - From: grip_2ls li...@2ls.com To: firebird-support@yahoogroups.com Date: Thu, 20 Oct 2011 07:54:42 - Subject: [firebird-support] Re: Find out how a user has connected to the database Thomas We would be connected from Delphi and all our clients are on 2.1. But before I get this implemented in code I would like to see it in SQL. I have tried in both IBQuery and isql but whenever I run the following: select * from MON$ATTACHMENT; I always get an error that the table is unknown. Should I be able to run that statement from isql? Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote: Hi Neil, Thanks for the quick response. I have a few further questions... When I try and select from this table I get a message to say that it doesn't exist. I am logged in as sysdba - do I need to do anything else? My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 I assume that the remote process property has to be set as part of the connection string? And finally, I assume that this is available via an api? The following is from the 2.1 release notes. - columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values only if the client library is version 2.1 or higher - column MON$REMOTE_PROCESS can contain a non-pathname value if an application has specified a custom process name via DPB But I know from Jaybird for instance, that one needs to specify specific properties, see: http://tracker.firebirdsql.org/browse/JDBC-118 But I guess this is related to the thin driver not communicating via the client library. What access technology do you use and is your mentioned gds32.dll version the one on the client or do we speak about a local installation here anyway? -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurerts@ wrote: Neil, I know that it is possible to see who is currently connected to a database but is it possible to see how they have connected? By which I mean is it possible to see whether they are connected from, say, IBConsole, IBQuery or an application? MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 of the client libraries. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
[firebird-support] Re: Find out how a user has connected to the database
Hi Thanks all for the information. The database is ODS 10.1 so I guess that is the problem. Thanks you all for your help. I will upgrade the db. Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote: Hello Neil, We would be connected from Delphi and all our clients are on 2.1. But before I get this implemented in code I would like to see it in SQL. I have tried in both IBQuery and isql but whenever I run the following: select * from MON$ATTACHMENT; I always get an error that the table is unknown. Should I be able to run that statement from isql? Yes, but it's MON$ATTACHMENTS and not MON$ATTACHMENT, sorry. This also requires a database with an ODS 11.1 or higher database. To check the ODS, e.g. use gstat -h or AFAIR a: SHOW DATABASE; in isql shows the ODS as well. So, if you are connecting with a 2.1 server to an older (pre ODS 11.1) database, the monitoring tables aren't available. To upgrade a database to the server ODS, run a backup with the old Firebird server and a restore with 2.1. HTH. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurerts@ wrote: Hi Neil, Thanks for the quick response. I have a few further questions... When I try and select from this table I get a message to say that it doesn't exist. I am logged in as sysdba - do I need to do anything else? My db is 2.1.3.18185 and the GDS32.dll is version 6.3.3.18185 I assume that the remote process property has to be set as part of the connection string? And finally, I assume that this is available via an api? The following is from the 2.1 release notes. - columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values only if the client library is version 2.1 or higher - column MON$REMOTE_PROCESS can contain a non-pathname value if an application has specified a custom process name via DPB But I know from Jaybird for instance, that one needs to specify specific properties, see: http://tracker.firebirdsql.org/browse/JDBC-118 But I guess this is related to the thin driver not communicating via the client library. What access technology do you use and is your mentioned gds32.dll version the one on the client or do we speak about a local installation here anyway? -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ Thanks Neil --- In firebird-support@yahoogroups.com, Thomas Steinmaurerts@ wrote: Neil, I know that it is possible to see who is currently connected to a database but is it possible to see how they have connected? By which I mean is it possible to see whether they are connected from, say, IBConsole, IBQuery or an application? MON$REMOTE_PROCESS in MON$ATTACHMENT, but AFAIK this needs at least V2.1 of the client libraries. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/ ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
Re: [firebird-support] Re: Find out how a user has connected to the database
At 09:31 PM 20/10/2011, grip_2ls wrote: Hi Thanks all for the information. The database is ODS 10.1 so I guess that is the problem. Thanks you all for your help. I will upgrade the db. ...after CAREFULLY studying the v.2.1 release notes and migration/compatibility guide, natch. ;-) You have a major ODS upgrade taking a v.1.5 database through to ODS 11.0 and another leap to 11.1. ./heLen
[firebird-support] Re: Find out how a user has connected to the database
Thanks Helen, I have just been looking at your Firebird 2 Supplement to work out just exactly how I do it! Neil --- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote: At 09:31 PM 20/10/2011, grip_2ls wrote: Hi Thanks all for the information. The database is ODS 10.1 so I guess that is the problem. Thanks you all for your help. I will upgrade the db. ...after CAREFULLY studying the v.2.1 release notes and migration/compatibility guide, natch. ;-) You have a major ODS upgrade taking a v.1.5 database through to ODS 11.0 and another leap to 11.1. ./heLen
[firebird-support] Re: Firebird Program loses network connection
Hi, Thanks everyone for replying. Here a quick update on the testing. Put in a laptop to replace the server and the problem is gone. Took the client's server back for testing and unsurprisingly the problem could not be reproduced anymore at my office. Going to perform few test on the server before deciding to reformat the server. Thanks and regards, John --- In firebird-support@yahoogroups.com, gwc8182 gwc8182@... wrote: Hi everyone, I have encountered a weird network problem and have no idea what might be the cause of it. Background info: Using Firebird 2.0.5 Program is written on Delphi with BDE. Server is Windows 7 Ultimate Clients are running mixed of Windows 7 Starter, Home Premium, WinXP, Vista Home Premium. Client connection path to the database is using TCP/IP Situation: Client's program frequently shows Error writing data to the connection An existing connection was forcibly closed by the remote host Client stated it happened after using the program for some time. As testing, I tried saving couple of big transaction on the client computer and will encountered the same error. Note: Processing time for the transaction takes less then 3 mins. Initially thought might be related to memory but hit the same problem with a computer with 2 GB memory. Tried adjusting memory usage in Firebird.conf file but makes no different. Firebird.conf was then restore back to original to continue testing for other possibility. Testing on network so far shows it is stable. Perform test by pinging the server and then transferring file to the server while the program is running. Will get the same error above but ping and file transfer continue without hiccup. Perform another test by having two program open. Only the program running the test above will crashed with error and the second program can continue being used without any problem. Server Firebird's log file only recorded 10054 error. Tried running both SuperServer and Classic mode and tried Firebird 2.5.1 as well but no go. Also suspected something is not right with the program but running the same test at my own office with lower end computer and much heavier transaction will not have any problem. I pretty much given up and not sure what so unique about this client network that keep getting this error. Thanks and regards, John
Re: [firebird-support] Re: Firebird Program loses network connection
On Thu, 20 Oct 2011 11:18:23 -, gwc8182 gwc8...@yahoo.com wrote: Hi, Thanks everyone for replying. Here a quick update on the testing. Put in a laptop to replace the server and the problem is gone. Took the client's server back for testing and unsurprisingly the problem could not be reproduced anymore at my office. Going to perform few test on the server before deciding to reformat the server. This could also mean that it is some piece of network equipment (router, switch, firewall appliance) is interfering.
Re: [firebird-support] Re: Firebird Program loses network connection
John, On 20/10/11 12:18, gwc8182 wrote: Took the client's server back for testing and unsurprisingly the problem could not be reproduced anymore at my office. Ok, there's a clue there. The network at the customer site. Well, the infrastructure at the customer site. Going to perform few test on the server before deciding to reformat the server. Reformat the server is a tad drastic. Sounds like a Microsoft Certified Engineer type thing! duck and runs ;-) There is, I'd bet, a network configuration somewhere that's causing this. Have you tried, as was suggested, leaving a couple of ssh sessions (or something else that connects and then sits idle) open, but idle, to see if they disconnect as well? I'd be willing to bet that a reformat of the server will not solve the problem unless you reinstall Windows (?) and reconfigure it to not kill dead connections. The links I gave in my previous reply might give you a clue as to where to look for the setting. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT
Pepek, The normal cause of wild variations in the performance of queries is garbage collection... Garbage collection doesn't seem to play any significant part. I even deleted the whole backup table, performed the backup/restore cycle, and still got long times. What *did* help was limiting the SELECT from sourcetable to significant records, some 6000 of them (a reduction of two orders of magnitude). But it still seems strange that Firebird would choke on INSERTs, even in what I perceive to be the ideal condictions to the server. OK, then we'll need to look for less common problems. When the query is slow, are you seeing lots of page faults? Process size growth? What is the rate of reads/writes/marks/fetches? Do you have any guess as to where the time is going? Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Re: How to insert only if a matching row does not exist?
--- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote:
What is the analogous way to achieve this in Firebird (2.5) ?
INSERT INTO emp (fruits) values ('mango')
where not exists (select 1 from emp where fruits = 'mango')
Thanks for the quick reply.
Strange... when I try to do this (via flamerobin or ibexpert) it does not
recognize the Where token (Dynamic SQL error -104, Token unknown, where ).
Must be something simple :(
Here is what I am trying:
I have a table like the following:
CREATE TABLE T1 (
ID INTEGER NOT NULL,
TYPE INTEGER,
NAME VARCHAR(128),
SYSIDINTEGER,
FLAG INTEGER
);
ID has a generator so I don't want to insert a value for it.
Here is the query I tried
INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0)
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z')
It
Look up INSERT OR UPDATE and also MERGE in the Language Reference Update.
One or both might do what you want, more efficiently than NOT EXISTS. Not
enough info here to guess what you're really going to do with it.
My application is really to write a script that will be run once on a smallish
dataset (on the order of 1000 rows) so performance isn't really a big deal.
Thanks for the pointer. I did look at INSERT OR UPDATE prior to posting but I
do not want to UPDATE if there is a match so I didn't think this applied.
[firebird-support] Firebird Embedded Deadlock problems.
I'm using a firebird embedded 2.5.0.26074 database via latest ado provider
which contains a simple table
CREATE TABLE INVENTORY (
ID ID NOT NULL /* ID = VARCHAR(36) NOT NULL */,
EXPIRYTIME EXPIRYTIME NOT NULL /* EXPIRYTIME = BIGINT NOT NULL */,
ITEMSIZEITEMSIZE /* ITEMSIZE = BIGINT NOT NULL */,
ACCESSCOUNT ACCESSCOUNT DEFAULT 1 NOT NULL /* ACCESSCOUNT = INTEGER
DEFAULT 1 NOT NULL */,
LASTACCESSTIME LASTACCESSTIME /* LASTACCESSTIME = TIMESTAMP NOT NULL */
);
To tally the sum of all ITEMSIZE rows two triggers where created on the
INVENTORY table to add or subtract the value in ITEMSIZE to a TOTALSIZE Column
(Single row) in table STATS as a row was added or removed.
CREATE TABLE STATS (
INSTANCESMALLINT,
SIZEBIGINT DEFAULT 0);
This did not work as deadlock exceptions kept occurring in the triggers.
Someone suggested using a view instead to get the sum of all ITEMSIZE records.
CREATE OR ALTER VIEW SIZEVIEW(ITEMSIZE) AS SELECT
CAST(COALESCE(SUM(inventory.itemsize), 0) AS BIGINT) FROM INVENTORY;
This did not work either. The deadlock errors still occur when using this view
as follows SELECT * FROM INVENTORY. Lastly I tried different isolation
levels IsolationLevel.ReadCommitted, ReadUncommitted, IsolationLevel.Snapshot
but still not joy.
this is the calling code
public T ExecuteScalarQueryAsT(string Query)
{
try
{
FbTransaction Transaction =
DBConnection.BeginTransaction(IsolationLevel.Snapshot);
using (var FBC = new FbCommand(Query, DBConnection,
Transaction))
{
object Value = FBC.ExecuteScalar();
Transaction.Commit();
if (Value != null)
{
return (T)Value;
}
return default(T);
}
}
catch (Exception e)
{
Log.FatalException(Database Execute Scalar Query Exception,
e);
throw;
}
}
Have also tried IsolationLevel.ReadCommitted, ReadUncommitted
How can I read the sum of itemsize while items are being removed and added
concurrently? It is not critical that this value is 100% correct but I do need
to be able to read it reliably.
Thank you..
[firebird-support] Invalid Token when trying to insert XML into a BLOB
Hello to everyone,
First off:
1. Used Firebird Version Model:
Firebird-2.5.1 - Embedded (x86)
2. Operating System
Windows 7 Enterprise x64
Windows 7 Professional x64
Windows 7 Enterprise x86
Windows 7 Professional x86
3. Language
C# + .NET-Provider FirebirdSql.Data.FirebirdClient (Version 2.6.0, I couldn't
get 2.6.5 to work)
My Problem:
I am building a database which heavily relies on XML-Data. The problem is that
I cannot get any XML-Data into the database, the db refuses to accept the data
and always throws Token Unknown in line 3 Column 122 s - which is strange,
because even if I actually change the statement for insertion or do an update
on the xml-field, I always get line 3 Column 122 s, it refuses to accept the
XML as a value. When I use any other string (for example
TESTTESTTESTTESTTEST) it works however.
Here is my insert statement:
FbCommand DBCommand = new FbCommand(INSERT INTO games (name_of_game, info_xml)
VALUES (' + GameToInsert.Name + ', ' + XMLString + '), DBConnector,
DBTransaction);
DBCommand.ExecuteNonQuery();
DBTransaction.Commit();
Now - if I do an insert on the name_of_product-field, it works fine. The
info_xml however refuses everything. The field is a BLOB SUB_TYPE TEXT-type
field. I cannot seem to get a grasp why this happens - can someone help me out
here? How do I insert the XML into the db?
Thanks in advance for your replies,
Thomas Bueter
P.S.:
An example for the full insert statement (I also tried with removing the , no
luck either):
INSERT INTO games (name_of_game, info_xml) VALUES ('Rage',
'Gamegamesdbid895/gamesdbidGameTitleRage/GameTitlePlatformPC/PlatformReleaseDate10/07/2011/ReleaseDateOverviewRage
is a groundbreaking first-person shooter set in the not-too-distant future
after an asterogamesdbid impacts Earth, leaving a ravaged world behind. You
emerge into this vast wasteland to discover humanity working to rebuild itself
against such forces as ragamesdbider gangs, mutants, and the Authority -- an
oppressive government regime that has a special interest in you in particular.
Featuring intense first-person action, vehicle combat, an expansive world and
jaw-dropping graphics powered by gamesdbid's revolutionary gamesdbidTech 5
technology, Rage continues the legacy of design studio Id Software in
delivering an experience like no other./OverviewESRBM -
Mature/ESRBGenresgenreShooter/genre/GenresPlayers1/PlayersCo-opNo/Co-opYoutubehttp://www.youtube.com/watch?v=OVX9V_Uf30Q?hd=1/YoutubePublisherBethesda
Softworks/PublisherDeveloperID
Software/DeveloperRating10/RatingImagesfanartoriginal
wgamesdbidth=1920
height=1080http://thegamesdb.net/banners/fanart/original/895-1.jpg/originalvignette
wgamesdbidth=1920
height=1080fanart/vignette/895-1.jpg/vignettethumbhttp://thegamesdb.net/banners/fanart/thumb/895-1.jpg/thumb/fanartfanartoriginal
wgamesdbidth=1920
height=1080http://thegamesdb.net/banners/fanart/original/895-2.jpg/originalvignette
wgamesdbidth=1920
height=1080fanart/vignette/895-2.jpg/vignettethumbhttp://thegamesdb.net/banners/fanart/thumb/895-2.jpg/thumb/fanartfanartoriginal
wgamesdbidth=1920
height=1080http://thegamesdb.net/banners/fanart/original/895-3.jpg/originalvignette
wgamesdbidth=1920
height=1080fanart/vignette/895-3.jpg/vignettethumbhttp://thegamesdb.net/banners/fanart/thumb/895-3.jpg/thumb/fanartfanartoriginal
wgamesdbidth=1920
height=1080http://thegamesdb.net/banners/fanart/original/895-4.jpg/originalvignette
wgamesdbidth=1920
height=1080fanart/vignette/895-4.jpg/vignettethumbhttp://thegamesdb.net/banners/fanart/thumb/895-4.jpg/thumb/fanartboxart
sgamesdbide=front wgamesdbidth=1280
height=1803http://thegamesdb.net/banners/boxart/original/front/895-1.jpg/boxart/Images/Game')
Re: [firebird-support] Re: How to insert only if a matching row does not exist?
I do not believe that that way be able to
You should make a stored procedure with:
SELECT 1 FROM T1 WHERE name = :imput_parameter
if (row_count = 0) then
INSERT INTO T1 (type, name, sysid, flag) values (1, :imput_parameter, 1, 0);
Best Regards
=
|| ISMAEL ||
=
- Original Message -
From: gastrocus
To: firebird-support@yahoogroups.com
Sent: Thursday, October 20, 2011 12:28 PM
Subject: [firebird-support] Re: How to insert only if a matching row does not
exist?
--- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote:
What is the analogous way to achieve this in Firebird (2.5) ?
INSERT INTO emp (fruits) values ('mango')
where not exists (select 1 from emp where fruits = 'mango')
Thanks for the quick reply.
Strange... when I try to do this (via flamerobin or ibexpert) it does not
recognize the Where token (Dynamic SQL error -104, Token unknown, where ).
Must be something simple :(
Here is what I am trying:
I have a table like the following:
CREATE TABLE T1 (
ID INTEGER NOT NULL,
TYPE INTEGER,
NAME VARCHAR(128),
SYSID INTEGER,
FLAG INTEGER
);
ID has a generator so I don't want to insert a value for it.
Here is the query I tried
INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0)
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z')
It
Look up INSERT OR UPDATE and also MERGE in the Language Reference Update.
One or both might do what you want, more efficiently than NOT EXISTS. Not
enough info here to guess what you're really going to do with it.
My application is really to write a script that will be run once on a
smallish dataset (on the order of 1000 rows) so performance isn't really a big
deal.
Thanks for the pointer. I did look at INSERT OR UPDATE prior to posting but I
do not want to UPDATE if there is a match so I didn't think this applied.
[Non-text portions of this message have been removed]
Re: [firebird-support] Invalid Token when trying to insert XML into a BLOB
On Thu, 20 Oct 2011 11:32:13 -, dasdiohsauiguihsfda
thomas.bue...@googlemail.com wrote:
3. Language
C# + .NET-Provider FirebirdSql.Data.FirebirdClient (Version 2.6.0, I
couldn't get 2.6.5 to work)
My Problem:
I am building a database which heavily relies on XML-Data. The problem
is
that I cannot get any XML-Data into the database, the db refuses to
accept
the data and always throws Token Unknown in line 3 Column 122 s -
which
is strange, because even if I actually change the statement for
insertion
or do an update on the xml-field, I always get line 3 Column 122 s, it
refuses to accept the XML as a value. When I use any other string (for
example TESTTESTTESTTESTTEST) it works however.
Here is my insert statement:
FbCommand DBCommand = new FbCommand(INSERT INTO games (name_of_game,
info_xml) VALUES (' + GameToInsert.Name + ', ' + XMLString + '),
DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBTransaction.Commit();
Now - if I do an insert on the name_of_product-field, it works fine.
The
info_xml however refuses everything. The field is a BLOB SUB_TYPE
TEXT-type field. I cannot seem to get a grasp why this happens - can
someone help me out here? How do I insert the XML into the db?
Thanks in advance for your replies,
Thomas Bueter
snipped load of XML
and jaw-dropping graphics powered by gamesdbid's revolutionary
snipped even more XML
Here is your problem. You are concatenating the query string instead of
using prepared statements (parameterized queries). The text in your XML
contains an unescaped apostrophe, so that closes the SQL string after
'gamesdbid'. Next the parser encounters the s, which is out of place and
results in the error.
Please change your code to use prepared statements and never ever
concatenate external strings into a query; by using prepared statements you
immediately remove problems like vulnerability to SQL injection.
Mark
BTW: For more specifics on the Firebird .net provider, join the .net
provider mailinglist.
Re: [firebird-support] Firebird Embedded Deadlock problems.
On Thu, 20 Oct 2011 11:04:33 -, canacourse canacou...@gmail.com wrote: I'm using a firebird embedded 2.5.0.26074 database via latest ado provider which contains a simple table CREATE TABLE INVENTORY ( ID ID NOT NULL /* ID = VARCHAR(36) NOT NULL */, EXPIRYTIME EXPIRYTIME NOT NULL /* EXPIRYTIME = BIGINT NOT NULL */, ITEMSIZEITEMSIZE /* ITEMSIZE = BIGINT NOT NULL */, ACCESSCOUNT ACCESSCOUNT DEFAULT 1 NOT NULL /* ACCESSCOUNT = INTEGER DEFAULT 1 NOT NULL */, LASTACCESSTIME LASTACCESSTIME /* LASTACCESSTIME = TIMESTAMP NOT NULL */ ); To tally the sum of all ITEMSIZE rows two triggers where created on the INVENTORY table to add or subtract the value in ITEMSIZE to a TOTALSIZE Column (Single row) in table STATS as a row was added or removed. CREATE TABLE STATS ( INSTANCESMALLINT, SIZEBIGINT DEFAULT 0); This did not work as deadlock exceptions kept occurring in the triggers. Someone suggested using a view instead to get the sum of all ITEMSIZE records. CREATE OR ALTER VIEW SIZEVIEW(ITEMSIZE) AS SELECT CAST(COALESCE(SUM(inventory.itemsize), 0) AS BIGINT) FROM INVENTORY; This did not work either. The deadlock errors still occur when using this view as follows SELECT * FROM INVENTORY. Lastly I tried different isolation levels IsolationLevel.ReadCommitted, ReadUncommitted, IsolationLevel.Snapshot but still not joy. this is the calling code ... Have also tried IsolationLevel.ReadCommitted, ReadUncommitted How can I read the sum of itemsize while items are being removed and added concurrently? It is not critical that this value is 100% correct but I do need to be able to read it reliably. These problems are usually easier solved by using a record per change with a +1 or -1, and occassionaly (daily, weekly) processing the entire table to sum everything up and have only one record again. Next change will then again a +1 or -1 record and you query the sum total. So you would have something like: ITEM COUNT item1 10 item2 10 item1 1 item2 -1 item2 -1 item1 -1 and after the scheduled merge you get: ITEM COUNT item1 10 item2 8 You can then add a view which simply sums the records per item. PS I also added this to your Stack Exchange post
[firebird-support] Blobs not working correctly with Firebird 2.5 and ODBC
Hi, We have faced a very strange problem. We are running a firebird 2.5 database with the following table structure. CREATE TABLE TEST ( IDINTEGER NOT NULL, MEMO BLOB SUB_TYPE 1 SEGMENT SIZE 80, DATO TIMESTAMP ); ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID); We are using Firebird 2.5.1 + ODBC 2.0.0.151 on Windows 7. We use Delphi XE, with TADOConnection + TADODataSet + TDataSetProvider + TClientDataSet to connect to the database. The problem is when we insert (or update) a new record into the ClientDataSet and updating the field MEMO. (TMemoField) (This has been working perfekt in FB 1.5 and with all versions of MS SQL server). Now with a firebird 2.5 database we get the error message Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. when running from the Delphi IDE, but no error if we run outside. The big problem is that the time part of the field DATO is gone when we update the field MEMO. If we don't include the MEMO field, everything is OK. I have made a small demo project illustrating the problem. Test.zip (1090 KB): http://www.consultas.no/u/3037f738 All tips and hints will be highly appreciated! Best regards, Bjørn Larsen
[firebird-support] Re: How to insert only if a matching row does not exist?
At 05:28 AM 21/10/2011, you wrote:
--- In firebird-support@yahoogroups.com, Helen Borrie helebor@... wrote:
What is the analogous way to achieve this in Firebird (2.5) ?
INSERT INTO emp (fruits) values ('mango')
where not exists (select 1 from emp where fruits = 'mango')
Strange... when I try to do this (via flamerobin or ibexpert) it does not
recognize the Where token (Dynamic SQL error -104, Token unknown, where ).
Must be something simple :(
Not strange - I put you wrong there. Sorry! Of course, INSERT won't accept a
WHERE clause.
Here is what I am trying:
I have a table like the following:
CREATE TABLE T1 (
ID INTEGER NOT NULL,
TYPE INTEGER,
NAME VARCHAR(128),
SYSIDINTEGER,
FLAG INTEGER
);
ID has a generator so I don't want to insert a value for it.
Here is the query I tried
INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0)
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z')
My application is really to write a script that will be run once on a
smallish dataset (on the order of 1000 rows) so performance isn't really a
big deal.
Thanks for the pointer. I did look at INSERT OR UPDATE
UPDATE OR INSERT - I get that wrong 50% of the time. ;-)
prior to posting but I do not want to UPDATE if there is a match so I didn't
think this applied.
Would you like to try this:
merge into t1 tab2
using t1 tab1
on tab1.name = tab2.name and tab1.name = 'Z'
when not matched then
insert (type, name, sysid, flag) values (1, 'Z', 1, 0)
./heLen
[firebird-support] Re: How to insert only if a matching row does not exist?
--- In firebird-support@yahoogroups.com, Helen helebor@... wrote: Would you like to try this: merge into t1 tab2 using t1 tab1 on tab1.name = tab2.name and tab1.name = 'Z' when not matched then insert (type, name, sysid, flag) values (1, 'Z', 1, 0) That works like a charm! Thanks Helen.
Re: [firebird-support] Re: How to insert only if a matching row does not exist?
Hi Ed,
merge
into emp
using (select 'mango' fruits from rdb$database) src
on emp.fruits = src.fruits
when not matched then insert (fruits) values ('mango')
Nice trick! That seems to work.
Just realized you can make it even easier:
merge
into emp
using rdb$database
on emp.fruits = 'mango'
when not matched then insert (fruits) values ('mango')
Cheers,
Paul
Re: [firebird-support] Great variance in speed of an INSERT INTO ... SELECT
OK, then we'll need to look for less common problems. When the query is slow, are you seeing lots of page faults? Process size growth? What is the rate of reads/writes/marks/fetches? Do you have any guess as to where the time is going? Hi Ann, I am afraid most of the values you are asking me about are outside of my ability to gather, at least until I learn where to find them. Well, I do see a strange behavior. When the process started, Firebird fully utilized the CPU core it was running on, with memory requirements being very moderate (the highest I have seen is 70 MB). A few minutes into the operation, the Firebird's CPU load dropped to some 3-5%, with memory load at less than 20 MB. It doesn't seem to change anymore. Process Explorer reveals: - The most active thread seems to be a thread in state Wait:WrResource - I/O bytes fluctuate a lot between 100 and 800 KB - I do not see any open handles to temporary files, only the databases, the lock file, the message file and expected system libraries and devices It seems very much as if Firebird thinks someone is holding a required resource and is waiting until that resource is freed, but I have no idea which resource it could be. Well, well, well... Just as I was finishing the previous sentence, Firebird suddenly once again utilized the CPU completely and a few seconds after that finished the job. Pepak ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
