kotlinlang #exposed

Hi all, I’m trying to return values as described in the wiki ( https://github.com/JetBrains/Exposed/wiki/Transactions#accessing-returned-values ), but I’m having trouble The following code successfully prints 10 rows:

Copy code

transaction(Connection.TRANSACTION_SERIALIZABLE, 1) {
    Message
        .slice(Message.text)
        .selectAll()
        .limit(10)
        .forEach { println(it) }
}

But when I try to return the rows and use them outside of the transaction, it doesn’t seem to work:

Copy code

val messages = transaction(Connection.TRANSACTION_SERIALIZABLE, 1) {
    Message
        .slice(Message.text)
        .selectAll()
        .limit(10)
        .toList()
}

messages.forEach { println(it) }

I’m getting

java.lang.IllegalStateException: No transaction in context

. When I set a breakpoint after the transaction,

messages

has length 10, but trying to access any elements throws an exception. I’m pretty new to this and wasn’t able to find anything by googling. Could someone help me figure out what I have wrong?

Marcin Wisniowski

01/23/2019, 5:35 PM

I'm using DAO, is the only solution to use DSL and then

wrapRows

back?

Tasos Stamadianos

01/24/2019, 12:18 AM

Hi there, I'm using a database with

PostGIS

support, and I'd like to call a

PostGIS

function during an insert, but I really can't figure out how to do this. I've seen examples involving

ExpressionWithColumnType

, but nothing that actually uses this function during an

insert

in a

transaction

. Some relevant code:

Copy code

abstract class SQLFunction<T>(override val columnType: IColumnType) : ExpressionWithColumnType<T>()

class ST_Centroid(private val poly: MultiPolygon): SQLFunction<Point>(PointColumnType()) {
    override fun toSQL(queryBuilder: QueryBuilder) = "ST_Centroid($poly)"
}

I'm trying to do this now:

[DbFields.center] = ST_Centroid(boundaryPoly)

where

boundaryPoly

is a PostGIS multipolygon. Any help would be greatly appreciated 🙂

APXEOLOG

01/26/2019, 12:57 AM

Hello guys, how do i select max value of a specific column?

hyukchan

01/27/2019, 1:30 PM

Hi guys, I'm having connection timed out after some minutes ... Is there any conf to set to make it try to reconnect by itself ?

Edouard Goossens

01/28/2019, 9:55 PM

Hi everyone, I’m pretty new to exposed, and I’m wondering if there’s a way to do something equivalent to:

SELECT field1, field2, field2-field1 AS diff FROM table

using DAO. Thanks!

Kevin

01/29/2019, 8:10 AM

Is it possible to do something like this with the DSL?

Copy code

select date, message.ROWID, id, text, filename, is_from_me, mime_type
from message
       left join handle on message.handle_id = handle.ROWID
       inner join chat_message_join c on message.ROWID = c.message_id and c.chat_id = 259
       left join message_attachment_join maj on message.ROWID = maj.message_id
       left join attachment a on maj.attachment_id = a.ROWID;

I’m pretty new to exposed and SQL in general, so I’m not sure if this is even the right way to do things in SQL.

Alexander Potukar

01/30/2019, 10:39 AM

I have an "updated_at" attribute in a PostgreSQL table updated by a trigger, so I just want to read this attribute but I want to ignore it when writing an entry via Exposed. One solution would be having two classes, one read model and one write model. However, is there a solution with Exposed to exclude a non-nullable field when it comes to inserting data?

bjonnh

02/02/2019, 12:26 AM

what is a good way to test for database-related code (using JUNIT5 if it matters)

samir

02/06/2019, 10:28 AM

Is it possible to get this to work with exposed in a transaction block?

Copy code

SET @total = 2; 
PREPARE STMT FROM "SELECT * FROM my_table LIMIT ?"; 
EXECUTE STMT USING @total;

I know that this is not type safe and that it is better to use DSL/DAO but the requirements is that statements should be defined as strings from an admin interface.

Copy code

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE STMT FROM "SELECT * FROM my_table LIMIT ?";
EXECUTE STMT USING @total' at line 2
SQL: [SET @total = 2;
PREPARE STMT FROM "SELECT * FROM my_table LIMIT ?";
EXECUTE STMT USING @total;]

Kevin

02/08/2019, 8:49 AM

connection check

timm

02/10/2019, 8:41 PM

is there anything i have to do to get exposed to call a sequence for id generation besides declating the id column as follows?

Copy code

override val id: Column<EntityID<Long>> = long("id").autoIncrement("hibernate_sequence").primaryKey().entityId()

Hasan

02/11/2019, 2:12 PM

Hi everyone, This might be a very specific problem, or I might be doing something wrong. I am playing around with

Exposed

and trying to use it with

postgresql

. I imported the latest verison of the

postgresql jdbc driver

using the maven dependency:

Copy code

maven
<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>42.2.5.jre7</version>
	<type>bundle</type>
</dependency>

And I attempted to connect to the database using:

Copy code

kotlin
Database.connect(
	"jdbc:<postgresql://localhost:5432/my_db>", 
	driver = "org.postgresql.Driver", 
	user = "postgres", 
	password = "welcome")

And this is the error I'm getting:

Copy code

Exception in thread "main" java.lang.ClassNotFoundException: org.postgresql.Driver at
java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:582) at 
java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:190) at 
java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:499) at 
java.base/java.lang.Class.forName0(Native Method) at java.base/java.lang.Class.forName(Class.java:291) at 
org.jetbrains.exposed.sql.Database$Companion.connect(Database.kt:112) at 
org.jetbrains.exposed.sql.Database$Companion.connect$default(Database.kt:111) at 
com.example.test.ExposedTest.connect(ExposedTest.kt:30) at com.example.test.HelloKt.main(Hello.kt:5)

