Jump to content




[CC 1.48 - 1.5] ccDB - Connect With Real Databases

peripheral

88 replies to this topic

#1 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 26 October 2012 - 04:59 PM

Posted Image

Interface between ComputerCraft and any DBMS that comes with a JDBC 4 driver.

( Compatible with Xfel's Peripheral Cables )


Download Links:
v2.0.2 for CC 1.481 - 1.5 (MC 1.4.6 & MC 1.4.7):
(See bottom of post for older versions)

Changelog:
Spoiler


What can I do with it?

Connect to JDBC-capable databases and then execute queries, read and write data, etc. like e.g.:
executeQuery( "SELECT * FROM accounts.users WHERE password = 'secret'" )
getString( "surname" ) -> returns the element at column-label "surname" of the current row as a String
getBoolean( 3 ) -> returns the element at column-index 3 of the current row as a Boolean
updateString( "nickname", "lamarr" )
Or look at the example in the screenshot above! :)


Recipe: (subject to change, I'm open for suggestions)

Posted Image OR Posted Image
Or in ComputerCraft's creative tab.




Requirements:
Spoiler

Installation:
  • Download the necessary JDBC 4 drivers. Each driver should be a .JAR file. Store them to a location of your choice.
  • Next, if you use a batch file to launch your MC via Minecraft.jar or a launcher where you can add startup-arguments for MC:
    Put the .JAR file into a directory of your choosing (but not the mods folder).
    Then add this to your MC startup arguments:
    -Djdbc.drivers=com.mysql.jdbc.Driver -Xbootclasspath/a:C:\\mysql-connector-java-5.1.22-bin.jar
    Obviously, change com.mysql.jdbc.Driver to the package name of the JDBC driver you're using (see installation docs of the respective driver).
    Also change C:\\mysql-connector-java-5.1.22-bin.jar to the path and name you've stored your JDBC .JAR file.
    To load multiple drivers, separate them with a colon, like this: -Djdbc.drivers=com.mysql.jdbc.Driver:org.postgresql.Driver
    Also add the location of each JDBC driver as a separate -Xbootclasspath/a:LOCATION, where LOCATION is the absolute path to the respective driver.
    Lastly, keep in mind that the java command line parameters are case-sensitive!
    • As an easier alternative, simply put the .JAR driver file into your Minecraft's mods folder.
      If you are on Windows, then it should be %appdata%\.minecraft\mods\
      I recommend doing it the former way though, as that loads the drivers together with Minecraft instead of having Forge inject them during the mod loading process.
  • Download this peripheral, it's a .ZIP file. Don't extract it, simply put it into the mods folder as well.
  • Done :)

Available functions:
Spoiler

General usage:
Spoiler

Bugs:
Spoiler




Plans for the future:
Spoiler

"License":
Spoiler


Have fun! :)

Cheers,
Espen

Old versions (not supported):
Spoiler

Edited by Espen, 28 March 2013 - 06:09 AM.


#2 Tiin57

    Java Lunatic

  • Members
  • 1,412 posts
  • LocationIndiana, United States

Posted 26 October 2012 - 05:01 PM

Excellent work!

#3 bbqroast

  • Members
  • 124 posts

Posted 28 October 2012 - 02:11 AM

Could it be possible to use variables for holding connections?

So you could do soemthing like this:
a, b = mysql_connect("localhost","bbqroast","epicpassword")
c, d = mysql_connect("192.41.3.32","user","pass")
if a == true then
  e, f = mysql_select_db("database1", a)
  if e == true then
   // do stuff
  end
  g, h = mysql_select_db("database1", a)
  if g == true then
   // do stuff
  end
  end
end
Basically the mysql connect and select DB functions return "handles" which are then used by the other functions. This way you can open as many connections as you want. This also adds an extra layer of security, as the handle must be used to connect to the database. I imagine the handles could just be integers which Java has lined up to actual connections (no need for LUA to handle anything more intensive).

#4 Tiin57

    Java Lunatic

  • Members
  • 1,412 posts
  • LocationIndiana, United States

Posted 28 October 2012 - 10:49 AM

View Postbbqroast, on 28 October 2012 - 02:11 AM, said:

Could it be possible to use variables for holding connections?

So you could do soemthing like this:
a, b = mysql_connect("localhost","bbqroast","epicpassword")
c, d = mysql_connect("192.41.3.32","user","pass")
if a == true then
  e, f = mysql_select_db("database1", a)
  if e == true then
   // do stuff
  end
  g, h = mysql_select_db("database1", a)
  if g == true then
   // do stuff
  end
  end
end
Basically the mysql connect and select DB functions return "handles" which are then used by the other functions. This way you can open as many connections as you want. This also adds an extra layer of security, as the handle must be used to connect to the database. I imagine the handles could just be integers which Java has lined up to actual connections (no need for LUA to handle anything more intensive).
I have no idea.
However, just need to point something out. Doing
 if x == true then end 
