SQL Server setup

[mikemeer]

I am in the Data Space of the app builder. I want to add a SQL server database - how do I do it?

[lianjasupport]

You import the SQL server database (click the small cog icon) then build you apps against the copy. In final release the "Data Source" of the app, page or section is used to connect via odbc to your live data.

[mikemeer]

I do that and after the 3rd table, it stops working. Help...

[lianjasupport]

Set debug on

Then do it

And let us see the c:\lianja\debug\debug_client.txt file

It will help us identify the issue.

[mikemeer]

how do I set debug on?

[HankFay]

Hi Mike,

go to the Console and issue:

set debug on

That affects debug for all parts of Lianja, including the current app.

Hank

[mikemeer]

Ok. So I go to Console option - Recital/VFP tab (Under Input) - I type "Set step on". Then what? I see no interaction telling me anything has changed.

Thanks,
Mike

[lianjasupport]

After typing set debug on in the Recital/VFP tab in the Console Workspace, certain debug information will be logged to the file C:\Lianja\debug\debug_client.txt. So, if you rerun the operation that caused the problem, information about it will be logged in debug_client.txt. Send us the file (ideally Submit a Ticket from the website Support menu and attach the file) and we can investigate the problem.

Thanks.

[mikemeer]

I tried that and it doesn't write anything to the debug file.

[lianjasupport]

The debug output should be in:

c:\lianja\debug\debug_client.txt

[mikemeer]

The file has a size of 0. It doesn't change.

[lianjasupport]

Try selecting the ODBC tab in the console workspace.

Type in the DSN name. and connect. If the connection is not succesful the reason will be displayed in the "Output" panel. If it is successful then issue:

dir

You will see the table names and schemas from the MSSQL database if you have used them.

Now, assuming you have a schema called mike and a table called test issue the following commands:

use mike.test
? reccount()
list structure
list

[lianjasupport]

The file has a size of 0. It doesn't change.


Hi Mike, do you have a debug.txt in the same directory?

[siotti]

The file has a size of 0. It doesn't change.

I think, you should close lianja. After leaving lianja, the file is written.

[mikemeer]

I can't close the program the normal way. See the picture attached.110

Support: I would be willing to work with you if you want to send me a "special" dll that has debugging code.

Mike

[lianjasupport]

Mike, did you open the odbc connection as we suggested in the ODBC tab of the console workspace?

If so, issue a DIR and let us see the output.

Then issue USE schemaname.tablename on each table until you identify the table causing the problem.

Then let us see the structure of that MSSQL table.

[mikemeer]

Here is the Dir List. 111
It crashes on the first table.
Here is the structure

USE [Solovue_Welland]
GO

