Réf.: FW: 126374Re: [firebird-support] Diagnose SQL error code -303
try to place chars et to NONE... You must have a letter That is not supported by your charset in one records... -- Le lun. 16 mars 2015 22:08 HNEC, 'Chris LeFebvre' lefebv...@comcast.net [firebird-support] a écrit : > >Thanks for the reply, I used a trial of Database Workbench 5 to create the >new database rather than the paid for version 3.4.4 I usually use (just >testing to see if I want to upgrade) and I checked all my existing databases >and this new database was created to use UTF8 (by default I guess and I >didn't change it) while all my other databases seem to use a characterset of >NONE. I'm still not entirely sure what exactly the problem was because I >tried deleting a few records and I still couldn't add a record or edit the >artist and title fields of any existing records. So I created a new database >with Database Workbench 3.4.4 and created the tables, generators and >procedures using the DDL statements from the old database (editing out >references to UTF8) and then used the datapump to copy data from the >existing database to the new one. After renaming both databases everything >appears to be working properly again. > >I looked up UTF8 and the Wikipedia article says "UTF8 is a character >encoding capable of encoding all possible characters (called code points) in >Unicode" so if that's the case then I still don't understand exactly what >the problem was with the old database; why I was able to enter 104 records >and then suddenly start getting a -303 error when adding a record or editing >the Artist or Title fields of an existing record? Also what's the real >difference between having a database with a default character set of UTF8 >and NONE? I'm asking for future reference and because I'm pretty sure that I >didn't fix the problem but instead just developed a workaround and I'd >really like to know? > >>Michel Le Clezio >> >>Take a look to charset used in text and the Charset of database... May be >in one record you use is an unsupported char... by the Charset of >database... this can happen if you paste >> a value... ( a value not build by your keyboard) >> >> >>Le Dimanche 15 mars 2015 5h28, "'Chris LeFebvre' lefebvrec@... >[firebird-support]" a écrit : >
[firebird-support] Index Memory Usage
Hi All, can anyone tell me (or point me in the direction of manuals) how i can calculate the amount of memory used by the indexes of a particular table? I have a table in which I store sha1 hashes (currently about 2 million records) as char(40) and its indexed, I'm wondering if the table would consume less memory if the hashes are stored as BIGINT Thank you
FW: 126374Re: [firebird-support] Diagnose SQL error code -303
Thanks for the reply, I used a trial of Database Workbench 5 to create the new database rather than the paid for version 3.4.4 I usually use (just testing to see if I want to upgrade) and I checked all my existing databases and this new database was created to use UTF8 (by default I guess and I didn't change it) while all my other databases seem to use a characterset of NONE. I'm still not entirely sure what exactly the problem was because I tried deleting a few records and I still couldn't add a record or edit the artist and title fields of any existing records. So I created a new database with Database Workbench 3.4.4 and created the tables, generators and procedures using the DDL statements from the old database (editing out references to UTF8) and then used the datapump to copy data from the existing database to the new one. After renaming both databases everything appears to be working properly again. I looked up UTF8 and the Wikipedia article says "UTF8 is a character encoding capable of encoding all possible characters (called code points) in Unicode" so if that's the case then I still don't understand exactly what the problem was with the old database; why I was able to enter 104 records and then suddenly start getting a -303 error when adding a record or editing the Artist or Title fields of an existing record? Also what's the real difference between having a database with a default character set of UTF8 and NONE? I'm asking for future reference and because I'm pretty sure that I didn't fix the problem but instead just developed a workaround and I'd really like to know? >Michel Le Clezio > >Take a look to charset used in text and the Charset of database... May be in one record you use is an unsupported char... by the Charset of database... this can happen if you paste > a value... ( a value not build by your keyboard) > > >Le Dimanche 15 mars 2015 5h28, "'Chris LeFebvre' lefebvrec@... [firebird-support]" a écrit :
[firebird-support] Transactions : about OIT, OAT, OST, NEXT
Hello,
i have remarked a strange thing, if you do only one transaction by connection
these counters are not updated , it is normal ?
i have done the test with dotnet provider and node-firebird (they uses the xdr
remote protocol).
it is very easy to reproduce :
1) Open Connection
2) Open transaction
3) do a select query
4) hard commit on transact
5) Close connection
ps : if i do a second transaction on the same connection these counters are
updated.
so if you do a little script that launch N times this little program you
obtain with gstat -h
IF N = 100
BEFORE :
OIT 1, OAT 2, OST 2, Next 3
AFTER
OIT 1, OAT 2, OST 2, NEXT 103
[CODE => DOTNET]
using FirebirdSql.Data.FirebirdClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace dotnet_firebird
{
class Program
{
static void Main(string[] args)
{
FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder scnx =
new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder();
scnx.UserID = "SYSDBA";
scnx.Password = "masterkey";
scnx.DataSource = "127.0.0.1";
scnx.Database = "c:\\test.fdb";
int i = 0;
FbConnection cnx = new FbConnection(scnx.ToString());
cnx.Open();
FbTransaction tr = cnx.BeginTransaction();
FbCommand cmd = new FbCommand("SELECT COUNT(1) FROM TRANSACT",
cnx, tr);
FbDataReader _reader = cmd.ExecuteReader();
while (_reader.Read())
Console.WriteLine(_reader.GetString(0));
_reader.Close();
tr.Commit();
i++;
cnx.Close();
cnx.Dispose();
}
}
}
[/CODE]
[CODE => node]
var fb = require('node-firebird');
var _connection = {
user : 'SYSDBA',
password : 'masterkey',
host : '127.0.0.1',
port : 3050,
database : 'test.fdb',
}
fb.attach(_connection, function (err, cnx) {
if (err) {
console.log("can't connect to db");
return;
}
cnx.query("SELECT count(1) FROM TRANSACT", function (err, data) {
console.log(data);
cnx.detach(function (err) {
if (err)
console.log(err);
});
});
});
[/CODE]
[firebird-support] What is difference between Distinct and Distinct(FieldName)
Hi, is there some difference in meaning between this two SELECT DISTINCT R.RDB$FIELD_ID, R.RDB$RELATION_NAME FROM RDB$RELATIONS R and this SELECT DISTINCT(R.RDB$FIELD_ID), R.RDB$RELATION_NAME FROM RDB$RELATIONS R regards, Karol Bieniaszewski
RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?
>HI Louis, > >It's pretty cool, but i need only in SQL, that too same number for all records >for same employee and next incremental number for all records of the another >employee. > >SET any help offered ? The first thing that comes to mind, is EXECUTE BLOCK (which is basically a STORED PROCEDURE built dynamically and not stored anywhere and that can be used anywhere a SELECT can be used, at least for recent Fb versions) combined with a temporary table, i.e. EXECUTE BLOCK RETURNS () AS DECLARE VARIABLE RowNo INTEGER; DECLARE VARIABLE EmpCode ; BEGIN RowNo = 1; FOR SELECT DISTINCT EmpCode FROM INTO :EmpCode DO BEGIN INSERT INTO MyTempTable(RowNo, EmpCode) VALUES (:RowNo, :EmpCode); RowNo = RowNo + 1; END FOR SELECT FROM MyTempTable MTT JOIN WHERE INTO DO SUSPEND; END It might also be possible to actually create a stored procedure similar to: CREATE PROCEDURE (MyID RETURNS (RowNo Integer) AS BEGIN RowNo = SELECT RowNo FROM MyTempTable WHERE MyID = :MyID; IF (RowNo IS NULL) THEN BEGIN RowNo = SELECT GetContext(SetContext(GetContext) --Don't have time now to find out exactly how INSERT INTO MyTempTable(RowNo, MyID) VALUES(:RowNo, :MyID); END END Then you'd only use a LEFT JOIN to this stored procedure in your queries (this could be reusable for different queries, not tied to your particular query). HTH, Set