is always unnecessary. Just do
 if x then end 


#5 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 28 October 2012 - 01:40 PM

View Postbbqroast, on 28 October 2012 - 02:11 AM, said:

Could it be possible to use variables for holding connections?
[...]

That would definitely be a possibility. I planned something similar for SQL statements, because I thought that opening too many connections would be a waste when you could just provide a statement-object per user that runs on the same connection. Admittedly though they would all run within the same user-context of this one connection and I would have to add an extra layer of access control via Java which - come to think of it - is really unnecessary.
Why reinvent the wheel when the database server already has built-in functionality for this, right?

So yeah, definitely something I'll put on my todo-list, as this makes much more sense.
Unfortunately I catched a cold, so at the moment I'm a bit incapacitated. But as soon as I'm better (1-2 days), I'll put this in the next version.

Thanks for the suggestion, very much appreciated!
If you have any other suggestions, ideas or questions, don't hesitate to ask. That's exactly the reason why this is in alpha. :D/>

EDIT: Updated OP accordingly.

Edited by Espen, 28 October 2012 - 01:49 PM.


#6 MaHuJa

  • Members
  • 26 posts

Posted 05 November 2012 - 01:31 PM

View Posttiin57, on 28 October 2012 - 10:49 AM, said:

However, just need to point something out. Doing
 if x == true then end 
is always unnecessary. Just do
 if x then end 


> = "text"==true
false
> = "text"==false
false

View PostEspen, on 28 October 2012 - 01:40 PM, said:

If you have any other suggestions, ideas or questions, don't hesitate to ask. That's exactly the reason why this is in alpha. :D/>

Can you, provided the appropriate jdbc drivers are installed, access other databases as well? Postgres in particular.
There's just so much wrong about mysql that I've stopped using it, and neither do I have the tools for it anymore.

#7 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 06 November 2012 - 09:15 AM

View PostMaHuJa, on 05 November 2012 - 01:31 PM, said:

Can you, provided the appropriate jdbc drivers are installed, access other databases as well? Postgres in particular.
There's just so much wrong about mysql that I've stopped using it, and neither do I have the tools for it anymore.

For the alpha version the plan was to develop exclusively with MySQL in mind. This way I can focus on getting it to work on at least one DBMS first.
Also I'm most experienced with MySQL compared to other DBMSes. But that's about to change due to my new courses this semester. :P/>
Having said that, I made sure from the beginning to not use MySQL-specific code, but to use the general java sql API only.
The only "hard-coded" part is where I explicitly initialize the MySQL driver ("com.mysql.jdbc.Driver"). But I only did that for legacy purposes, i.e. it is necessary for JDBC versions below 4.0.

So in theory then, it should be possible to use it with another DBMS (since I only used the java.sql API), but I haven't tested that yet.
At the moment I'm overhauling the way connections and statements are created & handled in order to allow a more dynamic use of multiple connections.
Once I've got that working well enough I'll start focusing on testing it with other drivers/DBMSes.

Cheers :D/>

#8 sirdabalot

  • Members
  • 115 posts

Posted 23 November 2012 - 01:02 PM

Hi, I'm getting this error:

Spoiler

Could you tell me what I've done wrong this time? ;)/> I think I installed all the prerequisites correctly...

#9 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 23 November 2012 - 03:17 PM

Hmm, it looks like Minecraft can't find the class. May I ask what CC version you're using?
And did you extract the .zip into the mods-folder or put it in there without extracting?

I'm not sure, but if you're using 1.47 then I might have to recompile for that.
For some reason I was under the impression the obfuscation didn't change, but I might've been wrong about that.
I'll do that in a few hours, but now I have to go to bed as it's almost in the morning and I can't think straight anymore.^^

I'll report back as soon as I got some shuteye. Cu! ;)/>

#10 sirdabalot

  • Members
  • 115 posts

Posted 23 November 2012 - 11:29 PM

View PostEspen, on 23 November 2012 - 03:17 PM, said:

-Snip-

Thanks for the reply, I am using CC 1.47 and didn't extract the zip, however I did use magic launcher to add the mod but also tried the mod folder option and both gave me the same error. ;)/>

#11 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 24 November 2012 - 03:46 AM

Ok, I was mistaken about the MCP changes. I remember having read that CC didn't need to do much for an update because the obfuscation didn't change and somehow I falsely remembered that to be the case for the update to MC 1.4.4
But as it turns out it was the update to MC 1.4.5 that didn't have an obfuscation change, whereas there actually was one from MC 1.4.2 to 1.4.4

To make it short: I just updated the OP by adding a download-link for CC 1.47 that should work with both MC 1.4.4 and 1.4.5 -_-/>
I also changed the install-instructions a bit to show an alternative way for how to install the JDBC driver(s).

Regarding the progress of the mod:
Since the first release I've been working on the mod, it's just that I follow a work-in-secret-until-it's-done work ethic.^^
But I am still working on it! Not every day, as I have other projects going on + exams I have to learn for. But it's progressing steadily.

