Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Snowflake: stored procedure/functions defintion #6286

Closed
serge-rider opened this issue Jul 16, 2019 · 8 comments

Comments

@serge-rider
Copy link
Member

commented Jul 16, 2019

We need to support stored procedures/function defintion reading for Snowflake

@serge-rider serge-rider added this to the 6.1.3 milestone Jul 16, 2019

@serge-rider serge-rider added this to Backlog in DBeaver project via automation Jul 16, 2019

@serge-rider serge-rider moved this from Backlog to Ready for QA in DBeaver project Jul 16, 2019

@uslss uslss self-assigned this Jul 17, 2019

@uslss

This comment has been minimized.

Copy link
Collaborator

commented Jul 17, 2019

verified

@uslss uslss closed this Jul 17, 2019

DBeaver project automation moved this from Ready for QA to Done Jul 17, 2019

@chiman-wend

This comment has been minimized.

Copy link

commented Aug 13, 2019

How has this feature been implemented? Right-clicking on a stored procedure and generating the DDL throws the following error: (I replaced the procedure name in the log). I am using Dbeaver 6.1.4

!ENTRY org.jkiss.dbeaver.model 4 0 2019-08-13 12:58:53.530
!MESSAGE SQL Error [2003] [02000]: SQL compilation error:
Function 'xyz' does not exist.
!SUBENTRY 1 org.jkiss.dbeaver.model 4 0 2019-08-13 12:58:53.530
!MESSAGE SQL compilation error:
Function 'xyz' does not exist.
!STACK 0
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
Function 'xyz' does not exist.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:139)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:485)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:362)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:504)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:249)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:187)
at net.snowflake.client.core.SFStatement.describe(SFStatement.java:206)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.parseSql(SnowflakePreparedStatementV1.java:137)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.(SnowflakePreparedStatementV1.java:128)
at net.snowflake.client.jdbc.SnowflakeConnectionV1.prepareStatement(SnowflakeConnectionV1.java:650)
at net.snowflake.client.jdbc.SnowflakeConnectionV1.prepareStatement(SnowflakeConnectionV1.java:567)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCConnectionImpl.prepareStatement(JDBCConnectionImpl.java:244)
at org.jkiss.dbeaver.ext.snowflake.model.SnowflakeMetaModel.getProcedureDDL(SnowflakeMetaModel.java:89)
at org.jkiss.dbeaver.ext.generic.model.GenericProcedure.getObjectDefinitionText(GenericProcedure.java:273)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$1.generateSQL(GenerateSQLContributor.java:176)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$1.generateSQL(GenerateSQLContributor.java:1)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$SQLGenerator.run(GenerateSQLContributor.java:438)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$1.run(GenerateSQLContributor.java:152)
at org.jkiss.dbeaver.ui.UIUtils.lambda$11(UIUtils.java:1632)
at org.eclipse.jface.operation.ModalContext.runInCurrentThread(ModalContext.java:438)
at org.eclipse.jface.operation.ModalContext.run(ModalContext.java:354)
at org.eclipse.ui.internal.WorkbenchWindow.lambda$4(WorkbenchWindow.java:2249)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:72)
at org.eclipse.ui.internal.WorkbenchWindow.run(WorkbenchWindow.java:2247)
at org.eclipse.ui.internal.progress.ProgressManager$RunnableWithStatus.run(ProgressManager.java:1101)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:72)
at org.eclipse.ui.internal.progress.ProgressManager.lambda$25(ProgressManager.java:978)
at org.eclipse.swt.widgets.Synchronizer.syncExec(Synchronizer.java:236)
at org.eclipse.ui.internal.UISynchronizer.syncExec(UISynchronizer.java:147)
at org.eclipse.swt.widgets.Display.syncExec(Display.java:4580)
at org.eclipse.ui.internal.progress.ProgressManager.runInUI(ProgressManager.java:978)
at org.jkiss.dbeaver.ui.UIUtils.runInUI(UIUtils.java:1631)
at org.jkiss.dbeaver.ui.UIUtils.runInUI(UIUtils.java:1644)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$GenerateSQLDialog.createDialogArea(GenerateSQLContributor.java:645)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$GenerateSQLDialog.createDialogArea(GenerateSQLContributor.java:1)
at org.eclipse.jface.dialogs.Dialog.createContents(Dialog.java:771)
at org.jkiss.dbeaver.ui.editors.sql.dialogs.ViewSQLDialog.createContents(ViewSQLDialog.java:75)
at org.eclipse.jface.window.Window.create(Window.java:431)
at org.eclipse.jface.dialogs.Dialog.create(Dialog.java:1099)
at org.jkiss.dbeaver.ui.dialogs.BaseDialog.create(BaseDialog.java:72)
at org.eclipse.jface.window.Window.open(Window.java:788)
at org.jkiss.dbeaver.ui.editors.sql.generator.GenerateSQLContributor$8.run(GenerateSQLContributor.java:618)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:474)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:568)
at org.eclipse.jface.action.ActionContributionItem.lambda$4(ActionContributionItem.java:400)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4131)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1055)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3944)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3547)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1173)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1062)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:644)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:566)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)
at org.jkiss.dbeaver.core.application.DBeaverApplication.start(DBeaverApplication.java:245)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:137)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:107)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:400)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:661)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:597)
at org.eclipse.equinox.launcher.Main.run(Main.java:1476)