/****** Object: Table [dbo].[account] Script Date: 08/03/2012 10:46:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[account](
[account_id] [dbo].[primary_key_id] NOT NULL,
[company_id] [dbo].[id] NULL,
[account_num] [decimal](6, 1) NULL,
[description] [char](50) NULL,
[account_type] [dbo].[code] NULL,
[ar_adjust_fl] [dbo].[flag] NULL,
[ar_cash_fl] [dbo].[flag] NULL,
[ar_income_fl] [dbo].[flag] NULL,
[bad_debt_fl] [dbo].[flag] NULL,
[system_fl] [dbo].[flag] NULL,
[qb_link_info] [char](30) NULL,
[inactive_fl] [dbo].[flag] NULL,
[primary_fl] [dbo].[flag] NULL,
[commission_fl] [dbo].[flag] NULL,
[created_on_user_id] [dbo].[id] NULL,
[created_on_dt] [dbo].[datetime_both] NULL,
[last_updated_user_id] [dbo].[id] NULL,
[last_updated_dt] [dbo].[datetime_both] NULL,
[locked_by_user_id] [dbo].[id] NULL,
[locked_dt] [dbo].[datetime_both] NULL,
[CreatedBy] [nvarchar](128) NOT NULL,
[ModifiedBy] [nvarchar](128) NOT NULL,
[Created] [datetime] NOT NULL,
[Modified] [datetime] NOT NULL,
[RowVersion] [int] NOT NULL,
[cash_fl] [dbo].[flag] NULL,
[cogs_fl] [dbo].[flag] NULL,
[inventory_fl] [dbo].[flag] NULL,
[Po_wash_fl] [dbo].[flag] NULL,
[Materials_fl] [dbo].[flag] NULL,
[Show_on_ar_fl] [dbo].[flag] NULL,
CONSTRAINT [account_pkey] PRIMARY KEY CLUSTERED
(
[account_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__company__7E6CC920] DEFAULT ((0)) FOR [company_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__account__7F60ED59] DEFAULT (space((0))) FOR [account_type]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_adju__00551192] DEFAULT ((0)) FOR [ar_adjust_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_cash__014935CB] DEFAULT ((0)) FOR [ar_cash_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__ar_inco__023D5A04] DEFAULT ((0)) FOR [ar_income_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__system___03317E3D] DEFAULT ((0)) FOR [system_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__inactiv__0425A276] DEFAULT ((0)) FOR [inactive_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__primary__0519C6AF] DEFAULT ((0)) FOR [primary_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__commiss__060DEAE8] DEFAULT ((0)) FOR [commission_fl]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__created__07020F21] DEFAULT ((0)) FOR [created_on_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__last_up__07F6335A] DEFAULT ((0)) FOR [last_updated_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__locked___08EA5793] DEFAULT ((0)) FOR [locked_by_user_id]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [DF__account__locked___09DE7BCC] DEFAULT (NULL) FOR [locked_dt]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_CreatedBy_df] DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_ModifiedBy_df] DEFAULT (suser_sname()) FOR [ModifiedBy]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_Created_df] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_Modified_df] DEFAULT (getdate()) FOR [Modified]
GO

ALTER TABLE [dbo].[account] ADD CONSTRAINT [account_RowVersion_df] DEFAULT ((1)) FOR [RowVersion]
GO

[lianjasupport]

Mike, can you create that table as a new temporary table without the constraints and see if that can then be opened.

If it can't, drop the temp table then create it starting with the first column, then the second etc until you identify the column causing the problem.

[mikemeer]

Ok. from the console, I can use "dbo.account" and can select * from dbo.account - however, it crashes immediately when I try to import the database.

[lianjasupport]

Can you issue

LIST STRU

after the USE and let me see what it created.

Also

GOTO TOP
DISPLAY

and let me see the output. Sounds like a NULLs issue on a column.

[mikemeer]

Here you go:

112


I don't understand why it works in the console but it wont import it.

Mike

[lianjasupport]

You and me both. Let me take a look at our code and see what could be causing this.

Obviously the issue with the importer as the table looks fine.

We will take a look and get back to you.

FYI you can open tables like this.

use dbo.account connst "..."

then:

copy to tablename

but that would be tedious.

[lianjasupport]

Mike, looking at our code, try setting up a DSN with a username and password just for the import and see if that works for you. That is the only difference i can see between import and using tables in the console workspace.

[mikemeer]

How would I do the import in L with a DSN?

[lianjasupport]

Just create the DSN using ODBC admin then specify it in the import dialog.

[mikemeer]

Any chance I can call you or Skype you to see if we can get this resolved?

[lianjasupport]

Someone will contact you with their skype name.

[mikemeer]

*
* Recital embedded database engine
* Version 10.0.4
* Compiled on Jul 18 2012 22:23:57
*
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G
File E:\recital\c\source\lib_dao.c at line 5279 unrecognised type=G

[mikemeer]

Here is the table it seems to hang on:

114

[davefoss]

Here is the table it seems to hang on:

114

Hi Mike, If we could ask you to do one more thing. If you could SET DEBUG ON, make your ODBC connection from the console, USE this table, then post the resulting debug file here we'd appreciate it. Thanks for your patience!

[mikemeer]

I can't post anything. I let it "spin" for over 5 min. When I clicked on the window it went white and unresponsive. I had to end it and nothing got written out to either file.

Mike

[lianjasupport]

Mike, can you turn ODBC tracing on so we can determine if this is in our code or the ODBC driver.

[mikemeer]

Sorry, but, I dont know how... can you give me instructions?

[mikemeer]

Hi,

You found that the ODBC driver is not translating user defined field types "properly".... Are you going to adjust for this issue in some future release? Until then, I can create a program that creates the "sql create statements" to reproduce the database. How would I run a group of them? I don't want to have to cut and paste each one.

Also, once the structures are there... how do I get the data into it?

Thanks,
Mike

[barrymavin]

Mike, odbc import is all fixed up in beta9r6. Give that a whirl and let us know how you get on.

[mikemeer]

No, it doesn't work in release 7. It stops on the following table:


CREATE TABLE [dbo].[SchemaAudit](
[AuditDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [varchar](50) NULL,
[TSQL] [varchar](max) NOT NULL,
[XMLEventData] [xml] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Mike

[mikemeer]

Can you add a field to the import to handle a comma delimited list of tables to exclude from import? That would solve this issue.

Mike

[lianjasupport]

Hi Mike, we can look into that in another beta refresh as we are expecting to ship beta9r8 this week. Maybe we should have grid of tables that you can select from.