Currently I've managed to support any database managment systems for which JDBC drivers exist. So it's not a MySQL-only deal anymore.
I've also had success with making it possible to create multiple connections ingame, which was actually a lot trickier than I anticipated.
But it works and now I just have to streamline the process for the end-user from within CC.

There's still some restructuring I need to do, but I'm progressing at a good pace.
When I'm done there might be a namechange to "ccDB" or similar to account for the fact that it's not limited to MySQL-only anymore.
But first I have to finish the current TODO's and make it pwetty. ;)/>

#12 jsom

  • New Members
  • 1 posts
  • LocationUkraine

Posted 25 November 2012 - 02:59 AM

Excellent work!
need a simple example of how to use ResultSet, print on the monitor  or something similar.
I have successfully connected to the database and stuck)

#13 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 25 November 2012 - 04:39 AM

View Postjsom, on 25 November 2012 - 02:59 AM, said:

Excellent work!
need a simple example of how to use ResultSet, print on the monitor  or something similar.
I have successfully connected to the database and stuck)
Let's say the database table 'persons' has this structure:
ID - firstname - lastname - age - nickname - city - country

And let's further assume, that there are the following rows for that structure:
0 - Peter - Parker - 25 - spidey - Arachnopolis - Fantasyland
1 - Jenny - Carsson - 17 - bluebird - Seattle - USA
2 - Sherlock - Holmes - 35 - magnyfire - London - UK

Then the following code...
Spoiler

... should output this:
Person #1
============================
First Name: Peter
Last Name: Parker
Nickname: spidey
Age: 25
City: Arachnopolis

Person #2
============================
First Name: Jenny
Last Name: Carsson
Nickname: bluebird
Age: 17
City: Seattle

Person #3
============================
First Name: Sherlock
Last Name: Holmes
Nickname: magnyfire
Age: 35
City: London


#14 infchem

  • Members
  • 3 posts

Posted 27 November 2012 - 08:40 AM

Great job.
But.....could you downgrade it to CC 1.4.1?

Greetings,
infchem

#15 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 27 November 2012 - 09:27 AM

@infchem:
That should be no problem, as it doesn't seem there were any major changes regarding the peripheral API since that CC version that would have any on this mod.
Have to get the MCP for MC1.2.5 and recompile which should be pretty straightforard.
I'm about to go out for a few hours, but as soon as I'm back I'll do it

#16 infchem

  • Members
  • 3 posts

Posted 27 November 2012 - 09:35 AM

Thanks for your time!

I'll use your mod with my students in highschool for teaching computer science.
For motivational reasons i choose minecraft as the programming environment :)

#17 Espen

    Curious Explorer

  • Members
  • 708 posts

Posted 27 November 2012 - 11:03 AM

View Postinfchem, on 27 November 2012 - 09:35 AM, said:

Thanks for your time!

I'll use your mod with my students in highschool for teaching computer science.
For motivational reasons i choose minecraft as the programming environment :)
Oh wow, didn't expect that to happen.^^
But you are aware that the alpha version can only read form the database? No offense, just checking. Don't want you to find out during your class.
I am currently working on the next version which will allow manipulation as well, along with being able to connect to other DBMS than MySQL.
And the way connections are being created from within CC will undergo a little change to accomodate multiple DB server connections.

I can't really say how much longer it will take, but a rough estimate would be sometime next week, depending on my motivation and RL stuff.
But if you're gonna use this in class, then I feel inclined to put a bit more time into it and make the code nice and tidy. ^_^

EDIT:
I spoke too soon. That is, about it being pretty straightforward to downgrade the mod to MC 1.2.5 -_-
Didn't think of Forge and its changes since 1.2.5. Ergo I'm gonna have to make a bunch of code changes to accomodate for these changes, or the mod won't compile. So it won't be that easy after all. Just wanted to let you know.^^

Edited by Espen, 27 November 2012 - 11:40 AM.


#18 KaoS

    Diabolical Coder

  • Members
  • 1,510 posts
  • LocationThat dark shadow under your bed...

Posted 27 November 2012 - 12:50 PM

nice innovation there :) I did not see the peripheral block coming; I must admit it is a great way of preventing global communication hacks

#19 sirdabalot

  • Members
  • 115 posts

Posted 02 December 2012 - 12:19 AM

View Postinfchem, on 27 November 2012 - 09:35 AM, said:

Thanks for your time!

I'll use your mod with my students in highschool for teaching computer science.
For motivational reasons i choose minecraft as the programming environment :)

Every day, my old highscool seems to get crapper and crapper... :(

#20 infchem

  • Members
  • 3 posts

Posted 18 December 2012 - 09:51 AM

Sorry for the delay. I'll try to work with CC 1.4.7 and CCLan instead of Redpower, so I don't need an older mc. I evaluate your block on thursday ^_^





1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users