@serge-rider

This comment has been minimized.

Copy link
Member Author

commented Aug 13, 2019

@chiman-wend Thank you for checking this. Please open Query Manager view (main menu Window->Show View) and see what exactly query is executed by DBeaver to read procedure definitions.

It should be something like
DESCRIBE FUNCTION DB_NAME.SCHEMA_NAME.PROC_NAME(PROC_SIGNATURE)

Try to execute it in SQL editor. If it fails - do you have any clue why?

@chiman-wend

This comment has been minimized.

Copy link

commented Aug 13, 2019

@serge-rider Opening the Query Manager View does not reveal any query executed by DBeaver, at least not when right-clicking on Procedure or Function and generating DDL. Nevertheless, I can see the SQL executed against the database in the Snowflake History View.
Therefore, I can tell that the implementation works, but only for UDFs, i.e. functions. Dbeaver executes

DESCRIBE FUNCTION DB_NAME.SCHEMA_NAME.PROC_NAME(PROC_SIGNATURE)

The reason why my attempt above failed, was because I tested the feature using a procedure. To get the procedure ddl you would need to change the query to something like:

DESCRIBE PROCEDURE DB_NAME.SCHEMA_NAME.PROC_NAME(PROC_SIGNATURE)

I am not sure, if it is relevant, but in Dbeaver functions and procedures for Snowflake Connections are stored in the same folder

@chiman-wend

This comment has been minimized.

Copy link

commented Aug 13, 2019

While testing this feature, I noticed that using
DESCRIBE FUNCTION... or DESCRIBE PROCEDURE...
you will only get the Body of the function and procedure returned, but not the complete DDL.

I would suggest changing to
SELECT GET_DDL('FUNCTION', 'DB_NAME.SCHEMA_NAME.FUNC_NAME(FUNC_SIGNATURE') and
SELECT GET_DDL('PROCEDURE', 'DB_NAME.SCHEMA_NAME.PROC_NAME(PROC_SIGNATURE')
That way, you will get a complete DDL.

An example to illustrate:
SELECT GET_DDL('FUNCTION', 'DB_NAME.SCHEMA_NAME.FUNC_NAME(FUNC_SIGNATURE') returns:

CREATE OR REPLACE FUNCTION "JS_FACTORIAL"(D FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS '
if (D <= 0) {
return 1;
} else {
var result = 1;
for (var i = 2; i <= D; i++) {
result = result * i;
}
return result;
}
';

Whereas DESCRIBE FUNCTION DB_NAME.SCHEMA_NAME.FUNC_NAME(FUNC_SIGNATURE) returns:

if (D <= 0) {
return 1;
} else {
var result = 1;
for (var i = 2; i <= D; i++) {
result = result * i;
}
return result;
};

@serge-rider

This comment has been minimized.

Copy link
Member Author

commented Aug 13, 2019

@chiman-wend thanks you for testing. Generally it should use PROCEDURE or FUNCTION clause according to the object type. but by some reason it doesn't work in some cases. I'll investigate this a bit more.

BTW to see these queries in DBeaver you need to enable metadata/utils queries in filter settings ( or in the context menu)

serge-rider added a commit that referenced this issue Aug 13, 2019

serge-rider added a commit that referenced this issue Aug 13, 2019

@serge-rider

This comment has been minimized.

Copy link
Member Author

commented Aug 13, 2019

Fixed

serge-rider added a commit that referenced this issue Aug 13, 2019

@chiman-wend

This comment has been minimized.

Copy link

commented Aug 14, 2019

@serge-rider Thank you for the quick fix!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
3 participants
You can’t perform that action at this time.