Virtual table encrypted

[phabio]

Hi all,
I'm running Lianja 1.1 and I'm try to connect to a remote DB.

I've successfull installed SQL Express on a virtual Win 7 machine and updated my VFP DB on this server.

On Lianja, I've created a DB, then, with the funcion "Create Virtual Table From ODBC", I've created all my virtual table..

this is the end of the log.


Create Virtual Table 'vt_tdoc' from 'tdoc'
Create Virtual Table 'vt_tdoc_sto' from 'tdoc_sto'
Create Virtual Table 'vt_tesche' from 'tesche'
Create Virtual Table 'vt_tesrip' from 'tesrip'
Create Virtual Table 'vt_testi' from 'testi'
Create Virtual Table 'vt_tipi_pag' from 'tipi_pag'
Create Virtual Table 'vt_tipi_progre' from 'tipi_progre'
Create Virtual Table 'vt_tipo_odp' from 'tipo_odp'
Create Virtual Table 'vt_tmovco' from 'tmovco'
Create Virtual Table 'vt_trans_cod' from 'trans_cod'
Create Virtual Table 'vt_translate' from 'translate'
Create Virtual Table 'vt_trasf_mag' from 'trasf_mag'
Create Virtual Table 'vt_trasferte' from 'trasferte'
Create Virtual Table 'vt_tsc_modell' from 'tsc_modell'
Create Virtual Table 'vt_ubicazioni' from 'ubicazioni'
Create Virtual Table 'vt_umisu' from 'umisu'
Create Virtual Table 'vt_user' from 'user'
Create Virtual Table 'vt_valmag' from 'valmag'
Create Virtual Table 'vt_valute' from 'valute'
Create Virtual Table 'vt_versioni_disegni' from 'versioni_disegni'
Create Virtual Table 'trace_xe_action_map' skipped
Create Virtual Table 'trace_xe_event_map' skipped
Operation complete, 209 Virtual Tables created.
Refreshing tables panel.
Completed.


Now, all the VT are Encrypted... and if I try to open a table, Lianja return the error: File XXX does not exist..

I have already tried a "Rebuild", but without success.

What can I do?

thanks
Fabio

[hmischel@diligentsystems.com]

Hi Phabio,

Are these virtual tables from SQL server?
This is how I create my virtual tables against SQL Server.

create virtualtable <vtname> connstr 'driver={SQL server};server=<servername>;Trusted_Connection=Yes;database=<dbname>' as select * from <tableName>

Try creating one this way and see if it works.

Herb

btw - If you are trying to connect to SQL express remotely, you will need to configure it to use tcp-ip which is off on SQL Express by default.

[HankFay]

Hi Phabio,

close and reopen Lianja.

From the console:

set debug on
open yourdatabase && whatever name it is
use vt_tdoc

