Is anyone using Doc's MySql protocol (v4.1 1.3.0) with MySql 5.5.24-cll, protocol v10?
I have a client migrating to a new host. We've been using 1.2.1 against mysql 5.0.95, but 5.5 triggers the client auth error. Moving to Doc's 4.1 version solved that, but a simple test query returns an empty block.
Can't rule out a data migration error yet, as things just moved and getting connected was the frist step. Just curious if someone is known to have this combo working.
Nevermind. I was able to test another query which works, so all is well there. Thanks Doc, once again!
The failing query was a data migration issue, just FYI.
Janko
I just upgraded local ubutu and I made the rebol work with ia32-libs again, now rebol mysql client is givin me auth error
Janko
Gregg: what did you say you did to make it work? what did you move to 4.1 the mysql server or something else?
DocKimbel
Janko: if you're using a newer MySQL version, you need my 4.1 driver.
Janko
aha, I hoped he meant that :) , but I only found 1.2.1 on your website
It's not an official release yet...I wish I had time to finish it...:-/
IIRC, it was missing some minor feature support from the protocol and good new test cases...
Janko
well if it works for what I need it it's ok with me (I just use mysql for user db so it's rather simple). I found mention of it here now http://rebolweek.blogspot.com/2008_12_01_archive.html is this the latest version you have?
Yes, the link from the blog entry is the right one too.
Janko
thanks! I will try it ... otherwise I will reinstall mysq 5.1
DocKimbel
Ah right (reading the blog article reminded me a few things), the multiple results reading + stored proc needed more tests.
james_nak
I've been relying on 4.1 for a long time now. No problems so far. Thanks Doc.
DocKimbel
You're welcome.
Gabriele
I've also been using it for a very long time. Sadly, not a lot testing with multiple result sets as we don't use this on production, so i can't use multiple result sets. :( it did work correctly with simple tests.
Chris
Thought I'd share this in case anyone found it useful (or had any suggestions/criticisms). First part is a description in REBOL of a database schema (sample here: http://reb4.me/r/schema-sample ), second part is a function that generates the MySQL code to create the database/tables. I'd like to add functions to analyse database schemas against my REBOL schema and create 'ALTER' statements as necessary. All in good time...
Definitely a work in progress, but has saved me some time.
Kaj
Did you port WITH from Red/System? ;-)
Chris
No, just an old QM function : )
Kaj
Ah, I remember now
Chris
Erk, fixed a couple other dependencies, d'oh!
Endo
Very nice work Chris. It would be nice to have that for MS-SQL too.
Chris
By the looks of it (I've not used MS SQL) it wouldn't be too different. Same for Sqlite. Just a matter of hacking http://reb4.me/r/mysql-schema to include the most appropriate datatypes.
Of course, that's only for the 'create statement. I imagine detecting differences in schemas between SQL dialects might be more nuanced. But then I haven't got that far with any dialect of SQL, so...
afsanehsamim
guys , i have one project (crossword )which uses mysql database! i want use mysql driver ,i know how we can create table and make connectivity with database ! crossword should show on web page ,and when user put characters in each field ,then press button ,it will chek with database and if it is correct ,user can continue otherwise shows error message ....i created crossword table with cgi and is working on web ,i have created database as well,now my problem is i do not know how can i fetch data and comparing with database? is there anybody for helping my mini project????
i need only simple example of that ...plz help me if you can ... i do not have time :( ...i am very begginer in rebol
Arnold
Show what you have got so far, may be you are close.
afsanehsamim
my understanding of work steps is : i have two files *.cgi and *.r ,in cgi file i created crossword that shows on web page ... in data.r i have made connectivity with database and create table ... and my codes are as following : in cgi file:
REBOL [] do %mysql-protocol.r db: open mysql://root@localhost/test insert db {create table data ( name varchar(100), address text )} insert db {INSERT into data VALUES ('raj', 'pune'), ('ekta', 'delhi'), ('ankur', 'mumbai')
} insert db "SELECT * from data" results: copy db probe results close db
the second file is only exaple of connectivity
table is differenet
Arnold
what did the probe results show?
afsanehsamim
in rebol interpreter only created table
i mean when i run data.r it creats table
i do not know how should i mix cgi file with database file
the results is like this:
do %data.r Script: "Untitled" (none) Script: "MySQL Protocol" (12-Jul-2008) MySQL protocol loaded connecting to: localhost [ ["raj" "pune"] ["ekta" "delhi"] ["ankur" "mumbai"] ]
Arnold
you have to mix them something like #! "C:/wamp/bin/apache/Apache2.2.11/cgi-bin/rebol-core-278-3-1.exe" -cs REBOL [Title: "Table"] do %mysql-protocol.r db: open mysql://root@localhost/test insert db {create table data ( name varchar(100), address text )} insert db {INSERT into data VALUES ('raj', 'pune'), ('ekta', 'delhi'), ('ankur', 'mumbai')
} insert db "SELECT * from data" results: copy db print "content-type: text/html^/" print [<HTML><BODY><TABLE bgcolor="black" border="1"> { <tr bgcolor="white"> <td style="width:30px; height:30px;"><input name="one-one" type="text" size="1">} print results/name print {</td>}
etc
afsanehsamim
and what extension should i use ? .r ???/
Arnold
learning comes with a lot of try and err even for a 'simple' language as REBOL
Probably yes and have your apache server set up right for .r files
afsanehsamim
you're right,i configured my apache server for cgi ... i think i should configure again
TomBon
just rename it to .cgi you have the correct shebang at the top. apache should recognize it automatically. don't forget to chmod your cgi to grant execution permission.
afsanehsamim
but my OS is windows 7
i am not using linux
BrianH
Then you don't have to chmod, you just have to put it in the right directory.
TomBon
correct
afsanehsamim
thank you Arnold.... i van fetch data from database :) ...i am working on compare two values (value that is entered by user and value that is correct )...
do you know for comparing which aommand should i use?
command for comparing values in mysql database ????
afsanehsamim
hey guys... do you know how we can check values of two tables in mysql?????!!!!
Arnold
select * from table where answer = useranswer select * form table1, table2 where table1.field = table2.field Do you have google? Any MySQL tutorial out there holds the answer to your question. Or want to compare from wihin your REBOL script? It is either [ left = right][do-something][do-else-thing].
afsanehsamim
i exactly meant that how we can use Mysql queries(checking two values in two different table) in Rebol script !!!!!
Arnold
so you have put the users answer also in a mySQL table? read both tablerows from the tables into different variables and compare the subfields of the returned rows result1/fielda result2/fielda
afsanehsamim
if user wants insert values to table and it should save in database ,what is the command for that ???
this command is correct ??? : insert db ["insert into data1 values (?)" myfields name]
afsanehsamim
guys ,i want insert data into database ,i wrote the following codes but no value save !!... :(
#! "C:/wamp/bin/apache/Apache2.2.11/cgi-bin/rebol-core-278-3-1.exe" -cs REBOL [Title: "Table"] do %mysql-protocol.r db: open mysql://root@localhost/test insert db { DROP TABLE IF EXISTS data1; create table data1 ( oneone varchar(1), onetwo varchar(1), onethree varchar(1), twoone varchar(1), twothree varchar(1), threeone varchar(1), threetwo varchar(1), threethree varchar(1) )}
you see? The use four ? ? ? ?, and after the string { }, there are four values fname lname title phone
afsanehsamim
i completely underestand !!!! but fname,lname,title,phone are string and before query ,they are defiend as : fname: "Johnny" lname: "Johnson" title: "President" phone: "(707) 555-1212"
i have <input ....>,how should i define it as string????
i should write like fname:<input name="oneone" type="text" size="1">????
Pekr
what do you want exactly to be put into the database field?
afsanehsamim
character
when i write like this :print [<form><input type="submit" value=" submit !" />] print [<TABLE bgcolor="black" border="1">] print {<tr bgcolor="white"> <td style="width:30px; height:30px;"><input name="oneone" type="text" size="1">} insert db ["insert into data1(oneone) values(c)"] results: copy db print {</td>}
it saves c to database
but it will not save user input
Pekr
yes, you have basically two options:
1) work with string - insert db {insert into data1 ....} - you simply construct the DB string directly
2) work with block - insert db ["initial string here, contatining values(?)" value] - which allows to work with rebol blocks and evaluate some things ...
so in your case it should be:
insert db ["insert into data1(oneone) values(?)" "c"]
or something like:
value: "c" insert db ["insert into data1(oneone) values(c)" value]
afsanehsamim
but value should not be as constant ! when user put character it should save in values ,now plz tell me how should i write ? i should write like : value:"c"
ishould write like : value :<input ...> ?
@Peker : i am using option one ,but i does not save any value ...
Pekr
you want to save value from the form into the db?
afsanehsamim
yes
afsanehsamim
i want to save value from the form into the database!
afsanehsamim
any solution?
Pekr
I think, that your script is wrongly constructed! What you seem to do is to just print a form, which then gets delivered to user's browser screen. But at that time, there is no value yet. You should study, how to do CGI in REBOL. You need a separate html form, then you need to read CGI values and store them in a DB ...
but in those links ,i can not find database example! i read that link before ,i created html form and cgi as well, it is working properly ... the point is when user enter input and click submit it goes to cgi page. i need save that input in database ...plz guid me or show me one example how can i save value from the form into db?
insert db ["insert into data1(oneone) values(?)" result/oneone]
afsanehsamim
how can i write <input ...> in block?
Pekr
uh, my question is, if you ever worked with CGI?
examples on the above page are pretty straightforward?
there are two things - 1) you have to write a form, basically a html file, with fields, etc., and submit button, linking it to your cgi script 2) you have to write a CGI script, being able to read submitted values ...
I might dig-up some simple example later ...
afsanehsamim
i am using the first one ... can i use codes which you mensioned in above ? values: decode-cgi read-cgi result: construct values
insert db ["insert into data1(oneone) values(?)" result/oneone]
afsanehsamim
i wrote whatever you said but i got this error :
>> do %compare.cgi Script: "Untitled" (none) Script: "MySQL Protocol" (12-Jul-2008) MySQL protocol loaded connecting to: localhost ** Script Error: Invalid path value: oneone ** Where: map-rebol-values ** Near: result/oneone >>
afsanehsamim
Any one knows how can we save value from the form into database?
Arnold
You first check that the value is acceptable for what can be expected. This is to prevent SQL injections and other malicious input from hackers/innocent users and monkeys using your application. Than you insert a SQL command to insert or update the mysql database just like you did when you did with your select statement before. mijnquery: "INSERT INTO cms_artikel (titel, tagregel, sectie, toegevoegd, artikel_tekst) VALUES ('" mijnquery: append mijnquery rejoin [titel "', '" tagregel "', '" desectie "', '" toegevoegd "', '" artikel-tekst "')"] insert db mijnquery The names after INTO are the fieldnames of the table cms_artikel the ones after VALUES are the REBOL variables that get replaced by their values
afsanehsamim
it means i should write like: query: "INSERT INTO data1 (oneone,onetwo,onethree) VALUES ('" query: append insert db rejoin [oneone "', '" onetwo "', '" onethree )"] insert db query
i am very thankful if you do based on my codes ...
i am too confiused ...
Arnold
Yes. Do a probe of the query to see if it generates the sql you expected.
I am not doing your code for you. If you want to find someone to do it for you post your job in the ~Opportunity section.
afsanehsamim
i did not say do my code Arnold !!! i wanted only help ... because till now whatever guys said here was not related to form ! my problem is only saving value from form in database ...
"Any one knows how can we save value from the form into database?" - sure, Pekr told you how to do it.
Your problem is that you do not do what Pekr told you to do. First, you need to create the form. Check: Do you really have the form? Second, you need to create a CGI script (this is not the form from the first point, the form from the first point is not a CGI script). Check: do you really have a CGI script ?
Pekr told you that the example you posted was neither the form, nor the CGI script.
One more note: your problem is much more elementary than handling the database. Before writing data to the database you need to have a script accepting (decoding) the data obtained from the form.
Pekr
Ladislav - thank you. It is apparent, that what afsanehsamim is missing is the basic knowledge of how webserver stuff works between the client and the server. Examples at rebol.com are pretty straightforward. The only chance is to really create a simple example for him ...
Pekr
Create 2 files. Call the first one e.g. cgi-test.html, and upload it to your server. The only thing you have to change is the link to your .cgi script in there:
Create a second file, called cgi-test.cgi (it has to align to how you name it in the above source file). Upload it to your cgi working directory. Remember to change the first line to contain the path, where your REBOL executable is placed:
but afsa, honestly - it does not even belong to the database group, but to Rebol School group - you seem to miss the basic understanding, of how CGI works on the server. Your problem is not in getting the value into DB, but handling CGI stuff in general. In above example, what you would put into your DB would be values/field ...
afsanehsamim
Thankyou so much ladislav and Pekr ... guys i underestand whatever you said ... Pekr : you meant i should first decode values after that should values save in database? i have two files and both work properly! one html and another one is cgi ! i did your codes as well ... now plz tell me what is the next step ? As i told you before i should save value in database ,it is one part of my project !!!! :( i did this link http://www.rebol.com/docs/cgi2.html#section-2 and i underestood ... http://www.rebol.com/docs/cgi2.html#section-2http://www.rebol.com/docs/cgi2.html#section-2
plz tell me decoding value is not related to saving data ?
then how can i save values ?
Endo
do you mean saving result to a file? it is just a block, you can simple SAVE %file.r RESULT
afsanehsamim
no ...i mean saving values into database .
Endo
use a normal INSERT query. insert db-port "INSERT INTO table (colA, colB) VALUES (1,2)" or insert db-port ["INSERT INTO table (colA, colB) VALUES (?,?)" 1 2]
afsanehsamim
Endo values should get from form ,it is a big problem till now that no one could underestand ...
@Pekr: could you tell me after decoding values what is the next step?
i decoded my values which i got from the form! my cgi and html are working ,plz tell me what should i do?
TomBon
afsa, did you succesfull echo back the decoded form values to the browser andreas told you before?
TomBon
if so, you have to add your mysql connection parameters to your script., open a mysql port and do an sql insert to your table.
afsanehsamim
yes TomBon ,i did it ... but there are no values in my database.
TomBon
can you post your insert command here?
afsanehsamim
insert db ["insert into data1(oneone,onetwo,onethree,twoone,twothree,threeone,threetwo,threethree) values(?,?,?,?,?,?,?,?)" ]
i know it dose not have any value
i do not know what should i write
BrianH
You are missing the actual values to insert. Put those in the block after the SQL string.
TomBon
yes, I see. parameterized inserts are ok but perhaps better make a rejoin.
Andreas
insert db ["insert into sql-tablename (sql-fieldname) values (?)" cgi-values/cgi-fieldname]
TomBon
afsa, the last one from andreas is fine.
BrianH
TomBon, don't encourage people to use rejoin for SQL queries. Definitely use parameterized queries. Building your own queries with rejoin is a sure recipe for SQL injection.
Andreas
i suggest to get the html+cgi echoing working first, then getting a minimal script that inserts a value into your database working, and then putting the two pieces together by extending your "echo" cgi to insert into the database
TomBon
brian, made this for year without any problems. also good for beginners.
checking for proper values and a corerct sql syntax should be always done even when parameterized.
BrianH
Nice to hear, TomBon. Nonetheless, such checking is exactly what parameterized queries do, and I often have to fix errors made by other developers who don't use them. Plus, parameterized queries are a lot quicker on most databases because the query plan gets cached.
It is always a bad idea to suggest to newbie programmers that they not use parameterized queries.
TomBon
well better first to make him clear whats going up, then make the final. I think he is confused by this examples. btw, how parameterized queries preventing sql injection if not serverside?
BrianH
Non-parameterized queries are an advanced topic for experienced developers, though also the subject of the worst coding horror stories :)
TomBon
well, well :)
but let's first try to help afsan, if his script is running he can improve it.
afsanehsamim
guys ...i am happy :) it is working... tnx a lot Andreas ...
thank you TomBon and BrianH
TomBon
nice, good luck with your crossword afsan...
BrianH
With parameterized queries (even in REBOL) the SQL and the parameters are sent separately and combined in the server. The query plan is generated only once per query, with the parameter placeholders being in the plan. Then the actual parameters are plugged into the plan. The next time the parameterized query is called (maybe with differe3nt parameter values) the same plan is used and the new parameter values are plugged in.
TomBon
isn't this execution optimation?. in this case a stored procedure will help also. how will this prevent from sql injection? compared to a concatenated server side sql string?
BrianH
If you build a query dynamically with rejoin or something, the query is put together client side and then the server has to generate a new query plan for each distinct set of parameter values. This takes time and blows the query plan cache, which slows down the whole query process.
The problem is that your ad-hoc parameter screening is usually not perfect. Parameterized queries don't build a query in the server, they just plug in the values to an already-compiled query (the "query plan"). The server doesn't have to do any parameter screening other than for malformed values in the protocol.
TomBon
depends on the needs. I always try to detach the data sink from input logic. this way you can change your db backend very easy but of course everybody has it's own style in this.
BrianH
For new developers ad-hoc parameter screening is even more likely to be bad (and most that don't use parameterized queries are still new, no matter how long they've been programming, because parameterized queries are almost always inherently better). Even if it wasn't a safety issue, they're a lot faster.
I've seen data front-ends that don't use parametrized queries when talking to SQL servers that support them. They need work.
afsanehsamim
could you tell me how can i compare values of two tables in database?
Endo
About parametrized queries: The only problem using them on R2, at least with RT's default ODBC, there is no chance to use NULL values. None of those work: insert db-port ["INSERT t (a) VALUES (?)" NULL] insert db-port ["INSERT t (a) VALUES (?)" 'NULL] insert db-port ["INSERT t (a) VALUES (?)" "NULL"] insert db-port ["INSERT t (a) VALUES (?)" none] insert db-port reduce ["INSERT t (a) VALUES (?)" none]
TomBon
you have more than one solution, the first is a simple serial SELECT on each table -> compare the output for equal. of course this produce unnecessary DB overhead but I guess you won't feel any speed difference except you are serving a whole city concurrently. another, better one is a JOIN or UNION.
SELECT table_name1.column_name(s), ... FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
the JOIN direction (LEFT,RIGHT,INNER) for your reference table is important here. the resultset is a table containing BOTH columns. if both having a value -> match, if one is empty then you don't.
index both fields to accelerate the query and use something like the free SQLyog to test different queries to make debugging easier for you.
while you situation reminds me to myself, sitting infront of a monochrom asthon tate dot some decades ago and asking what next?, you should 'bite' yourself now thru the rest. It won't help you on longterm if you don't.
afsanehsamim
@TomBon: my query for joining two tables is :insert db["select * from data LEFT JOIN data1 ON data.oneone=data1.oneone"] and output is :[ ["c" "a" "t" "a" "e" "r" "o" "a" none none none none none none none none] ] plz tell me what should i write in query that i get values instead of none in output ?
afsanehsamim
guys when i enter correct value in form the above join query works properly... i need help for writing queries which other condition,it means if user enter wrong value ,it joins with first table but dose comparing indicidually and shows error message.
the output of this query insert db[{select * from data LEFT JOIN data1 ON data.oneone=data1.oneone}] is : [ ["c" "a" "t" "a" "e" "r" "o" "a" "c" "a" "t" "a" "e" "r" "o" "a"] ]
i got this results:c c a none t t a none e none r none o none a none
now how can i write query for everyvalues which are same and print correct message on web page?
afsanehsamim
hey guys... i have just 2days time for my project ! could you help me?
i could not do the last step ... i should show result of comparing values on web page
TomBon
a quick update on elasticsearch. Currently I have reached 2TB datasize (~85M documents) on a single node. Queries now starting to slow down but the system is very stable even under heavy load. While queries in average took between 50-250ms against a dataset around 1TB the same queries are now in a range between 900-1500 ms. The average allocated java heap is around 9GB which is nearly 100% of the max heap size by a 15 shards and 0 replicas setting. elasticsearch looks like a very good candidate for handling big data with a need for 'near realtime' analysis. Classical RDBMS like mysql and postgresql where grilled at around 150-500GB. Another tested candidate was MongoDB which was great too but since it stores all metadata and fields uncompressed the waste of diskspace was ridiculous high. Furthermore query execution times differs unexpectable without any known reason by factor 3. Tokyo Cabinet started fine but around 1TB I have noticed file integrity problems which leads into endless restoring/repairing procedures. Adding sharding logic by coding an additional layer wasn't very motivating but could solve this issue. Within the next six months the datasize should reached the 100TB mark. Would be interesting to see how elasticsearch will scale and how many nodes are nessesary to handle this efficiently.
Maxim
when you talk about "documents" what type of documents are they?
Gregg
Thanks for the info Tomas.
TomBon
crawled html/mime embedded documents/images etc. as plain compressed source (avg. 25kb) and 14 searchable metafields (ngram) to train different NN types for pattern recognition.
Maxim
thanks :-)
MaxV
I have a problem with RebDB: how works db-select/group? Example: >> db-select/where/group/count [ID title post date] archive [find post "t" ] [ID] ** User Error: Invalid number of group by columns ** Near: to error! :value
Endo
Don't you need to use aggregate functions when you grouping?
* when you use grouping.
Scot
I use the sql dialect like this: sql [select count [ID title post date] from archive group by [ID title post] where [find post "t"]]
The trick with this particular query is the that the "count" selector must have exactly one more column than the "group by" selector. The first three elements [ID title post] are used to sort the output and the last element [date] is counted.
output will be organized: ID title post count
I would like to be able to include other columns in the output that are not part of the grouping or count, but I haven't figured out how to do this in RebDB. I have used a parse grammar on the output to achieve the desired result.
I would also like to query the results of a query, which I haven't figured out how to do so without creating and committing a new database. So I have used a parse grammar to merge two queries.
Pavel
SQLite version 4 announced/proposed. The default built-in storage engine is a log-structured merge database instead of B-tree in SQlite3. As far as I understand the docs This store could be usable standalone or use SQL frontend. Google to SQLite4.
Kaj
Cool
Endo
I cannot see any announcement on the sqlite.org web site? SQLite 3.7.17 is the latest and recommended version?
Kaj
I saw code last year, but it's probably still in deep development
Or differently, has anyone worked with excel files via ODBC, using either R2 or R3? I tried Graham's code, which works for .xls files, but not .xlsx files. When I convert my file to .xls, R2 returns - not enough memory :-(
p: open [ scheme: 'ODBC target: "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\path-to-file\file.xls" ] conn: first p insert conn "select * from [Sheet1$]" result: copy conn
As for R3 - maybe there was also some other R3 ODBC extension, somehow can't find it ....
What you found looks to be the latest version of that
I've also had loading problems with R3 extensions on Linux that worked before. Sometimes you seem to need an older R3, sometimes a newer
If all else fails, recompile the C code
Pekr
well, I have even old latest Carl's view.exe, does not work either ... lost battle here ... not fluent with recompile of ODBC DLL, does not imo guarantee, that loading it in R3 will actually work. I wonder if there was any change to import function or to extension mechanism itself ...
Kaj
Bug fixes, I think, but they also seem to cause compatibility regressions
DocKimbel
Do all your binding have Red-level interfaces now?
I guess some like SDL don't need that.
Kaj
Yes, it's in progress. Some like SQLite are one-to-one in Red like in Red/System. SDL is used more as a part in other low level bindings, such as OpenGL. OpenGL itself is waiting for floats in Red
Pekr
ok, so got valid ODBC connection string fro .xlsx files. R2 crashes when copying a data though ...
ok, got it kind of working with the increase of p/locals/rows to 10K lines ... the excel sheets are so complex, that it does not return half of the info, it most probably counts on more columnar/db kind of data ...
pity Saphirion's excel dialect is not available for download anymore. Will try with Anton's old COMlib code ...
DocKimbel
Sorry for the off-topic question, I though I was in another channel.
Pekr
You are used to Red channel being at the top, right? :-)
DocKimbel
Just forgot to check the channel name before posting. ;-)
Pavel
Very light mmap() based b+tree library LMDB a part of OpenLDAP is available separately at symas.com/mdb/. After some mungling I succeed with Mingw on windows, TCC needs a bit more but works too. Compiled dll is 90kB packed with upx touch directly 30kB what is a size Carl requested for DB backend for Rebol some time ago. test aplications runs under windows. Mingw is more compatible with unix like getopts() used in some tools included in package.
DocKimbel
The "packed with upx" part doesn't seem compatible to me with the way encapped mezz code is loaded by Rebol on booting.
Pekr
I would go with SQLite anyway. Version 4 brings some architectural changes IIRC ...
AdrianS
If you read the LMDB page, there is a SQLite implementation with LMDB replacing SQLite's own B-tree implementation. Performance looks significantly better.
SQLite 4 has a new interface for low level storage engines like these, and a default one that's also faster than SQLite 3
AdrianS
LMDB for SQLite 4 is in the works too, it seems. I guess it remains to be seen if it will improve upon the built in implementation.
Pavel
https://code.google.com/p/high-concurrency-btree/ a public domain implementation compiles to about 25 kB. No problem with TCC compiler. using mmap partially sensitive for configuration parameters (page size and amount of cached space). This program doesnt store a data only pointers to them ie creating only indexes.
Pavel
previosly mentioned LMDB was based on work of Martin Hedenfalk http://www.bzero.se/ldapd/ original implementation needs be touched to be complilable under Windows, but it is worth to look at it. Append only DB
Endo
Cannot connect to MySQL 5x with Rebol/Command's official MySQL driver? I get "Client does not support authentication protocol" even I use "update user set Password=OLD_PASSWORD('password') WHERE User='username'; " which should work. So the only way is to use http://softinnov.org/tmp/mysql-protocol-41.r ?
DocKimbel
IIRC, the statically linked libmysql in the SDK is a 3.x version. You can use that MySQL driver version or you can write a binding to a newer libmysql client library...as you wish. ;-)
GiuseppeC
Hi, is this the place to talk about R3 ODBC ?
Gregg
Seems as good as any to me.
sqlab
Is there a newer version than 1.2.1 of the mysql-driver of Softinnov available? I tried to connect to MariaDB and got an error message >> read mysql://localhost/mysql/test ** User Error: ERROR 1251 : Client does not support authentication protocol requested by server; consider upgrading MariaDB client ** Near: read mysql://localhost/mysql/test
the second one I really don't remember what it does.. :
- either find port/locals/capabilities 'protocol-41 [ - insert-query port data - ][ + ; either find port/locals/capabilities 'protocol-41 [ + ; insert-query port data + ; ][ insert-all-queries port data - ] + ; ]
Endo
Is there any way to insert a NULL using parametrized query?
insert mysql "create temporary table t (a int)" insert mysql "insert into t values (NULL)" ; this works insert mysql ["insert into t values (?)" none] ; this doesn't ** Script Error: ODBC error: [Microsoft][ODBC Driver Manager] Invalid string or buffer length
GiuseppeC
Hi Doc, is the mysql protocol R3 compatible ?
Endo
Protocol source is very clear, it looks like it might compatible with R3 or at least it can be with a little effort. I'll get his to-sql function from the protocol source and use it to generate SQL of my parametrized query.
DocKimbel
@Giuseppe I never tried, usually low-level port-related code is AFAIK, not compatible between Rebol2 and 3.
That said, I guess it could be ported without much efforts by someone knowledgeable in R3's port coding.
I need help with %mysql-protocol.r v1.3.0. I use it nicely with a mysql 5.5.49. But I tried to use it with a mysql 5.5.53 server (on a different machine), and I get an error at the opening of the port! :
DideC
REBOL/Core 2.7.8.4.10 (23-Jan-2016) Copyright 2016 REBOL Technologies REBOL is a Trademark of REBOL Technologies All rights reserved.
Finger protocol loaded Whois protocol loaded Daytime protocol loaded SMTP protocol loaded ESMTP protocol loaded POP protocol loaded IMAP protocol loaded HTTP protocol loaded FTP protocol loaded NNTP protocol loaded >> >> do %mysql-protocol.r Script: "MySQL Protocol" (3-Dec-2008) MySQL protocol loaded >> trace/net on >> open mysql://user:1Cuqb!40@localhost/adgo-cmde URL Parse: user 1Cuqb!40 localhost none none adgo-cmde Net-log: ["Opening" "tcp" "for" "MySQL"] connecting to: localhost Net-log: "low level read of 4 bytes" Net-log: "low level read of 66 bytes" ** Script Error: find expected series argument of type: series object port bitset ** Where: read-packet ** Near: parse/all next pl/buffer case [ find pl/capabilities 'protocol-41 [ [ read-int (pl/error-code: i... >>
Bo
Hmm, I use mysql-protocol a lot, and I've never seen that type of error before.
DocKimbel
Never seen that error before. Might be caused by a change in the protocol, so that pl/capabilities is not decoded properly anymore.
DideC
Same with a probe of the binary data readed form the mysql server at handshake :
Net-log: ["Opening" "tcp" "for" "MySQL"] connecting to: localhost Net-log: "low level read of 4 bytes" #{42000000} Net-log: "low level read of 66 bytes" #{ FF6A04486F737420273132372E302E302E3127206973206E6F7420616C6C6F77 656420746F20636F6E6E65637420746F2074686973204D7953514C2073657276 6572 } ** Script Error: find expected series argument of type: series object port bitset ** Where: read-packet ** Near: parse/all next pl/buffer case [ find pl/capabilities 'protocol-41 [ [ read-int (pl/error-code: i...
(note: first probe is the header, so the length of the packet, second probe is the data).
DocKimbel
>> probe as-string #{ { FF6A04486F737420273132372E302E302E3127206973206E6F7420616C6C6F77 { 656420746F20636F6E6E65637420746F2074686973204D7953514C2073657276 { 6572 { } {ÿj^DHost '127.0.0.1' is not allowed to connect to this MySQL server} == {ÿj^DHost '127.0.0.1' is not allowed to connect to this MySQL server}
;-)
DideC
Easier when you know you can do that :-) Thanks, need to investigate that now.
OK, I changed the 'locals-class object! prototype to have an empty block for 'capabilities, and know I get :
Net-log: ["Opening" "tcp" "for" "MySQL"] connecting to: localhost Net-log: "low level read of 4 bytes" Net-log: "low level read of 66 bytes" #{ FF6A04486F737420273132372E302E302E3127206973206E6F7420616C6C6F77 656420746F20636F6E6E65637420746F2074686973204D7953514C2073657276 6572 } ** User Error: ERROR 1130 : Host '127.0.0.1' is not allowed to connect to this MySQL server ** Near: p: open mysql://user:1Cuqb!40@localhost/adgo-cmde
DocKimbel
Looks better.
DideC
I don't know if it need to be corrected in your protocol implementation. Sure there is something that do not goes as expected if the 'capabilities word is not set to a block! at this time.
Now, any idea about the problem ? Because the "user" user is the one used by phpMyAdmin to manage the DB and it works form there !
form=from
DocKimbel
I haven't touch MySQL since 7 years. From what I remember, it has to do with the access permissions table (`user` and `host` tables?).
DideC
Seems to. Maybe related to this :
"Combine dlo's answer with Darren Chamberlain's. The reason for this is that the special meaning that 'localhost' has in MySQL is that it signifies to use the local unix socket (mysql.sock) vs the TCP socket. This is why specifying 127.0.0.1 as the host will get you in so that you can fix the situation; it signifies to the MySQL client to use the TCP socket."
Nop. Localhost or 127.0.0.1, same error result.
DocKimbel
There's a table for giving permissions to (user, IP) couples, I think it is `host` or `hosts`, you should check that.
DideC
Yes. it's related. I use PLESK to manage the server, so I changed the "user" config to allow acces from any host (was only from localhost). No I get :
** User Error: ERROR 1045 : Access denied for user ''@'127.0.0.1' (using password: NO) ** Near: p: open [scheme: 'mysql username: "user" password: "v6lvZ6&8" host: "127.0.0.1" port-id: 3306 target: "adgo-cmde"]
OUPS, my bad. Its "user:" not "username:".
DocKimbel
You should have an entry in that table for: "user" | "127.0.0.1".
DideC
Need more investigations. I have exactly the same entry on this mysql server than another. But the other is older v5.5.49.
Anyway thanks for your help. You put me on the railways. Need to see where it goes now.
And I did my first PR on Github !!
DocKimbel
I thought you did one on red/red already.
DideC
It was not merged AFAIR. Sorry for my to quickly typed PR. It was meant to be "copy []" of course. But "make block! 1" does the same.
DideC
Oh my ! 'copy does not work as it is redefine in the script. So "make block! 1" is the good solution. Like Franck Dubosc said "I'm not in my plate" ;-)
DideC
Back to my initial problem. It is that "localhost" and "127.0.0.1" are not the same for the mysql.user table.
And (dunno why), on this new server, when I try to connect to "localhost" with the user "user", it is converted to email@withheld4.me and this host "127.0.0.1" is not in the mysql.user host column. There is only "localhost" values. So the quick solution is to copy the "locahost" mysql.user entry with a host value of "127.0.0.1" and the connexion works.
But if I edit the user (there are managed by PLESK), all entries for it are removed and only the "localhost" one is put back. Do it again man.
So now the question : why is this "localhost" be converted to "127.0.0.1" in between rebol and mysql. On the pretty same server I had before it does not do that.
DocKimbel
If it's on Linux servers, try comparing the /etc/hosts files content.
Pekr
Using on1 Photo RAW beta right now, talked to devs, they claim to use LevekDB from Google, claiming it is faster than SQLite (though highly unreliable and not prone to failures). However, reading Wikipedia articles, I found out about Lightning DB - seems nice: https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Database
Gregg
I remember reading about it some time back. The licensing notes are interesting. Another example of people being surprised at how well simplicity works.
Gregg
It would be interesting to port to Red, maybe first as a wrapper, then a fresh implementation in Red/System. So many DB choices these days.
GiuseppeC
Is SQL Lite usable on REBOL3 ?
Endo
I think Kaj has a SQLite wrapper and it is stable though I didn't use it.
GiuseppeC
Have you a link for it ?
Endo
Sorry, I don't have. Actually not sure if it was for Red or R3.
It is not supposed to replace SQLite3, it's more a different project ....
GiuseppeC
When the bounty for ODBC red will be rewarderd another one will start for SQLLite 4
Endo
Pekr: As I understand SQLite4 is not *just* a key/value database, "SQLite4 is a complete, relational, transactional, ACID, SQL database database engine ". That key/alue is just one of its stored engines. So it actually is a new version of SQLite3 which is not backward compatible, but also not a replacement. Thanks for sharing the link by the way.
Endo
And it uses its key/value storage engine for all indexes in all tables in a database which is different than sqlite3.
Yes, that's the code being used to port this ODBC extension to a rebol 3 module
amacleod
Anyone of experience with RETS (Real Estate Transaction Standard)? Its a gateway to access an SQL database. It seems staight forward but it deals with cookies and I'm not sure how that is done with rebol.