Working with database

[Aleksandar_Ar]

My friend wants to have SQLite on his website and he created folder for it in “/MembersOnly/DatabaseFiles/users.sqlite”

My structure looks like this:

/root
/Register/index.cfm
/MembersOnly/DatabaseFiles/users.sqlite

This is the code I am trying to use but it doesn’t work:

<cfset request.pageTitle = "Registration">

<!--- Define the relative path to your SQLite database file --->
<cfset database_path = "../MembersOnly/DatabaseFiles/users.sqlite">

<cfif IsDefined("form.username") and IsDefined("form.email")>
    <!--- Establish Connection to SQLite Database --->
    <cfquery name="insertUser" datasource="#database_path#">
        INSERT INTO users (username, email)
        VALUES (
            <cfqueryparam value="#form.username#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.email#" cfsqltype="CF_SQL_VARCHAR">
        )
    </cfquery>

    <!--- Check if the registration was successful --->
    <cfif insertUser.recordCount>
        <p class="style1">Registration successful!</p>
    <cfelse>
        <p class="style1">Registration failed. Please try again.</p>
    </cfif>
</cfif>

<p align="center" class="style2">REGISTRATION FORM</p>
<p class="style1">In Development process</p>

<div class="reg__container">
    <form class="form__container" method="post">
        <label class="form-label" for="username">Username:</label>
        <input class="form-input" type="text" id="username" name="username" placeholder="Enter your username" required>

        <label class="form-label" for="email">Email:</label>
        <input class="form-input" type="email" id="email" name="email" placeholder="Enter your email" required>

        <div class="form-checkbox-container">
            <input class="form-checkbox" type="checkbox" id="subscribe" name="subscribe">
            <label class="form-label" for="subscribe">Agree to out <a href="#">terms</a> of use.</label>
        </div>
        <button class="form-button" type="submit">Submit</button>
    </form>
</div>

I am trying to access this users.sqlite db from index.cfm file but I keep getting error.

Don’t forget to tell us about your stack!

OS: Windows 10

[Roberto_Marzialetti]

@Aleksandar_Ar

I am trying to access this users.sqlite db from index.cfm file but I keep getting error.

You have to create a Datasource
Something like this in yout Application.cfc

this.datasources[ "myDasourceSqlLite" ] = {
  class: 'org.sqlite.JDBC',
  connectionString: 'jdbc:sqlite:#ExpandPath('/MembersOnly/DatabaseFiles/users.sqlite')#'
};

than you can use your DB:

<cfquery datasource="myDasourceSqlLite">

I’m not sure if the SQL Lite driver is readily available within Lucee. If not, you need to download the file and copy it to some /lib directory. And then, add in your Application.cfc:

this.javaSettings = {LoadPaths = [ "/lib/your-file-driver-sqllite.jar" ] }>

HTH

[Aleksandar_Ar]

Where exactly should I add this code

this.datasources[ "myDasourceSqlLite" ] = {
  class: 'org.sqlite.JDBC',
  connectionString: 'jdbc:sqlite:#ExpandPath('/MembersOnly/DatabaseFiles/users.sqlite')#'
};

Because I have plenty of stuff in Application.cfc

When I added on the top, I got this error:

[Roberto_Marzialetti]

@Aleksandar_Ar

Because I have plenty of stuff in Application.cfc

At the top of Application.cfc is ok

Some docs:

Datasources :: Lucee Documentation

[Aleksandar_Ar]

Alrighty, I configured some things and this is the current error I am getting:

“lucee.commons.lang.ClassException: cannot load class through its string name, because no definition for the class with the specified name [org.sqlite.JDBC] could be found caused by (java.lang.ClassNotFoundException:org.sqlite.JDBC not found by lucee.core [48];java.lang.ClassNotFoundException:org.sqlite.JDBC;)”

No more errors regarding no datasource found.

[Roberto_Marzialetti]

cannot load class through its string name, because no definition for the class

Missing driver class of SQL Lite.

Try this:

@Roberto_Marzialetti

I’m not sure if the SQL Lite driver is readily available within Lucee. If not, you need to download the file and copy it to some /lib directory. And then, add in your Application.cfc:

this.javaSettings = {LoadPaths = [ "/lib/YOUR-file-driver-sqllite.jar" ] }>

HTH

[Aleksandar_Ar]

So I need to download only this file:

And then place it to /lib directory. Afterwards in my application.cfc do this:

this.javaSettings = {LoadPaths = [ "/lib/sqlite-jdbc-3.45.2.0.jar" ] }>

Is that correct?

[Roberto_Marzialetti]

@Aleksandar_Ar

Is that correct?