close lianja (flushes any errors still in the buffer, although in this scenario I haven't seen that to be an issue).

Open debug.txt and you might see information that will help you find out what's going wrong.

hth,

Hank

[phabio]

thank you Hank.

I've created a 64 bit ODBC Connection using the SQL Server native client.

The VFP Upsizing have work fine.. also the Databane.net application read correctly the database.

I've try to enable the debug and the reopen the table.

on the errors tab:
ven 18. apr 21:15:00 2014
**** Lianja error ****
use vt_abicab
^
File 'VT_ABICAB' does not exist

The file Log.txt

*
* Lianja embedded database engine
* Version 1.0.0
* Compiled on Mar 27 2014 17:17:31
*
File E:\recital\c\source\rct_use.c at line 3117 virtualtable_use() table=C:\Lianja\data\sem\vt_abicab.dbf alias=VT_ABICAB fields= where= orderby= select= tally=0
File E:\recital\c\source\rct_use.c at line 3197 cmdbuf=USE 'vt_abicab.dbf' IN 1 CONNSTR "sem@@basetable=@@primarykey=@@properties=" alias "VT_ABICAB" as select * from abicab
Error reported from file rct_use.c line 890 n=15
cmdline=USE 'vt_abicab.dbf' IN 1 CONNSTR "sem@@basetable=@@primarykey=@@properties=" alias "VT_ABICAB" as select * from abicab

g_current=0
database=C:\Lianja\data\sem\
u->name=<null>
u->alias=<null>
Stacktrace:

*** ERROR *** called from rct_use.c at line 890 n=15 g_cmdpos=98 g_cmdlen=119 lex=201, g_uci=-1
on error:
error number: 15
error message: File 'VT_ABICAB' does not exist
cmdbuf: USE 'vt_abicab.dbf' IN 1 CONNSTR "sem@@basetable=@@primarykey=@@properties=" alias "VT_ABICAB" as select * from abicab

current=0
database=C:\Lianja\data\sem\
* u->name=<null>
* u->alias=<null>
Stacktrace:

Error reported from file rct_use.c line 3200 n=124
cmdline=use vt_abicab

g_current=0
database=C:\Lianja\data\sem\
u->name=<null>
u->alias=<null>
Stacktrace:

*** ERROR *** called from rct_use.c at line 3200 n=124 g_cmdpos=7 g_cmdlen=119 lex=1, g_uci=-1
on error:
error number: 124
error message: File 'VT_ABICAB' does not exist
cmdbuf: use vt_abicab

current=0
database=C:\Lianja\data\sem\
* u->name=<null>
* u->alias=<null>
Stacktrace:

[phabio]

Reading old post, I've tryed to use the "Odbc" tab on the console:
Connstring = sem --> my Odbc name
User = xxxx
Password = yyyy

then, in the command:
select * from abicab

this work fine.. with the paginating of data (very fast!)

So, is not an ODBC problem.. The prolem is with the virtual table.

How can I try to do?

thanks
Fabio

[HankFay]

Hi Phabio,

Lianja is a 32-bit application (although it can address > 2GB of data). You need a 32-bit ODBC connection.

Go to the Windows\syswow64 directory, and click on odbcad32.exe. This will enable you to create a 32-bit odbc connection.

Note that in Windows\system32 directory there also resides an odbcad32.exe file: that one creates 64-bit connections, even though it has the same name as the one in SysWow64.

This is all the same as VFP in this regard. The big difference is that you will never find yourself creating an error in pulling >2gb to a cursor (as you will in VFP). Not that you should be doing this anyway (it's on the list of "worst practices" <s>), but surprises do happen, and it's nice when it only leads to a slowdown, rather than an error.

hth,

Hank

[yvonne.milne]

Hi Fabio,

If you are specifying a DSN that does not or cannot store the username and password details, the account information should be included in the CONNSTR, e.g. 'mydsn;uid=myusername;pwd=mypassword'. So, if your connection is working correctly from the ODBC Console when you specify the username and password, include these in the CONNSTR clause of create virtualtable (http://www.lianja.com/doc/index.php/CREATE_VIRTUALTABLE) or the 'ODBC Connection' when you use the create dialogs in the Data Workspace.

http://www.lianja.com/resources/blog/39-coding-tips/380-using-virtual-tables-part-1

Regards,

Yvonne

[phabio]

Hi Hank, I've also tryed with ODBC at 32 bit.. but I had no luck...

Yvonne, you gave me a good suggestion...

When i do "Create virtual table from ODBC", if I type "my ODBC name;uid=My ID;pwd=My pwd" into the "ODBC DSN" box, all work fine.

Else, if I type only "my ODBC name" and I type the ID and the password into the Username and Password box, Lianja create the Virtual Table, but encrypted...

thank you all! Lasagna for Hank and Yvonne!

[HankFay]

Hi Phabio

there is a SQL Server security setting which does not allow the user to put the username and password in the login connection: it has to be supplied at the time. I've only run into this once

At the time, Lianja did not work with a connection string of a DSN and a user and password in a VT, but did work with SqlPassthrough, which is probably why you can create a VT (it's likely using SPT). Sounds like the same bug. I worked around it by using SPT to create the cursor.

Hank

[lianjasupport]

No, it's the same code running. The difference is he specified the username and password when creating the virtual tables. So they are created correctly. No data is retrieved at this point.

all databases have a different way if specifying the username and password in the ODBC DSN so this has to be done manually.