Can you spot what I might be doing wrong? Thanks in advance!

👍 1

pjagielski

02/13/2019, 11:35 AM

Hi, I wrote a post how we use Exposed at TouK, take a look 😉 https://medium.com/@pjagielski/how-we-use-kotlin-with-exposed-at-touk-eacaae4565b5 https://www.reddit.com/r/Kotlin/comments/aq45o6/how_we_use_kotlin_with_exposed_for_sql_access_at/

🤘 2

👍 2

Kevin

02/18/2019, 11:12 AM

I want to log how long it takes to execute statements. I made a class that implements

StatementInterceptor

and overrode the

beforeExecution

and

afterExecution

methods. Is this the right approach? Or am I missing some functionality that is already built in?

tjb

02/19/2019, 4:19 AM

or is there docs on how to use exposed with a data class?

tjb

02/19/2019, 6:38 AM

Copy code

return transaction {
      PostDAO.wrapRows(
        Posts
          .innerJoin(PostsTags, {Posts.id}, {<http://PostsTags.post|PostsTags.post>})
          .innerJoin(Tags, {Tags.id}, {PostsTags.tag})
          .selectAll()
          .andWhere {
            Posts.title like "%$query%" or (Tags.name inList tags)
          }
          .withDistinct()
      ).map {
        it.toModel()
      }
      .distinct()
      .toList()
    }

lovis

02/20/2019, 2:24 PM

Hey there 🙂 I wondered what’s the rationale behind not matching the common sql-syntax with the

select

clause. Someone who’s used to sql would expect the dsl to work something like this:

Copy code

val gamailUsers = Users.select(id, name, email)
  .where { email like "%@gmail.com"}
  .map { ... }

But instead expose uses

slice

to do the projection. And select to do the selection. Why is this? I don’t realy have an opinion, I just want to know what the reason behind this decision was.

Vinicius Carvalho

03/01/2019, 9:05 PM

Copy code

15:58:19.284 [main] DEBUG Exposed - SELECT products.upc, products.sku, products.name, products.price, products.type, products.manufacturer, products.description, products.model, products.image, products.last_updated FROM products WHERE products.upc = 12
SQL: SELECT products.upc, products.sku, products.name, products.price, products.type, products.manufacturer, products.description, products.model, products.image, products.last_updated FROM products WHERE products.upc = 12
15:58:40.625 [main] WARN Exposed - Transaction attempt #1 failed: Invalid argument value: java.io.NotSerializableException. Statement(s): null
java.sql.SQLException: Invalid argument value: java.io.NotSerializableException

asad.awadia

03/03/2019, 9:16 PM

Is it possible to concat two columns and then use that combined with an inList? E.g

area_code_column + phone_number_column inList listOf(...)

bdawg.io

03/04/2019, 6:44 PM

Are there any examples of using Exposed without using thread local?

galex

03/05/2019, 5:55 AM

Would be nice to have that in the documentation!

galex

03/06/2019, 7:46 AM

How do I query table B to count how many rows have my id from Table A? Basically a calculated field based on another select?

SrSouza

03/06/2019, 4:52 PM

Hi guys, I want to know something about Exposed DAO How I can update the object after change him

Copy code

daoObj.name = "new Name"
transaction(database) {
   daoObj.update()
}

Something like this have?

galex

03/08/2019, 8:01 AM

I see there’s a check function on a field (Column<T>), is there an example using this somewhere?

cedric

03/10/2019, 10:42 PM

Exposed doesn't seem to support UPSERT. is that correct? If it is, what's the most idiomatic way to insert a row if it's not already present, or update it otherwise?

spand

03/14/2019, 9:35 AM

How do I set the default value to

null

of a

.nullable()

column ? Many of these decorating functions are defined as

fun <T:Any> Column<T>.default(

spand

03/14/2019, 9:51 AM

Another thread: What is the use case for having

InsertStatement.get

return

T?

? The only use in the project that ought to use this functionality at all is

fun <Key:Comparable<Key>, T: IdTable<Key>> T.insertIgnoreAndGetId

. It seems to me that it ought to be changed to

and instead define a

InsertStatement.tryGet

for the much rarer use so can avoid having !! just to retrieve a generated key.