Just in case I ever run into this again: [WayBack] Lock conflict on no wait transaction
–jeroen
Archive for the ‘Database Development’ Category
Lock conflict on no wait transaction
Posted by jpluimers on 2019/01/04
Posted in Database Development, Development, Firebird, Power User | Leave a Comment »
Can’t format message nn:mmm — message text not found
Posted by jpluimers on 2018/12/20
Lovely: people that perform incomplete Firebird deployments…
This means the firebird.msg file on the client is either not found or is a different version than the server.
Source: [WayBack] Can’t format message nn:mmm — message text not found
–jeroen
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
Date and time conversion is hard in databases: `conversion error from string “30-12-1899″`
Posted by jpluimers on 2018/11/21
Converting string literals to to date/time/timestamp related data is always tricky in many Database environments.
Firebird is no exception, especially because sometimes it truncates a zero time portion from a date-time/timestamp.
So you can get this:
select cast('30-12-1899' as TimeStamp)
from rdb$databaseThrowing an error:
conversion error from string "30-12-1899"
And this:
select cast('30.12.1899' as TimeStamp)
from rdb$databaseReturning
CAST
30-12-1899 0:00:00
–jeroen
Share this:
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Jonathan Nelson on Twitter: “toilet paper visualization of the difference between zero and null https://t.co/ku3BBRjjUY”
Posted by jpluimers on 2018/10/24
toilet paper visualization of the difference between zero and null
The thread has a few others as well:
- NaN
- Public versus private
Sources:
- [WayBack] Jonathan Nelson on Twitter: “toilet paper visualization of the difference between zero and null https://t.co/ku3BBRjjUY”
- [WayBack] Angela Bassa on Twitter: “I couldn’t help myself (h/t @iamelgringo) https://t.co/k87pq1h9mb”
- [WayBack] Mouse on Twitter: “@iamelgringo Courtesy of my coworker: NaN https://t.co/lRm4O3EzRh”
- [WayBack] Reinhard on Twitter: “@iamelgringo found a sample for private and public attributes. https://t.co/qbvK8ScjvY”
- [WayBack] Maarten Lambrechts on Twitter: “@sickeroni @microbie @iamelgringo Ha! Or any of these https://t.co/VvZblMFVsl https://t.co/T0asFamspk”
Via: [WayBack] Difference between 0 and null – CodeProject – Google+
–jeroen
Share this:
Posted in Database Development, Development, Software Development | Leave a Comment »
How to read data from old delphi application Paradox databases without BDE?
Posted by jpluimers on 2018/08/30
Interesting question that raise some good tips: [WayBack] How to read data from old delphi application Paradox databases without BDE? I search for freeware or open source solution. – Jacek Laskowski – Google+
- [WayBack] PdxHak, PdxHakRecover and PdxHakViewer
- [WayBack] ODBC Paradox Setup Dialog Box
- [WayBack] Embarcadero Discussion Forums: Read DBase, Paradox, and Excel Files …
–jeroen
Share this:
Posted in Database Development, Delphi, Development, Paradox, Software Development | Leave a Comment »
Quickly generate queries for all non-system tables in your database in Firebird or InterBase
Posted by jpluimers on 2018/07/26
Change at will:
select 'select * from ' || r.rdb$relation_name as query
from rdb$relations r
where 1=1
and r.rdb$system_flag <> 1 -- no system relations
and r.rdb$view_source is null -- only tables
order by r.rdb$relation_name
For the EMPLOYEE demo database, this results in:
select * from COUNTRY
select * from CUSTOMER
select * from DEPARTMENT
select * from EMPLOYEE
select * from EMPLOYEE_PROJECT
select * from JOB
select * from PROJECT
select * from PROJ_DEPT_BUDGET
select * from SALARY_HISTORY
select * from SALES
–jeroen
Share this:
Posted in Database Development, Development, Firebird, InterBase, Software Development | Leave a Comment »
Getting the Firebird server version from a client using SQL
Posted by jpluimers on 2018/07/25
Since Firebird 2.1, you can get the first 3 numbers of the version with this SQL statement:
SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;
You get it in this format:
RDB$GET_CONTEXT --------------- 2.5.7
Which means you will not get the build number, which for most purposes is still quite OK.
–jeroen
via: [WayBack] How to detect the server version?
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements
Posted by jpluimers on 2018/07/19
The testing was done with Firebird 2.5.x x86 on Windows 7 x64.
Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBack] PkLab – Firebird: Tree data mangement with recursive CTE).
However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:
with
recursive
sequence(n) as (
-- When you select more than 1024 values, this error occurs:
-- Error while fetching data: Too many concurrent executions of the same request
select 0 -- start
from rdb$database
union all
select sequence.n + 1
from sequence
where sequence.n < 1023 -- finish
)
select sequence.n
from sequence
--where sequence.n in (24, 38)
order by sequence.n
It however is a pretty quick and CU bound solution: on my system ([WayBack] AMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.
In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?
Share this:
Posted in Database Development, Development, Firebird, IKEA hacks, OracleDB, SQL, SQL Server | Leave a Comment »
Firebird: the way you compare dates highly influences fetch times
Posted by jpluimers on 2018/07/18
I was amazed the influence on how you compare dates would be a factor 1000 change in fetch times even though I wrote about date, time and timestamp conversions before in Source: Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes.
All queries were ran at 20170410.
Slow
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- slow: prepared 0.066 sec; fetched 10.549 sec 250 rows
and date '1899-12-30' + data.datadate >= '2017-04-08' -- last 2 days
and date '1899-12-30' + data.datadate <= '2017-04-10' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.063 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date 'Now' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date 'Now' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.070 sec; fetched 0.011 sec 250 rows
and data.DATADATE >= date '2017-04-10' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date '2017-04-10' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.073 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date '2016-04-08' - date '1899-12-30' -- specific date range start
and data.DATADATE <= date '2016-04-10' - date '1899-12-30' -- specific date range end
The table looks like this:
CREATE TABLE "DATA" ( ID INTEGER NOT NULL, DATADATE DOUBLE PRECISION, DATATIME DOUBLE PRECISION ); CREATE ASC INDEX DATA_DATADATE ON "DATA" (DATADATE);
–jeroen
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
“The login already has an account under a different user name.” – MSSQL
Posted by jpluimers on 2018/07/11
You get “The login already has an account under a different user name.” when you try to create a user that already exists.
In this case it was a statement somewhere in a database creation script having a statement like this:
exec sp_addlogin N'<<UserName>>', N'<<Password>>', @logonDatabase, @logonLanguage
What happened was that the <<UserName>> in the script already existed and was used to create the new user using sp_addlogin.
I know that this is deprecated, but the search above also shows it happen with other ways of adding users.
It had been a while since doing SQL Server, so this was a good time for me to find back the relation between [WayBack] SQL Server Logins and Users
These queries will help you identify which ones are currently in your database:
use CRM124TestJWP
go
select sl.name as LoginName
, su.name as UserName
, sl.dbname as DatabaseName
from sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
select sp.name as LoginName
, sp.type_desc as LoginType
, dp.name as UserName
, dp.type_desc as UserType
, sp.default_database_name as DefaultDatabase
, dp.default_schema_name as DefaultSchema
from sys.server_principals sp
join sys.database_principals dp on dp.sid = sp.sid
Via: [WayBack] sql server – The login already has an account under a different user name – Database Administrators Stack Exchange
Share this:
Posted in Database Development, Development, SQL Server | Leave a Comment »
The Wiert Corner - irregular stream of stuff 