Let’s try. Show a little faith

[Aleksandar_Ar]

So, I added that in lib folder but I am unsure where to add:

this.javaSettings = {LoadPaths = [ "/lib/sqlite-jdbc-3.45.2.0.jar" ] }

Because if I just add it in application.cfc I get this:

[andreas]

You have incorrect syntax in your cfscript. Replace the greater sign > with a semicolon ;.

[Aleksandar_Ar]

Okay, I fixed that but now I am getting this error when I click on Register button:

When it comes to previous problem, is this ok now:

<cfscript>
    this.datasources = {
        mySQLiteDB = {
            class: "org.sqlite.JDBC",
            connectionString: "jdbc:sqlite:/MembersOnly/DatabaseFiles/users.sqlite",
            username: "",
            password: "",
            driver: "org.sqlite.JDBC"
        }
    };
	this.javaSettings = {LoadPaths = [ "/WEB-INF/lucee/lib/sqlite-jdbc-3.45.2.0.jar" ] }
</cfscript>

[Roberto_Marzialetti]

try writing first:
this.javaSettings = { bla, bla }

and then:
this.datasources = { bla, bla }

[Aleksandar_Ar]

You mean literally like that?

[Roberto_Marzialetti]

I mean: first you have to load the driver, and then use it in the datasource.
It’s just an idea, I’m not sure it works.

[Aleksandar_Ar]

I believe that one is fixed, but here is another issue:

[Roberto_Marzialetti]

@Aleksandar_Ar

another issue:

Lucee says:

C:\MembersOnly does not exists

You have to fix the position of your db file.

[Aleksandar_Ar]

That’s odd because it does exist. But, does it have to be inside Lucee? Because Lucee is inside root folder, but other folders like Registration and MembersOnly are outside of Lucee. MembersOnly have db inside. Could it be that Lucee can’t locate DB because membersOnly is not in Lucee folder?

This is so confusing for me.

/root
application.cfc
/Register
/MembersOnly
/DBFiles
users.sqlite
/WEB-INF
/lucee

This is the current structure.

this.javaSettings = {LoadPaths = [ "/WEB-INF/lucee/lib/sqlite-jdbc-3.45.2.0.jar" ] }
    this.datasources = {
        mySQLiteDB = {
            class: "org.sqlite.JDBC",
            connectionString: "jdbc:sqlite:/MembersOnly/DatabaseFiles/users.sqlite",
            username: "",
            password: "",
            driver: "org.sqlite.JDBC"
        }
    };

[Roberto_Marzialetti]

Try to use ExpandPath:

connectionString: "jdbc:sqlite:#ExpandPath('/MembersOnly/DatabaseFiles/users.sqlite')#"

Try to use the backslash \ in path too.

[Aleksandar_Ar]

Okay, I think we are closer and closer to make this work. Now I am getting more specific error because now it seems the only issue is the query for inserting:

This is the page:

<cfset request.pageTitle = "Registration">

<cfif IsDefined("form.username") and IsDefined("form.email")>
    <!--- Establish Connection to SQLite Database --->
    <cfquery name="insertUser" datasource="mySQLiteDB">
        INSERT INTO users (username, email)
        VALUES (
            <cfqueryparam value="#form.username#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.email#" cfsqltype="CF_SQL_VARCHAR">
        )
    </cfquery>

    <!--- Check if the registration was successful --->
    <cfif insertUser.recordCount>
        <p class="style1">Registration successful!</p>
    <cfelse>
        <p class="style1">Registration failed. Please try again.</p>
    </cfif>
</cfif>

<p align="center" class="style2">REGISTRATION FORM</p>
<p class="style1">In Development process</p>

<div class="reg__container">
    <form class="form__container" method="post">
        <label class="form-label" for="username">Username:</label>
        <input class="form-input" type="text" id="username" name="username" placeholder="Enter your username" required>

        <label class="form-label" for="email">Email:</label>
        <input class="form-input" type="email" id="email" name="email" placeholder="Enter your email" required>

        <div class="form-checkbox-container">
            <input class="form-checkbox" type="checkbox" id="subscribe" name="subscribe">
            <label class="form-label" for="subscribe">Agree to out <a href="#">terms</a> of use.</label>
        </div>
        <button class="form-button" type="submit">Submit</button>
    </form>
</div>

[Roberto_Marzialetti]

@Aleksandar_Ar

Okay, I think we are closer and closer to make this work.

From an INSERT query you haven’t “recordcount”.

“recordcount” is available only when you make SELECTs to find out the number of records present in the query.

If you want to know if the insertion was successful (why wouldn’t it?) you have to use a try/catch.